Difference between a temp table and Table Variable in SQL Server

I have faced this question in almost all the interviews. What is the difference between a Temporary table and Table Variable in SQL Server?

Here are the main differences:

1. Declaration syntax:

Temp Table:       CREATE TABLE #tmptbl(ID INT, NAME VARCHAR(20))
Table Variable:   DECLARE @tblvar TABLE(ID INT, NAME VARCHAR(20))

2. Creating Index:

You can create Indexes (Clustered and non-clustered Indexes) in Temp Tables. This can return records faster if there are large number of records in a table.

You cannot create indexes in Table Variables.
Note: You can have Primary key in table variables which will create a clustered index by default. But you cannot create any index explicitly by using Create Index command.

3. Transaction:

The transaction logs are not recorded for the table-variables. Hence, we cannot implement transaction mechanism in case of table variables.
But transaction mechanism is applicable in case of temp tables.

CREATE TABLE #tmptbl (val VARCHAR(50))
DECLARE @tblvar TABLE(val VARCHAR(50))

INSERT INTO #tmptbl VALUES ('Old value in temp table')
INSERT INTO @tblvar VALUES ('Old value in table variable')

BEGIN TRAN
UPDATE #tmptbl SET val='New value in temp table'
UPDATE @tblvar SET val='New value in table variable'

SELECT * FROM #tmptbl
SELECT * FROM @tblvar
ROLLBACK

SELECT * FROM #tmptbl
SELECT * FROM @tblvar



The output will be as below:


New value in temp table
New value in table variable


Old value in temp table
New value in table variable


Notice that the Update command against the table variable is not getting rolled back.

4. Stored Procedure Recompilation:

A procedure with a temporary table cannot be pre-compiled. But a procedure with table-variables can be compiled in advance. A Pre-compiled Stored Procedure runs faster.

5. Performance:

For small to medium volumes of data and simple usage scenarios you should use table variables. For large set of data, you should go for Temp tables (with proper Indexes).

If you have any more differences, please share in comments.