This is the first of a series of posts on the stuff I learnt
at TechEd. Some of it is brand spanking new and some of it, like this one, may
not be so new but definitely overlooked (at least by myself anyway).
Common Expression Tables were introduced in SQL Server 2005,
so this is not a SQL Server 2008 feature. They may seem a bit pointless at
first, but they are in fact awesome.
So a simple example would be
WITH SimpleCTE (ProductName, Price) AS
(
SELECT 'Chair', 5.5
)
SELECT * FROM SimpleCTE
ProductName Price
----------- -------
Chair 5.5
We define a CTE using the keyword WITH called SimpleCTE. It
has two columns, ProductName and Price. Notice that it is not necessary
to specify the type of the columns. The
data for the CTE is a very simple SELECT
statement of two values. The most important part is actually the line that
follows the definition of the CTE, the statement that selects all the rows of
the CTE. The reason it is so important is because it highlights the nature of
CTEs, which is that they are completely dependant on the statement that follows
them. What do I mean about that? Highlight all of the above code and execute as
part of a query. Everything works are normal. Now highlight only the definition
of the CTE omitting the last SELECT statement and execute again. You get an incorrect syntax error. That’s
because CTE cannot exist on their own, they can only exist as part of another
SQL statement.
Right, so you can have pretty much anything you like inside
your CTE; for example
WITH SimpleCTE(ID, [Name], Parent) AS
(
SELECT * FROM Category
)
SELECT * FROM SimpleCTE
ID Name Parent
-------------------- ------------------------- -------
1 Software NULL
2 Hardware NULL
3 Games 1
5 Business 1
6 Motherboards 2
7 CPUs 2
9 Children Games 3
10 Strategy Games 3
11 Small Business 5
12 Enterprise 5
13 ATX Motherboards 6
14 Mini ITX Motherboards 6
15 Intel CPUs 7
16 AMD CPUs 7
WITH SimpleCTE(ID, [Name], Parent) AS
(
SELECT * FROM Category WHERE Parent IS NULL
)
SELECT ID, Name FROM SimpleCTE
ID Name
-------------------- -------------------------
1 Software
2 Hardware
WITH SimpleCTE(ID, [Name], Children) AS
(
SELECT
ID,
Name,
(SELECT
COUNT(*)
FROM
Category ChildCategory
WHERE
ChildCategory.Parent = Category.ID
) AS Children
FROM
Category
)
SELECT ID, [Name], Children FROM SimpleCTE
ID Name Children
-------------------- ------------------------- -----------
1 Software 2
2 Hardware 2
3 Games 2
5 Business 2
6 Motherboards 2
7 CPUs 2
9 Children Games 0
10 Strategy Games 0
11 Small Business 0
12 Enterprise 0
13 ATX Motherboards 0
14 Mini ITX Motherboards 0
15 Intel CPUs 0
16 AMD CPUs 0
In a sense, CTE are very much like views created on the fly.
The SQL statement that follows it is based on the data provided by the CTE.
Things get interesting when you want to perform recursion
WITH SimpleCTE(ID, [Name], HierarchyDepth) AS
(
SELECT
ID,
[Name],
1 AS HierarchyDepth
FROM
Category
WHERE
Parent IS NULL
UNION ALL
SELECT
c.ID,
c.[Name],
SimpleCTE.HierarchyDepth + 1 AS HierarchyDepth
FROM
Category AS c
INNER JOIN SimpleCTE ON c.Parent = SimpleCTE.ID
)
SELECT
ID,
[Name],
HierarchyDepth
FROM
SimpleCTE
ORDER BY
HierarchyDepth
ID Name HierarchyDepth
-------------------- ------------------------- --------------
1 Software 1
2 Hardware 1
6 Motherboards 2
7 CPUs 2
3 Games 2
5 Business 2
11 Small Business 3
12 Enterprise 3
9 Children Games 3
10 Strategy Games 3
15 Intel CPUs 3
16 AMD CPUs 3
13 ATX Motherboards 3
14 Mini ITX Motherboards 3
CTEs are able to join on themselves within their definition,
which enables recursion. No need for cursors ever again :)
Now for what I think is the coolest thing about CTEs. At
the beginning of the post I went through the effort of highlighting their dependence
on the subsequent SQL statement so that I could demonstrate this
WITH SimpleCTE(ID, [Name], HierarchyDepth) AS
(
SELECT
ID,
[Name],
1 AS HierarchyDepth
FROM
Category
WHERE
Parent IS NULL
UNION ALL
SELECT
c.ID,
c.[Name],
SimpleCTE.HierarchyDepth + 1 AS HierarchyDepth
FROM
Category AS c
INNER JOIN SimpleCTE ON c.Parent = SimpleCTE.ID
)
SELECT
ID,
[Name],
HierarchyDepth
FROM
SimpleCTE
WHERE
HierarchyDepth < 3
ORDER BY
HierarchyDepth
ID Name HierarchyDepth
-------------------- ------------------------- --------------
1 Software 1
2 Hardware 1
6 Motherboards 2
7 CPUs 2
3 Games 2
5 Business 2
By specifying a WHERE
clause the results only show the categories that are up to second in the
hierarchy. What actually happened under the covers is that the recursion stopped at that level rather than
completing the recursion and then filtering the results using the WHERE condition. I love this because it’s
what sets it apart from using nested select statements and temporary tables to
achieve the same result. Imagine having a hierarchy 1,000 levels deep, but you are only interested in the first 10 levels ...