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
