Difference between CTE and Temp Table and Table Variable

Funny Videos: FB Fun4you

CTE - A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.
A CTE can be used to:

1. Create a recursive query. For more information, see Recursive Queries Using Common Table Expressions.
2. Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
3. Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
4. Reference the resulting table multiple times in the same statement.

Temp Tables - There are two types of temporary tables: local and global. Local temporary tables are visible only to their creators during the same connection to an instance of SQL Server as when the tables were first created or referenced. Local temporary tables are deleted after the user disconnects from the instance of SQL Server. Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server.Temporary tables are created using the same syntax as a CREATE TABLE except the table name starts with a '#' sign.

Table Varirable - Table variables store a set of records, so naturally the declaration syntax looks very similar to a CREATE TABLE statement, as you can see in the following example:
DECLARE @ProductTotals TABLE(  ProductID int,   Revenue money)
A table variable is scoped to the stored procedure, batch, or user-defined function just like any local variable you create with a DECLARE statement. The variable will no longer exist after the procedure exits - there will be no table to clean up with a DROP statement.

Differences between temp tbale and table variable:

1. Temp table: A Temp table is easy to create and back up data.
   Table variable: But the table variable involves the effort when we usually create the normal tables.
2. Temp table: Temp table result can be used by multiple users.
   Table variable: But the table variable can be used by the current user only.
3. Temp table: Temp table will be stored in the tempdb. It will make network traffic. When we have large data in the temp table then it has to work across the database. A Performance issue will exist.
   Table variable: But a table variable will store in the physical memory for some of the data, then later when the size increases it will be moved to the tempdb.
4. Temp table: Temp table can do all the DDL operations. It allows creating the indexes, dropping, altering, etc..,
   Table variable: Whereas table variable won't allow doing the DDL operations. But the table variable allows us to create the clustered index only.
5. Temp table: Temp table can be used for the current session or global. So that a multiple user session can utilize the results in the table.
   Table variable: But the table variable can be used up to that program. (Stored procedure)
6. Temp table: Temp variable cannot use the transactions. When we do the DML operations with the temp table then it can be rollback or commit the transactions.
   Table variable: But we cannot do it for table variable.
7. Temp table: Functions cannot use the temp variable. More over we cannot do the DML operation in the functions .
   Table variable: But the function allows us to use the table variable. But using the table variable we can do that.
8. Temp table: The stored procedure will do the recompilation (can't use same execution plan) when we use the temp variable for every sub sequent calls.
   Table variable: Whereas the table variable won't do like that.
9. Performance - A table variable will generally use fewer resources than a temporary table. Transactions touching table variables only last for the duration of the update on the table variable, so there is less locking and logging overhead.

Here are some good links about differences between CTE and Temp Table and Table Variable:

Difference between CTE and Temp Table and Table Variable


Temp Tables and types of temp tables