Throughout the T-Shirt series we have discussed Temp Tables, Table Variables, Derived Tables, and Common Table Expressions (CTE).

In this post I will summarize high level differences under normal circumstances. See the articles above for further details.

Temp Table Table Variable Derived Table CTE
Creation CREATE TABLE #TableName (Columns) DECLARE @TableName table (Columns) SELECT * FROM ( Query ) WITH CTEName AS ( Query ) …

SELECT * FROM CTEName
Storage Location TempDB TempDB Memory Memory
Scope Any statement following their creation for the connection Within the batch, procedure, or function they are declared The Statement it is part of Statements directly following the CTE
Transactions Directly Participate Do not Participate Indirectly Participate Indirectly Participate
Cardinality Statistics are used Considered as 1 record. Inline declarations available starting in SQL 2014 Determined from Base tables Determined from Base tables
Indexes Can be declared Using Primary key work arounds. Inline declarations starting in SQL 2014 Determined from Base tables Determined from Base tables
Logging Logged to TempDB Logged to TempDB Not Directly Not Directly
Usage When you need to perform multiple passes over the data. To aggregate or limit a large data set to join to later.

When referencing multiple times.

 When dealing with relatively small record sets. When you need to pass a table to a function or procedure. When referencing multiple times. When you just need to reference the result a single time in a single statement. When you need to reference a single result multiple times within a single statement. When you need to recursively join a result to itself. Trying to make a large query more readable.

It is important to note that based on the underlying structures and usage. You may get improved performance using one solution over another. You may want to try multiple approaches if you are optimizing for performance.

I will try to cover some other things to try for performance in future articles, such as CROSS APPLY etc.

 

What are some of the reasons you have used one of these types over another.

I would be curious to hear any thoughts you post in the comments below.