Difference between CTE and Temp Table in SQL Server

CTE stands for Common Table expressions. WITH common_table_expression (Transact-SQL) Specifies a temporary named result set, known as a common table expression (CTE). This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement.It is a temporary result set and typically it may be a result of complex sub-query. Always begin CTE with semicolon.

A sub query without CTE:

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 is:

;With CTE1(Address, Name, Age)--Column names for CTE, which are optional

AS

   (SELECT Addr.Address, Emp.Name, Emp.Age

    FROM Address Addr

    INNER JOIN EMP Emp ON Emp.EID = Addr.EID

   )

SELECT * FROM CTE1 --Using CTE

WHERE CTE1.Age > 50

ORDER BY CTE1.NAME

Local Temp Tables are only available to the SQL Server session or connection (means single user) that created the tables. These are automatically deleted when the session that created the tables has been closed. Local temporary table name is stared with single hash ("#") sign. 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.

CREATE TABLE #LocalTemp

 (UserID INT,

  Name VARCHAR(50),

  Address VARCHAR(150)

 )

GO

INSERT INTO #LocalTemp VALUES ( 1, 'Arka','Kolkata');

GO

SELECT * FROM #LocalTemp

Global Temp Tables are available to all SQL Server sessions or connections (means all the user). These can be created by any SQL Server connection user and these are automatically deleted when all the SQL Server connections have been closed. Global temporary table name is stared with double hash ("##") sign.

Global temporary tables are visible to all SQL Server connections while Local temporary tables are visible to only current SQL Server connection.

CREATE TABLE ##GlobalTemp

 (UserID INT,

  Name VARCHAR(50),

  Address VARCHAR(150)

 )

GO

INSERT INTO ##GlobalTemp VALUES ( 1, 'Arka','Kolkata');

GO

SELECT * FROM ##GlobalTemp

Table Variable

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.

 DECLARE @TProduct TABLE

 (SNo INT IDENTITY(1,1),

  ProductID INT,

  Qty INT

 )

 --Insert data to Table variable @Product

 INSERT INTO @TProduct(ProductID,Qty)

 SELECT DISTINCT ProductID,Qty FROM ProductsSales ORDER BY ProductID ASC

 --Select data

 SELECT * FROM @TProduct 

 --Next Execute

 SELECT * FROM @TProduct --gives error in next execution time

If there is any other better option,

Please share it here or mail to me arkaa4@gmail.com

Thank You,
Arka Gupta.

http://arkaguptablog.blogspot.in/

https://in.linkedin.com/in/arkagupta

https://plus.google.com/+ArkaGupta-arkagupta/posts