Common Table Expressions in SQL Server 2005 ROCK!!!
I've been taking a look at a new feature in SQL Server 2005 called "Common Table Expressions" (hereafter CTEs). This is an extra-ordinally powerful extenstion to Transact SQL that removes the need for managing temporary tables in many common scenarios.
In this example code I will set up a typical Employee table that is self-referencing; each Employee has a manager also in the Employee table. I've tested this code on IDW-15 (June CTP) only, but its been in the builds for a while so should work on the April CTP with no problems.
Here is the setup code:
USE master
GO
-- Drop the database if it already exists
IF EXISTS (
SELECT name
FROM sys.databases
WHERE name = N'CTE_Rank_Demo'
)
DROP DATABASE CTE_Rank_Demo
GO
CREATE DATABASE CTE_Rank_Demo
GO
USE CTE_Rank_Demo
GO
-- =========================================
-- Create demo table and data
-- This is a bunch of employees, each
-- having a manager which is a fk back
-- to the table.
-- =========================================
CREATE TABLE Employee
(
id int IDENTITY CONSTRAINT PK_Employee_id PRIMARY KEY CLUSTERED,
name nvarchar(50),
mgr_id int CONSTRAINT FK_Employee_mgr_id FOREIGN KEY REFERENCES Employee(id),
country varchar(2),
age int,
sales int
)
GO
-- Create some sample data
INSERT Employee VALUES('Steve', NULL, 'UK', 50, 200)
INSERT Employee VALUES('James', 1, 'UK', 30, 500)
INSERT Employee VALUES('Neil', 1, 'US', 35, 600)
INSERT Employee VALUES('Blair', 2, 'AU', 41, 250)
INSERT Employee VALUES('Ken', 1, 'DE', 38, 100)
INSERT Employee VALUES('Paul', 3, 'FR', 36, 480)
INSERT Employee VALUES('Adrian', 3, 'FR', 32, 290)
INSERT Employee VALUES('Ian', 4, 'UK', 27, 120)
INSERT Employee VALUES('Matt', 7, 'DE', 34, 10)
GO
|
OK, now lets look at a simple CTE.
-- 1. A really simple and not very useful
-- CTE example to introduce the syntax
-- The WITH clause introduces a CTE and
-- we define an in-memory result table
-- called T
WITH T(id, sales) AS
(
SELECT id, sales FROM Employee
)
-- We can now join to the in-memory result table
SELECT
E.id, T.sales
FROM
Employee E INNER JOIN T ON E.id = T.id
GO
|
You get back a list of employees and their sales total.
Big deal, we could have just done that in one simple query anyway. So lets look at something more interesting... recursive CTEs! Here is a sample that gets back a list of an employee and all employees that reports to him and all employees that report to them... etc. Basically the subtree of the orgtree rooted at a particular employee.
-- 2. Use a common table expression
-- with recursion to get Neil and
-- all employees he is responsible for
-- A recusive CTE is denoted by two queries
-- joined by a UNION ALL operator and
-- the second query references the CTE table
-- itself.
-- The WITH clause introduces a CTE and
-- we define an in-memory result table
-- called Manager
WITH Manager(id, name, mgr_id) AS
(
-- Neil will be the first row in this table
SELECT
id, name, mgr_id
FROM
Employee WHERE id=3
-- To use recusion we must say UNION ALL
UNION ALL
-- Now the following select table will recurse
-- At the first level of recursion it will find Neil's
-- direct reports and insert these into the
-- Manager table. Then it will be called again
-- on each of the the new rows added to the table to find
-- their direct reports and so on...
SELECT
Employee.id,
Employee.name,
Employee.mgr_id
FROM
Employee INNER JOIN Manager
ON Employee.mgr_id = Manager.id
)
-- Now we can select out all the employees
-- we found from the in-memory table
SELECT
*
FROM
Manager
GO
|
Awesome! Now let's look at a really cool example... this query uses aggregates to return the total sales of each employee and all the employees below them...
-- 3. Now lets use a recursive CTE to
-- get the total sales of each
-- employee and all their reports!!!
-- This really shows the power of CTEs
WITH Manager(id, name, mgr_id, sales) AS
(
-- Start by selecting *all* employees rather
-- than just one.
SELECT
id, name, mgr_id, sales
FROM
Employee
UNION ALL
-- This is almost the same as before,
-- but now we are recording the name
-- of the manager rather than the employee
-- name and copying that name as we
-- recurse so that each row is tied back to
-- the originating Employee in the first query
-- of the CTE.
SELECT
Employee.id,
name = Manager.name,
Employee.mgr_id,
Employee.sales
FROM
Employee INNER JOIN Manager
ON Employee.mgr_id = Manager.id
)
-- Now we can use an aggregate expression grouped
-- by the manager name to find their total sales
SELECT
name, SUM(sales) as total_sales
FROM
Manager
GROUP BY
name
ORDER BY
total_sales DESC
GO
|
I think you'll agree than CTEs are going to be very popular indeed!
