In SQL Server, we have various options for storing data temporarily. Temp Table, Table variable and CTE are commonly used way for storing temporary data.
CTE - Common Table Expressions
CTE was introduced with SQL Server 2005. It is a temporary result set and typically it may be a result
of complex sub-query. Unlike the temporary table, its life is limited to the current query. It is defined
by using WITH statement. CTE improves readability and ease in maintenance of complex queries and sub-queries.
CTE Basically used: -
1. To store the result of a complex subquery for further use.
2. To create a recursive query.
Always begin CTE with a semicolon.
A subquery without CTE is given below :
SELECT * FROM
SELECT Addr.Address, Emp.Name, Emp.Age From Address Addr
Inner join Employee Emp on Emp.EID = Addr.EID
WHERE Temp.Age > 50
ORDER BY Temp.NAME
By using CTE above query can be re-written as follows :
;With CTE1(Address, Name, Age)
SELECT Addr.Address, Emp.Name, Emp.Age from Address Addr
INNER JOIN EMP Emp ON Emp.EID = Addr.EID
SELECT * FROM CTE1
WHERE CTE1.Age > 50
ORDER BY CTE1.NAME
Temporary tables are created at run-time and you can do all the operations which you can do on a
normal table. These tables are created inside the Tempdb database. Based on the scope and behavior temporary
tables are of two types: -
1. Local Temp Table
The scope of Local temp table exists to the current session of the current user, that created the
tables means to the current query window. It automatically deleted when the session that created the
tables has been closed. The local temporary table name is stared with a single hash ("#") sign.
CREATE TABLE #Admissions
insert into #Admissions values ( 1, 'Itblow');
Select * from #LocalTemp
Temporary tables act like physical tables in many ways. You can create indexes and statistics on temporary
tables. You can also apply Data Definition Language (DDL) statements against temporary tables to add
constraints, defaults, and referential integrity such as primary and foreign keys. You can also add and
drop columns from temporary tables.
ALTER TABLE dbo.#Admissions
ADD CONSTRAINT [DF_DateEntered] DEFAULT (GETDATE()) FOR [DateEntered],
PRIMARY KEY CLUSTERED
( [StudentID] ) ON [PRIMARY]
2. Global Temp Table
Global temp tables are available to all SQL Server sessions of all users or connections . These can
be created by any SQL Server connection user and these are automatically deleted when all the SQL Server
connections have been closed
CREATE TABLE ##Admissions
insert into ##Admissions values ( 1, 'Itblow');
Select * from ##LocalTemp
Acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes
out of the batch. This is also created in the tempdb database. You can create a primary key, identity at
the time of Table variable declaration but not non-clustered index.
DECLARE @Student TABLE (
Id INT IDENTITY(1,1),
INSERT INTO @Student(Id,Name,Age)
SELECT DISTINCT Id, Name,Age FROM Student ORDER BY Id ASC
Select * from @Student
Select * from @Student --gives error in next batch
- Table variables can not have Non-Clustered Indexes
- You can not create constraints in table variables
- You can not create default values on table variable columns
- Statistics can not be created against table variables
- Temp Tables are physically created in the tempdb database. These tables act as the normal table and
also can have constraints, an index like normal tables.
- CTE is a named temporary result set which is used to manipulate the complex sub-queries data. This
exists for the scope of a statement. This is created in memory rather than the Tempdb database. You
cannot create an index on CTE.
- Table Variable acts like a variable and exists for a particular batch of query execution. It gets dropped
once it comes out of a batch. This is also created in the tempdb database but not the memory.
Similarities Table Variable and Temporary tables:
- Instantiated in tempdb
- Clustered indexes can be created on table variables and temporary tables
- Both are logged in the transaction log
- Just as with temp and regular tables, users can perform all Data Modification Language (DML) queries against
a table variable: SELECT, INSERT, UPDATE, and DELETE.