I have been asked what is the difference between Temp Tables, Table Variables, CTEs, and Derived tables. So I thought I would take this opportunity to do a short blog post on each and then summarize the differences.
I like to think of a CTE (Common Table Expression) as a temporary view to help make code more readable, but it’s biggest benefit has to be recursion. Outside of recursion, I typically use a CTE when I have a derived table that is very long or that I need to reference multiple times in my statement.
Creation
A CTE can be created for use in your statements by simply specifying the query like so:
;WITH [CTE_NAME] ( Optional Column List ) AS (
[Your Select Query]
)
[Your Statement Referencing the CTE, such as SELECT * FROM [CTE_NAME]] …
Note: The statement that precedes the CTE declaration needs to be terminated with a semicolon which is why you typically see it referenced in samples and in code at the beginning.
Here is a sample of a non-recursive CTE where I used the CTE in order to sequence effective dated entries . Then I join the CTE to itself in order to determine the valid date ranges for the settings associated to each code.
USE tempdb
GO
CREATE TABLE [dbo].[CPTCodes]
(
[CPTCodeKey] INT IDENTITY(1,1) NOT NULL,
[CPTCode] VARCHAR(10) NOT NULL,
[EffectiveStartDate] DATE NOT NULL,
[BaseUnit] INT NOT NULL,
[AddTime] BIT NOT NULL,
CONSTRAINT PK_CPTCodes PRIMARY KEY ( [CPTCodeKey] )
)
GO
CREATE UNIQUE INDEX UIX_CONFIG_CPTCodes_CPTCode_EffectiveStartDate ON dbo.CPTCodes (CPTCode, EffectiveStartDate)
GO
INSERT [dbo].[CPTCodes] ([CPTCode], [EffectiveStartDate], [BaseUnit], [AddTime])
VALUES (N’00100′, CAST(‘20130101’ AS Date), 5, 1),
(N’00102′, CAST(‘20130101’ AS Date), 7, 1),
(N’00103′, CAST(‘20130101’ AS Date), 9, 1),
(N’01444′, CAST(‘20130101’ AS Date), 8, 1),
(N’01462′, CAST(‘20130101’ AS Date), 3, 1),
(N’01953′, CAST(‘20130101’ AS Date), 1, 0),
(N’64413′, CAST(‘20130101’ AS Date), 6, 0),
(N’64415′, CAST(‘20130101’ AS Date), 5, 0),
(N’99999′, CAST(‘20130101’ AS Date), 11, 1),
(N’00100′, CAST(‘20150101’ AS Date), 6, 1)
WITH CTE_CodeSequencing AS (
SELECT
C.CPTCodeKey,
C.CPTCode,
C.BaseUnit,
C.AddTime,
C.EffectiveStartDate,
ROW_NUMBER() OVER ( PARTITION BY C.CPTCode ORDER BY C.EffectiveStartDate ASC) as CodeSequence
FROM dbo.CPTCodes C
)
SELECT
CSc.CPTCodeKey as CPTCodeKey,
CSc.CPTCode as CPTCode,
CSc.BaseUnit as BaseUnit,
CSc.AddTime as AddTime,
CSc.EffectiveStartDate as EffectiveStartDate,
COALESCE(DATEADD(DAY,-1,CSn.EffectiveStartDate), ‘99991231’) as EffectiveEndDate
FROM CTE_CodeSequencing CSc
LEFT JOIN CTE_CodeSequencing CSn ON CSn.CPTCode = CSc.CPTCode
AND CSn.CodeSequence = CSc.CodeSequence + 1
A typical use for using a CTE for recursion would be an employee table where each employee has a reference to another employee record (their manager for example). You can check that out here.
Here is a simple recursion example. This CTE reference itself in the declaration to keep adding to itself. There is a default recursion limit of 100. If you need more recursive loops than that then you have to specify the MAX_RECURSION property.
WITH CTE_Recursive AS (
SELECT 1 as Value
UNION ALL
SELECT Value + Value as Value –lets add the value to itself
FROM CTE_Recursive –referencing itself…WHAT!? that is recursion
WHERE Value < 100
)
SELECT Value
FROM CTE_Recursive
ORDER BY Value
OPTION (MAXRECURSION 1000) –if you need to override the default of 100
Storage Location
CTEs not stored physically in tempDB. They are compiled in-line with the rest of the query and should be stored in memory, unless your server is memory starved.
Scope
A CTE is only available within the statement it precedes. It can be referenced by other CTEs preceding the statement and it can be referenced multiple times within that statement.
Here is a simple example of a CTE being referenced within other CTEs to generate a list of 100 numbers.
;WITH CTE_0 AS (SELECT 0 g UNION ALL SELECT 0)
,CTE_1 AS (SELECT 0 g FROM CTE_0 a CROSS JOIN CTE_0 b) — 4
,CTE_2 AS (SELECT 0 g FROM CTE_1 a CROSS JOIN CTE_1 b) — 16
,CTE_3 AS (SELECT 0 g FROM CTE_2 a CROSS JOIN CTE_2 b) — 256
,CTE_Numbers AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as Number FROM CTE_3)
SELECT TOP (100) Number
FROM CTE_Numbers
ORDER BY Number;
Here is a simple example of a CTE being referenced multiple times within the same statement to generate a list of number.
WITH CTE_Numbers AS (SELECT 0 as Number UNION ALL SELECT 0 as Number)
SELECT TOP (100)
ROW_NUMBER() OVER ( ORDER BY (SELECT NULL)) as Number
FROM CTE_Numbers a
CROSS JOIN CTE_Numbers b –4
CROSS JOIN CTE_Numbers c –8
CROSS JOIN CTE_Numbers d –16
CROSS JOIN CTE_Numbers e –32
CROSS JOIN CTE_Numbers f –64
CROSS JOIN CTE_Numbers g –128
ORDER BY Number;
Transactions
CTEs are not physical entities and as such only participate in transactions so far as the statement it is part of.
Cardinality
CTEs are optimized in-line with the rest of the query. Statistics on the tables referenced in the CTE may be used.
Indexes
CTEs are optimized in-line with the rest of the query. Indexes on the tables referenced in the CTE may be used.
Logging
There is no logging as having directly used a CTE. However, if it is used as part of an Insert, Update, or Delete statement then the data modification is logged. Remember, a CTE is not itself a physical entity.
So if I was a CTE and was out looking for a t-shirt that described how special I was it would say something like …
Do you have any questions, or tips and tricks for using CTEs?
Let’s discuss in the comments below.
