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 or a temp table. In my case, I
usually use what's more convenient at that time and experiment a bit.
We
can easily differentiate them based on the following contents –
Contents
|
Common Table Expression (CTE)
|
Temporary Table
|
Storage
Location
|
However,
a CTE always uses memory.
|
Stored
in the 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.
|
Temporary
tables always stored in the tempdb.
User-defined
data types and XML collections must be in tempdb to use
for #temp tables.
|
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.
|
Temp
tables can be accessed within child batches (nested triggers, procedure, exec
calls).
They persist
till the end of the batch session or dropped them forcefully.
|
Lifetime
|
CTEs are
needed to be recreated if they needed again in the batch query.
|
Temp
tables are created explicitly when the TSQL CREATE TABLE statement is encountered
and can be dropped explicitly with DROP TABLE or will be dropped implicitly
when the batch ends.
|
Transactions
|
CTEs
don’t participate in transactions or locking. They are carried out as system
transactions.
|
Temp
tables operations would be carried out as part of the user transactions.
A
NOLOCK hint or READ UNCOMMITTED isolation level can of course be specified
explicitly when working with temp tables as well.
|
Indexes
|
CTEs
are not sported indexes directly.
|
Temporary
tables comply to the same rules as permanent tables when it comes down to
indexing. We need to create indexes on the temp table after fully populated
the data.
Indexes
on temporary tables do not come at a higher priority than indexes on
permanent tables
|
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) temp tables are not restricted in a
parallel plan.
Temp
tables cannot be used inside a function.
Temp
tables have named constraints.
Temp
tables can be SELECT-ed INTO, ALTER-ed, TRUNCATE-d or be the target of DBCC
commands such as DBCC CHECKIDENT or of SET IDENTITY INSERT.
Temp
tables supports table hints such as WITH (FORCESCAN).
CHECK
constraints on Temp tables 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.
|
Statistics
is maintained on temp tables.
|
To know more on the different kinds of the tables in SQL Servers at
Nice Article !
ReplyDeleteThis is my pleasure to read your article.
Really this will help to people of SQL Server Community.
I have also prepared one article about, Temporary Table vs Common Table Expression in SQL Server.
You can also visit my article, your comments and reviews are most welcome.
http://www.dbrnd.com/2016/02/sql-server-difference-between-temp-table-and-common-table-expression-cte/