When to use Temp Table, Table Variable and CTE.

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

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 TableGlobal Temporary Table Table VariableCommon Table Expression – CTE
ScopeWithin Server Process ID (SPID) i.e. only to current connectionWithin SQL Instance Active time. i.e. across all sql connectionsWithin Declaration ScopeNext line of execution after populating the CTE
DeclarationPrefixed with a #Prefixed with a ##Prefixed with an @Declared using a ;With Clause
Affected by TransactionsYesYesNoN/A
Generic Example of UsagePrimarily used in storing and accessing data in the management studio queries in the same query window. Can be used in Stored ProceduresPrimarily used in storing and accessing data in the management studio queries across multiple queries. Use in Stored Procedures should be avoidedPrimarily used in storing and accessing data in the management studio queries and should be used in Stored ProceduresCTE 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

 

Tags: , , ,

Leave a Reply