Temporary Tables – Part 5 – Ramblings of a Crafty DBA

Today, I am going to talking about common table expressions. A common table expression (CTE) is a temporary result set that is defined within the execution scope of a single DML statement. A CTE is simular to a derived table in which the results are not stored as an object; However, CTE’s can be recursive or built upon other CTE’s.

The WITH key word is used to indicate the start of a CTE and needs to be preceeded with a semicolon. The rest of the syntax is like a stored procedure — name of object, fields of object, AS keyword, and body of object (query). The only difference is the body is between two brackets () instead of a BEGIN and END. A CTE must be followed by a single DML statement such as SELECT, DELETE, INSERT, UPDATE, or MERGE.

The first example of a common table expression creates the car ownership table for person A. Please see the AUTOS database I created in a earlier article. The cool thing about this example is that the data is in-line VALUES and does not come from a table.

<span style="color: #008000;">-- Database selection does not matter
USE model;
GO

-- Create a list & show contents
WITH CTE_LIST(MyId, MyValue)
AS
(  
SELECT * FROM 
  (
    VALUES 
    (1, 'Continental'),
    (2, 'Edsel'),
    (3, 'Lincoln'),
    (4, 'Mercury'),
    (5, 'Ram')
  ) AS A (MyId, MyValue)
)
SELECT * FROM CTE_LIST;
GO
</span>

-- Database selection does not matter

USE model;

-- Create a list & show contents

WITH CTE_LIST(MyId, MyValue)

(

SELECT * FROM

(

VALUES

(1, 'Continental'),

(2, 'Edsel'),

(3, 'Lincoln'),

(4, 'Mercury'),

(5, 'Ram')

) AS A (MyId, MyValue)

)

SELECT * FROM CTE_LIST;

I will be using the Adventure Works Sample database supplied by Microsoft during the rest of this talk.

A common query pattern is to get the most recent id by date. Joining these results to the original table will bring back the most recent record by date.

The SELECT query below uses the last order by customer id as common table expression named MC. The main query uses the CTE to return customer name, sales id, sub-total and order date. This might be a query for the SALES team to cold call customers that have not ordered in the past x months.

<span style="color: #008000;">-- Use the correct databaseg
USE AdventureWorks2008R2
GO


-- Get customer name, sales id, sub-total and order date
WITH CTE_MC (MyCustomer, MyDate)
AS
(
    SELECT 
      CustomerID AS MyCustomer,
      MAX(OrderDate) AS MyDate
    FROM 
      Sales.SalesOrderHeader
    WHERE
      YEAR(OrderDate) = 2008 
    GROUP BY 
  	  CustomerID
)

SELECT 
  P.FirstName, 
  P.LastName,
  SH.SalesOrderID, 
  SH.SubTotal,
  SH.OrderDate
FROM
  Sales.SalesOrderHeader AS SH 
  INNER JOIN CTE_MC AS MC ON SH.CustomerID = MC.MyCustomer AND SH.OrderDate = MC.MyDate
  INNER JOIN Sales.Customer C ON MC.MyCustomer = C.CustomerID
  INNER JOIN Person.Person P ON C.PersonID = P.BusinessEntityID
ORDER BY 
  SH.OrderDate ASC;
GO
</span>

-- Use the correct databaseg

USE AdventureWorks2008R2

-- Get customer name, sales id, sub-total and order date

WITH CTE_MC (MyCustomer, MyDate)

(

SELECT

CustomerID AS MyCustomer,

MAX(OrderDate) AS MyDate

FROM

Sales.SalesOrderHeader

WHERE

YEAR(OrderDate) = 2008

GROUP BY

CustomerID

)

SELECT

P.FirstName,

P.LastName,

SH.SalesOrderID,

SH.SubTotal,

SH.OrderDate

FROM

Sales.SalesOrderHeader AS SH

INNER JOIN CTE_MC AS MC ON SH.CustomerID = MC.MyCustomer AND SH.OrderDate = MC.MyDate

INNER JOIN Sales.Customer C ON MC.MyCustomer = C.CustomerID

INNER JOIN Person.Person P ON C.PersonID = P.BusinessEntityID

ORDER BY

SH.OrderDate ASC;

I am going to create a duplicate of the department table from the Adventure Works database.
This table will be used in the examples below.

<span style="color: #008000;">-- Remove department staging table
IF  EXISTS (
    SELECT * 
    FROM sys.objects 
    WHERE object_id = OBJECT_ID(N'[STAGE].[Department]') AND type in (N'U')
    )
    DROP TABLE [STAGE].[Department]
GO

-- Recreate table from AW entries
SELECT * INTO [STAGE].[Department] 
  FROM [HumanResources].[Department]
GO

-- Add a default date
ALTER TABLE [STAGE].[Department] ADD CONSTRAINT df_Modified_Date
  DEFAULT GETDATE() FOR [ModifiedDate];
GO
</span>

-- Remove department staging table

IF EXISTS (

SELECT *

FROM sys.objects

WHERE object_id = OBJECT_ID(N'[STAGE].[Department]') AND type in (N'U')

)

DROP TABLE [STAGE].[Department]

-- Recreate table from AW entries

SELECT * INTO [STAGE].[Department]

FROM [HumanResources].[Department]

-- Add a default date

ALTER TABLE [STAGE].[Department] ADD CONSTRAINT df_Modified_Date

DEFAULT GETDATE() FOR [ModifiedDate];

Common table expressions can be used as the target of an UPDATE statement. The query below updates the two Quality Assurance deparment names with a location ‘US’ as a prefix. We are doing this to get ready for making a ASIA department with the same job functions.

<span style="color: #008000;">-- Update group to US
WITH CTE_DEPT (GroupName)
AS
(
    SELECT GroupName FROM [STAGE].[Department]
    WHERE GroupName = 'Quality Assurance'
)
UPDATE CTE_DEPT SET GroupName = 'US Quality Assurance';
GO
</span>

-- Update group to US

WITH CTE_DEPT (GroupName)

(

SELECT GroupName FROM [STAGE].[Department]

WHERE GroupName = 'Quality Assurance'

)

UPDATE CTE_DEPT SET GroupName = 'US Quality Assurance';

Common table expressions can be inserted into directly if they are based upon a simple table. Just like insertion rules for views, aggregation and joins can make a CTE non-deterministic. The query below INSERTs two new ASIA departments like their US counterparts.

<span style="color: #008000;">-- Insert group for ASIA
WITH CTE_DEPT(Name, GroupName)
AS
(
      SELECT Name, GroupName
      FROM [STAGE].[Department]
      WHERE GroupName = 'US Quality Assurance'
)
INSERT INTO CTE_DEPT
SELECT D.Name, 'ASIA ' + SUBSTRING(D.GroupName, 4, LEN(D.GroupName) - 3) 
FROM CTE_DEPT AS D
GO
</span>

-- Insert group for ASIA

WITH CTE_DEPT(Name, GroupName)

(

SELECT Name, GroupName

FROM [STAGE].[Department]

WHERE GroupName = 'US Quality Assurance'

)

INSERT INTO CTE_DEPT

SELECT D.Name, 'ASIA ' + SUBSTRING(D.GroupName, 4, LEN(D.GroupName) - 3)

FROM CTE_DEPT AS D

Last but not least, derived tables can be used as the target of a DELETE statement.

<span style="color: #008000;">-- Delete group for US
WITH CTE_DEPT(Name, GroupName)
AS
(
      SELECT Name, GroupName
      FROM [STAGE].[Department]
      WHERE GroupName = 'US Quality Assurance'
)
DELETE FROM CTE_DEPT;
GO

-- Show the data
SELECT * FROM [STAGE].[Department];
GO
</span>

-- Delete group for US

WITH CTE_DEPT(Name, GroupName)

(

SELECT Name, GroupName

FROM [STAGE].[Department]

WHERE GroupName = 'US Quality Assurance'

)

DELETE FROM CTE_DEPT;

-- Show the data

SELECT * FROM [STAGE].[Department];

In short, common table expressions (CTE’s) are useful when you want to combine the results of several CTE’s into one large result set. The real power of CTE’s comes with recursion. Traversing a hierarchial table structure can be accomplished with self referencing CTE’s. I did not talk about this topic since it is not related to storing results into a temporary table (object).

Next time, I want to analyze performance, disk space, and query plans of all five ways to create temporary objects to solve a business problem.

3 Thoughts to “Temporary Tables – Part 5”

Linda Cochrane

December 19, 2011 at 9:46 am

Lots of helpful information. I have bookmarked your site.

Myron Woodson

December 19, 2011 at 6:53 pm

I just want to mention I’m beginner to blogging and honestly loved you’re web blog. Very likely I’m want to bookmark your blog . You really have terrific writings. Regards for sharing with us your blog site.

Mariano Kinsland

February 4, 2012 at 9:07 pm

Wohh just what I was searching for, thank you for putting up.

Temporary Tables – Part 5

Related posts

3 Thoughts to “Temporary Tables – Part 5”

Leave a Comment Cancel reply

Related posts

Don’t pass over synonyms

Is my string a number?

Replacing Data – Part 2

3 Thoughts to “Temporary Tables – Part 5”

Leave a Comment Cancel reply