Total Page Preview:   000000013780

Difference between CTE(Common Table Expressions) and Table Variable and Temp Table in SQL SERVER ?


Temp Tables :

In SQL Server, 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 Tempdb database. Based on the scope and behavior temporary tables are of two types as given below-

 

Different Types of Temporary Tables

SQL Server provides two types of temp tables based on the behavior and scope of the table. These are:

  1. Local Temp Table
  2. Global Temp Table

 

01.Local Temp Table:

Local temp tables are only available to the current connection for the user; and they are automatically deleted when the user disconnects from instances. Local temporary table name is stared with hash ("#") sign.

 

Example :

 

CREATE TABLE #LocalTemp

(
 UserID int,
 Name varchar(50), 
 Address varchar(150)
)
GO
insert into #LocalTemp  values ( 1, 'Brijesh Kumar','Noida');
GO
Select * from #LocalTemp
 
The scope of Local temp table exist to the current session of current user means to the current query window. If you will close the current query window or open a new query window and will try to find above created temp table, it will give you the error.
 
01.Global Temp Table:

Global Temporary tables name starts with a double hash ("##"). Once this table has been created by a connection, like a permanent table it is then available to any user by any connection. It can only be deleted once all connections have been closed.

 

Example :

 

CREATE TABLE ##GlobalTemp

(
 UserID int,
 Name varchar(50), 
 Address varchar(150)
)
GO
insert into ##GlobalTemp  values ( 1, 'Brijesh Kumar','Noida');
GO
Select * from ##GlobalTemp
 
Global temporary tables are visible to all SQL Server connections while Local temporary tables are visible to only current SQL Server connection.


Table Variables :

This acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of batch. This is also created in the Tempdb database but not the memory. This also allows you to create primary key, identity at the time of Table variable declaration but not non-clustered index.

Example 

GO

DECLARE @TEmployee TABLE
(
Id INT IDENTITY(1,1),
EmpName VARCHAR(50),
Age INT
--Insert data to Table variable @TEmployee
INSERT INTO @TEmployee(EmpName , Age)
VALUES('BRIJESH KUMAR', 25)
--Select data
Select * from @TEmployee
 
--Next batch
GO
Select * from @TEmployee --gives error in next batch

 

 


CTE (Common Table Expressions)  :

 

A CTE creates the table being used in memory, but is only valid for the specific query following it. When using recursion, this can be an effective structure, but bear in mind that it will need to be recreated everytime it's needed.

 

You might also consider here a table variable. This is used as a temp table is used, but is also in-memory only, but can be used multiple times without needing to be recreated every time. Also, if you need to persist a few records now, add a few more records after the next select, add a few more records after another op, then return just those handful of records, then this is a handy in-memory structure.

Example :

;WITH Emp([Eid], [Salary]) --Column names for CTE, which are optional
  AS
  (
    SELECT TOP 3 [Eid], [Salary] FROM [TestDB].[dbo].[Employee] ORDER BY Salary DESC
  )

 --User CTE
  SELECT TOP 1 [Eid], [Salary] FROM emp ORDER BY Salary ASC

 

Difference between CTE(Common Table Expressions) and Table Variable :

  CTE Table Variable
1 CTE is like Derived Table or even like a sub-query. So it doesn't store on disk. Table variable is like Temporary Table. It stores on disk.
2 You cannot create any index on CTE You can create clustered index but can not create Non clustered index
3 Scope of CTE  is within the session Scope of table variable is within the batch
Difference Between CTE And Table Variable
 

 

Difference between CTE(Common Table Expressions) and #Temp Table :

  CTE #Temp Table
1 Are unindexable (but can use existing indexes on referenced objects). Are real materialized tables that exist in tempdb
2 Cannot have constraints Can be indexed
3 Are essentially disposable VIEWs Can have constraints
4 Persist only until the next query is run Persist for the life of the current CONNECTION
5 Can be recursive Can be referenced by other queries or subprocedures
6 Do not have dedicated stats (rely on stats on the underlying objects) Have dedicated stats generated by the engine
Defference CTE And Temp Table in SQL Server
 

 

Thanks!!

Thank You

About Author

Brijesh Kumar

Database Developer

I have more then 6 years Experience in Microsoft Technologies - SQL Server Database, ETL Azure Cloud - Azure SQL Database, CosmosDB, Azure Data Factory, PowerBI, Web Job, Azure Function, Azure Storage, Web Apps, Powershall and Database Migration On-Premise to Azure Cloud.
LinkedIn : https://www.linkedin.com



Comments

Brijesh Kumar
08-Apr-2016
This is very good and helpful article.
SANNY KUMAR
08-Apr-2016
Very helpful article

                           
                           

                           

                           

Facebook User: