Temp Table, Table Variable And CTE In SQL Server

Temp Table:

• Temp tables are also called as hash tables or temporary tables.
• The temporary tables are created during the execution time.
• The temp tables are available only in the scope where they are created.
• They are stored in the tempdb and will get dropped automatically after the scope.
• The name of the temp tables can have a maximum of 116 characters.
• The structure of temp table can be altered after creating.
• We can explicitly drop temp tables using DROP statement.
• It cannot be used in User Defined Function (UDF).
• Temp tables take part in transactions.
• We can be created Index on temp tables.
• We can apply read lock on temp tables.
• Constraints can be created on temp tables except FOREIGN KEY.
• Temp table is maintaining a require statistics.
• Temporary tables are of two types, Local Temp Tables and Global Temp Tables.

1. The local temp table is available only in the current session.

Only one SQL Server user can use the temp table.

Once the stored procedure finishes execution, the temp table drops automatically from the tempdb.

2. The global temp tables are available for all the sessions or the SQL Server connections.

Multiple SQL Server users can use the same temp table.

Example:

tempdb database.

Local temp table.

Global temp table.

Explicitly Drop table.

Using primary Constraint.

Create stored procedure on temp table and maintaining Statistics Information.

Table Variable:

• Temp Variables used for holding the data for a temporary time just like Temp tables.
• Temp variables are created using “DECLARE” statements and are assigned values by using either a SET or SELECT command.
• This acts like a variable and exists for a particular batch of query executions. It gets dropped once it comes out of batch.
• Temp variables are also created in the tempdb database but not the memory.
• It is used when you are required to use the current result set in next queries then store result into temp variables.

Example:

Temp variable.

Common Table Expression(CTE):

• CTE work as a temporary result set generated from SELECT query defined by WITH clause.
• CTE is the result of complex sub queries.
• Similar to temporary tables CTE doesn’t store as an object,the scope is limited to the current query.
• CTE improves readability and ease in maintenance of complex queries and sub-queries.
• There are two types of Common Table Expression Non-Recursive CTE and Recursive CTE.

1. Non-recursive common table expression is the generic form of CTE. It does not have any reference to itself in the CTE definition.
2. Recursive CTE has reference to itself.

Example:

Tables for CTE.

CTE Example.

If you are new to SQL refer Below video for better Understanding.