You are currently viewing When to use Temp Table, Table Variable and CTE.

As a layman or a novice, when we come across the concept of local temporary tables, global temporary tables, table variables or common table expressions; we tend to think that they function similarly i.e. they all store data in them in a tabular format. So whats the difference in them and when should I use either ones?

 

Let’s get to the basics first

  Local Temporary Table Global Temporary Table  Table Variable Common Table Expression – CTE
Scope Within Server Process ID (SPID) i.e. only to current connection Within SQL Instance Active time. i.e. across all sql connections Within Declaration Scope Next line of execution after populating the CTE
Declaration Prefixed with a # Prefixed with a ## Prefixed with an @ Declared using a ;With Clause
Affected by Transactions Yes Yes No N/A
Generic Example of Usage Primarily used in storing and accessing data in the management studio queries in the same query window. Can be used in Stored Procedures Primarily used in storing and accessing data in the management studio queries across multiple queries. Use in Stored Procedures should be avoided Primarily used in storing and accessing data in the management studio queries and should be used in Stored Procedures CTE is a result of complex sub-queries and recursive queries. It is used to deal with complex outputs generated from a complex query.

 

Examples:

Local Temporary Table

CREATE TABLE #temp_table (column_definitions)
INSERT INTO #temp_table (columns)
SELECT columns
FROM source_physical_table


Global Temporary Table

CREATE TABLE ##Global_temp_table (column_definitions)
INSERT INTO ##Global_temp_table (columns)
SELECT columns
FROM source_physical_table

Table Variable

Declare @TABLE_Variable table (column_definitions)
INSERT INTO @TABLE_Variable (columns)
SELECT columns
FROM source_physical_table

SELECT columns
FROM @TABLE_Variable -- Gives the desired output

GO

SELECT columns
FROM @TABLE_Variable -- Out of scope so it doesn't give desired output

Common Table Expression

; With MyCTE(Name, Age)--Column names for CTE are not mandatory

AS

(

SELECT Emp.Name, Emp.Age from EMP Emp 

)

SELECT * FROM MyCTE --Using CTE

WHERE MyCTE.Age > 50

ORDER BY MyCTE.NAME-- Gives desired output


SELECT * FROM MyCTE --Using CTE

WHERE MyCTE.Age > 50

ORDER BY MyCTE.NAME-- Out of scope so it doesn't give desired output


Now we know the difference between each one of them and when to use what.

Post comments if this helps

 

Leave a Reply