Temporarily Data Storage - CTE Vs Temp Table Vs Table Variable - Sql Server

May 02, 2019 BY xtreme

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
		) Temp
	WHERE Temp.Age > 50
	ORDER BY Temp.NAME


	By using CTE above query can be re-written as follows :
	;With CTE1(Address, Name, Age)
		AS
		(
			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
	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
		(
			StudentID int,
			Name varchar(100),
			DateEntered datetime 
		)	
		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] 
		GO


	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
		(
			StudentID int,
			Name varchar(100)
		)	
		insert into ##Admissions values ( 1, 'Itblow');
		Select * from ##LocalTemp
		

Table Variable
		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),
			Name Varcharchar(200),
			Age INT
		) 
		  
		 INSERT INTO @Student(Id,Name,Age)
		 SELECT DISTINCT Id, Name,Age FROM Student ORDER BY Id ASC 
		 --Select data
		 Select * from @Student
 
		 --Next batch
		 GO
		 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


Comparison: -		 
	- 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.
Related