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.