What is the difference between a Temporary Table and a Table Variable?

Temporary vs Table


1. Table variable is created in the memory where as a temporary table is created in the TempDB. But, if there is a memory pressure, the pages belonging to a table variable may be pushed out to tempdb.

2. Table variables cannot be involved in transactions, logging or locking. This makes table variable faster than a temporary table.

3. You can pass table variable as parameter to functions and stored procedures, where as you cannot do the same with temporary table.

4. A temporary table can have indexes, whereas a table variable can only have a primary index. If speed is an issue Table variables can be faster, but if there are a lot of records, or there is a need to search the temporary table based on a clustered index, then a Temporary Table would be better. If you have less than 100 rows generally use a table variable. Otherwise use a temporary table. This is because SQL Server won't create statistics on table variables.