Difference Between Temporary Table and Common Table Expression in SQL Server

Temporary Table or Temp Table :- Temp tables are the tables created for storing the Temporary data. Temp table is created with # sign as the start and during the execution time.Temp tables are Stored into TempDB and will dropped automaticaly after the current session has closed.Two types of Temp tables are available in SQL Server that is Local Temp Table and Global Temp Table.

Common Table Expression or CTE :- CTE is used for Storing the Temporary result set generated by the subqueries that is defined using WITH keyword.The Scope of CTE is limited to the Query Statement only.

Below is the Tabular Representation of the Differences in Temp Table and CTE.

Sr.No	Temp Table	Common Table Expression CTE
1	Scope of Local Temp table is available only in current session whereas Scope of Global Temp table in all over the open sessions.	Common table Expression has limited scope to the Statement for which it is written.
2	Temp Tables are defined as normal tables and name start with # sign	CTE is defined using the WITH .
3	Temp tables are Stored in TempDB in SQL Server.	CTE is not stored anywhere it is disposal automatically after the scope
4	Temp table has appended few characters automatically after the name of Temp table and it has a limit of 116 Characters.	CTE do not have any such character limits.
5	Temp tables has some performance overhead because it is storing in physical DB.	CTE is very fast and quickly manipulate the data because it is stored in in-momory.
6	Temp tables are used for Large data store and manipulations.	CTE is used for small data with more complex manipulations.
7	Using Recursive queries is more complex.	Mainly used for recursive queries and repeated data sets.
8	We can alter the structure of Temp table after creation of it.	We can not change the CTE defination during runtime.
9	We can Drop the temp tables using the Drop commands	We can not use drop command with CTE.
10	We can not use Temp tables in user defined Functions.	We can use CTE in User defined functions.
11	We can create Index on Temp tables.	We can not create a Index on CTE.