Exact use of Table variable and Temp table

Ashish Verma

10y764

Where to use Temp table and Table variable and which one will be fast and which scenario go for table variable rather than temp table.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke10y ago
Accepted answer
If you could just google such question(s), you will get the answer. No need to post questions over here.
I just did the same for you, despite of knowing the answer. Here is a detailed discussion on the subject:
http://stackoverflow.com/questions/27894/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server
Midhun Tp10y ago
Hi,
Temporary Tables are real tables so you can do things like CREATE INDEXes, etc. If you have large amounts of data for which accessing by index will be faster then temporary tables are a good option
Table variables can have indexes by using PRIMARY KEY or UNIQUE constraints.
Table variable cannot use the transactions. When we do the DML operations with the temp table then it can be rollback or commit the transactions. But we cannot do it for table variable.
Please check below link for more details -
http://www.c-sharpcorner.com/UploadFile/skumaar_mca/difference-between-temp-table-and-table-variable/