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.|
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
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