This post describes the major differences between CTE, temp table and table variables.

1) CTE

CTE stands for Common Table expressions. These are simple select queries and they do not create physical space in tempDB. Unlike temporary table there life is limited to the current query. This is created in memory rather than tempdb database and you cannot create any index on it. CTE improves readability and simplifies the maintenance of complex queries and sub-queries. It is defined by using WITH statement and always begin with semicolon.

CTE Usage

a) To store result of a complex sub query for referencing multiple times in the same statement.

b) To create a recursive query

c) To create a substitute for a view when the general use of a view is not required

2) Temporary Table

In SQL Server, temporary tables are created at run-time and you can do all the operations on them which you can do on a normal table.These tables are created inside tempdb database and can have constraints, indexes like normal tables.

Temporary Table Usage

a) Moving data to a temp table is I/O intensive, since the data is actually written back to disk. So the performance will depend on the current stress load of tempdb. Since tempdb is shared across database the performance of the query will be uncertain. Hence they are preferred when handling large datasets.

3) Table Variable

Table variable is a light weight version of temp table and exists for a particular batch of query execution. It gets dropped onceit comes out of batch. This is also created in the tempdb database but not on the memory. We can create indexes in temporary tables,however for table variable the index is limited to PRIMARY/UNIQUE KEY constraints at column level.

Table Variable Usage

a) For smaller count of rows a table variable may perform well than a temporary table. But if the query requires better query plan depending on statistics of involved tables, then temp table is preferred. It is so because table variables maintain no statistics and the objects are discarded after each execution.

Reference

http://www.dotnet-tricks.com/Tutorial/sqlserver/X517150913-Difference-between-CTE-and-Temp-Table-and-Table-Variable.html