Saturday, March 22, 2014

Temp variablesVS Temp tables(SQL Server interview questions)



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.