Difference between CTE and Temp Table and Table Variable

CTE

It is a temporary result set and Unlike temporary table its life is limited to the current query. CTE improves readability and it is widely used to create a recursive query.

Local Temp Table

Local temp tables are only available to the SQL Server session or connection

Global Temp Table

Global temp tables are available to all SQL Server sessions or connections (means all the user). These are automatically deleted when all the SQL Server connections have been closed.

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.