Thursday, June 11, 2015

SQL- Difference between Temp Table and CTE

What's the difference between a temp table 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 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.

Please suggest your opinions. 
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

1 comment:

  1. Nice Article !
    This 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/

    ReplyDelete

Popular Posts