Difference between Temp table table variable and CTE

What is the difference between temp table and global temp table and table variable and CTE in SQL Server ? It is a very commonly asked interview question and you must know the differences between them to use the appropriate method while writing SQL queries.

Temp Table Global Temp Table Table Variable CTE
CREATE TABLE #t (ID INT) CREATE TABLE ##t (ID INT) DECLARE @t TABLE (ID INT) ;WITH CTE_T AS (SELECT ID FROM table)
Gets created physically in the tempdb Gets created physically in the tempdb Gets created physically in the tempdb but acts like a variable Gets created in the memory. In case of low memory it can spill to tempdb also.
Available only to the particular session Available to all the sessions in that instance Available only to the particular batch in the session Available only to the particular  scope in the session
The table is available to the session as long as the session is active. The table is available to all other sessions as long as the created session is active. Auto deallocation on session disconnect Auto deallocation after the scope.
Primary key, indexes, constraints etc can be created Primary key, indexes, constraints etc can be created clustered index or Non-clustered index can be created with the primary key during the time of declaration. Not Applicable
Table can be altered after creation Table can be altered after creation Table can NOT be altered after creation CTE cannot be modified at all
DDL operation is allowed DDL operation is allowed DDL operation is NOT allowed DDL operation is NOT allowed
Can have statistics on columns and indexes Can have statistics on columns and indexes Can NOT have statistics Not Applicable
Recursion is not possible Recursion is not possible Recursion is not possible Recursion is possible
Rolls back in the transaction Rolls back in the transaction Does not roll back in the transaction. Hence often used in the try catch block to capture errors even after rollback occurred. READ MORE about the usage in error handling Rolls back in the transaction
Cannot be used in a view Cannot be used in a view Cannot be used in a view Can be used in a view
Advisable to store large number of records and use them through out the session. Better for large number of record storage but you should be careful as the table with the same name cannot be created by other session at the same time. Better for small number of records to use inside a batch. Better for small number of records or when recursion is required. READ MORE about CTE

temp-table-table-variable-CTE

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

%d bloggers like this: