Difference between "Temporary Table" and "Table Variable" in SQL Server 2000 / 2005 !!

Hi,

I have got these two concepts from online in SQL Server 2000 / 2005 - Difference between "Temporary Table" and "Table Variable". Can you please correct me if i am wrong / and please explain if necessary ?

Temporary Table
=============

1. If it is created within an SP or Trigger it is automatically destroyed when the creating routine terminates. If it is NOT created within SP or Trigger then we have to explicitly drop the table using "Drop Table #table" command.

2. It has more locking than table variable. // Can you please explain why ?

3. It has more logging than table variable. // Can you please explain why ?

Table Variable
===========

1. A table variable is automatically cleared when an SP, Trigger or UDF goes out of scope - Just like any other local variable. And we need not remember to drop or clear the data even though they are NOT created within an SP, Trigger or UDF.

2. It has less locking than temporary table.

3. It has less logging than temporary table.

Appreciate your time and patience !!

ThanksStart Free Trial