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.