Temp tables
|
Temp variables
|
|
Big difference
|
Temp tables are real temporary SQL Server tables , you can
create indexes , they can participate in transactions , it will use SQL
Server optimization techniques etc.
So if you are operating on large number of records use Temp
tables.
|
As the name says these are variables. So they do not participate
in transactions, you can not create indexes directly, they do not use SQL
server optimization techniques etc.
Good for small number of records.
|
Should be used when?
|
Large number of records.
|
Less than 100 records.
|
Scope
|
Outside procedure
|
Only Inside the procedure.
|
Transaction
|
Yes
|
No
|
Indexes
|
Yes
|
No (Note: - Indexes get indirectly created if you great a unique
primary key.)
|
Truncate
|
Yes
|
No
|
Alter Table
|
Yes
|
No it’s just variable.
|
Affected by SQL Server
optimization
|
Yes
|
No
|
Parallelism
|
Yes
|
No.
|
Recently one of our friends was asked this tricky SQL Server interview question: - Can SQL Server views be updated ?. Below is a great video by www.questpond.com which discusses this question in detail.
