SQL - Difference between Table Variable and Common Type Expression

What's the difference between Table Variable and Common Type Expression (CTE) in SQL Server?

In the real practice, it depends on the situation where we need to choose the current approach to complete the task by using of a CTE, Table Variable or a temp table. I usually use what's more convenient at that time and experiment a bit.

We can easily differentiate CTE and Table Variable based on the following contents –

Contents	Common Table Expression (CTE)	Table Variable
Storage Location	However, a CTE always uses memory.	Table variable is NOT necessarily memory resident. Under memory pressure, the pages belonging to a table variable can be pushed out to tempdb.
Logical Location	They have the same semantics as updatable views. Updating or deleting from them affects the base tables as the CTE definition just gets expanded out into the query and they do not exist as objects in their own right. User-defined data types and XML collections must be in current database to use CTE.	It acts like a DDL operation whenever we create a table variable and metadata of the table variable always stored in system catalog. User-defined data types and XML collections must be in current database to use for table variables.
Scope	CTE’s scope is only for the query. CTE can be referenced multiple times in other CTEs. CTEs can only be used to return data once. Mostly they are used to get recursive output. They always persist before the next query run.	Table variables have a limited scope. They are accessed within the same batch and scope in which they are declared. They will persist in the Query execution plan.
Lifetime	CTEs are needed to be recreated if they needed again in the batch query.	Table variable are created implicitly when a batch containing a DECLARE @.. TABLE statement. They are dropped implicitly at the end of the batch execution. We cannot use the table variable before the DECLARE statement.
Transactions	CTEs don’t participate in transactions or locking. They are carried out as system transactions.	Table variables don’t participate in transactions or locking. They are carried out as system transactions. This can be a useful feature, e.g. during a transaction certain activities can be logged in a table variable - if the transaction is rolled back then the table variable is available for inspection.
Indexes	CTEs are not sported indexes directly.	For versions prior to SQL Server 2014 indexes can only be created implicitly on table variables as a side effect of adding a unique constraint or primary key. Additionally table variables do not support INCLUDE columns, filtered indexes or partitioning.
Other Functional Differences	Queries that insert into (or otherwise modify) CTEs cannot have a parallel plan. CTEs can be used inside scalar or multi-statement table UDFs. CTEs cannot have named constraints. CTEs cannot be SELECT-ed INTO, ALTER-ed, TRUNCATE-d or be the target of DBCC commands such as DBCC CHECKIDENT or of SET IDENTITY INSERT. CHECK constraints on CTEs are not considered by the optimizer for simplification, implied predicates or contradiction detection.	Queries that insert into (or otherwise modify) table variables cannot have a parallel plan. Table variables can be used inside scalar or multi-statement table UDFs. Table variables cannot have named constraints. Table variables cannot be SELECT-ed INTO, ALTER-ed, TRUNCATE-d or be the target of DBCC commands such as DBCC CHECKIDENT or of SET IDENTITY INSERT. Table variables do not support table hints such as WITH (FORCESCAN). CHECK constraints on table variables are not considered by the optimizer for simplification, implied predicates or contradiction detection.
Statistics	No statistics is maintained on CTEs which means that any changes in data impacting CTEs will not cause recompilation of queries accessing CTEs.	No statistics is maintained on table variable which means that any changes in data impacting table variable will not cause recompilation of queries accessing table variable.

To know more on the different kinds of the tables in SQL Servers at

1. SQL – Wide Tables
2. SQL - Table Variables
3. SQL - Temp Table (Local & Global Temp Tables)
4. SQL - Common Table Expression
5. SQL - Difference between Table Variable and Common Type Expression
6. SQL - Difference between Temp Table and CTE
7. SQL - Difference between Temp Table and Table Variable