Share via

Common Table Expressions in SQL Server 2005 ROCK!!!

  • Article

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 TWITH T(id, sales) AS(SELECT id, sales FROM Employee)-- We can now join to the in-memory result tableSELECTE.id, T.salesFROMEmployee E INNER JOIN T ON E.id = T.idGO

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 ManagerWITH Manager(id, name, mgr_id) AS(-- Neil will be the first row in this table SELECTid, name, mgr_idFROMEmployee WHERE id=3-- To use recusion we must say UNION ALLUNION 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...SELECTEmployee.id,Employee.name,Employee.mgr_idFROMEmployee INNER JOIN ManagerON Employee.mgr_id = Manager.id)-- Now we can select out all the employees-- we found from the in-memory tableSELECT*FROMManagerGO

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 CTEsWITH Manager(id, name, mgr_id, sales) AS(-- Start by selecting *all* employees rather-- than just one.SELECTid, name, mgr_id, salesFROMEmployeeUNION 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.SELECTEmployee.id,name = Manager.name,Employee.mgr_id,Employee.salesFROMEmployee INNER JOIN ManagerON Employee.mgr_id = Manager.id)-- Now we can use an aggregate expression grouped-- by the manager name to find their total salesSELECTname, SUM(sales) as total_salesFROMManagerGROUP BYnameORDER BYtotal_sales DESCGO

I think you'll agree than CTEs are going to be very popular indeed!

Comments