Difference between a CTE and a Temp Table?

Temp Table or CTE are commonly used for storing data temporarily in SQL Server. In this article, you will learn the differences among these two.

CTEs

• Are unindexable (but can use existing indexes on referenced objects)
• Cannot have constraints
• Are essentially disposable VIEWs
• Persist only until the next query is run
• Can be recursive
• Do not have dedicated stats (rely on stats on the underlying objects)

Temp Tables

• Are real materialized tables that exist in tempdb
• Can be indexed
• Can have constraints
• Persist for the life of the current CONNECTION
• Can be referenced by other queries or subprocedures
• Have dedicated stats generated by the engine

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-

• Local Temp Table
• Global Temp Table

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.

CREATE TABLE #LocalTemp
(
 UserID int,
 Name varchar(50), 
 Address varchar(150)
)
GO
insert into #LocalTemp  values ( 1, 'codehunger','Patna');
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.

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.

CREATE TABLE ##GlobalTemp
(
 UserID int,
 Name varchar(50), 
 Address varchar(150)
)
GO
insert into ##GlobalTemp  values  ( 1, 'codehunger','Patna');
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.

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 everytime. 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.

It was introduced with SQL Server 2005. It is a temporary result set and typically it may be a result of complex sub-query. Unlike a 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. Always begin CTE with a semicolon.

;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