BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

SQL Server Common Table Expressions

Avoiding temporary tables and cursors for queries can lead to scripts that are easier to understand and maintain, and that provide better performance than the cursor-based alternative. One option for complex queries is the use of common table expressions.

What are Common Table Expressions?

Common table expressions (CTE), introduced in SQL Server 2005, provide similar functionality to derived tables. They allow you to create a table source, by means of a query, that can be used within the scope of another query or a data manipulation statement, such as an INSERT, UPDATE or DELETE. When you include a CTE in a query, the CTE is processed first to create a named, virtual table. This table's contents can be referenced multiple times in the main query or update command.

As with derived tables, CTEs allow you to create complex queries that would otherwise require the use of temporary tables and cursors, which can be inefficient. CTEs remove the overhead of creating and dropping those temporary tables, and of the INSERT and UPDATE statements required to populate them. This can make for faster execution, though you should always compare all available options when performance is critical.

A feature of CTEs that is not possible with derived tables is the ability to run recursive queries. These are ideal for generating results from hierarchical data structures and can be used to generate ranges of values that contain data that are not present in the database. We'll look at examples of these later in the article.

To demonstrate CTEs we need some sample data. We'll use two tables. The SalesPeople table has a row for each salesperson in an organisation, with each row holding the name of the salesperson and the name or his or her manager. The Sales table holds a row for each sale, with the date of the sale, the salesperson that made it and the monetary value.

CREATE TABLE SalesPeople
(
    Name VARCHAR(3),
    Manager VARCHAR(3)
)

CREATE TABLE Sales
(
    SaleDate SMALLDATETIME,
    SalesPerson VARCHAR(3),
    Value MONEY
)

INSERT INTO SalesPeople VALUES ('Bob', null)
INSERT INTO SalesPeople VALUES ('Jim', 'Bob')
INSERT INTO SalesPeople VALUES ('Sam', 'Bob')
INSERT INTO SalesPeople VALUES ('Mel', 'Jim')
INSERT INTO SalesPeople VALUES ('Nic', 'Jim')
INSERT INTO SalesPeople VALUES ('Pat', 'Mel')
INSERT INTO SalesPeople VALUES ('Ant', 'Mel')

INSERT INTO Sales VALUES ('2012-06-11', 'Bob', 1295)
INSERT INTO Sales VALUES ('2012-06-16', 'Jim', 1495)
INSERT INTO Sales VALUES ('2012-06-16', 'Sam', 95)
INSERT INTO Sales VALUES ('2012-06-21', 'Nic', 1095)
INSERT INTO Sales VALUES ('2012-06-22', 'Jim', 895)
INSERT INTO Sales VALUES ('2012-06-24', 'Pat', 195)
INSERT INTO Sales VALUES ('2012-06-26', 'Mel', 395)

Creating a Common Table Expression

Common table expressions have two parts. The first is the WITH clause. At minimum, the WITH clause names the CTE, allowing you to reference its results in the main query. The second part is the AS clause. This defines the query that will populate the table source. After the CTE, you add a further query or statement that works with the now named data. Once this main query is executed, the CTE data is discarded.

The basic syntax is as follows:

WITH table-source-name AS (query) query

We can see the syntax in a real example below. Here the WITH clause names the CTE data, "AggregatedSales". The query within the parentheses retrieves the names of the salespeople who have made sales, along with their individual sales totals. This is the data that will populate the virtual table.

The query that follows is the one for which the CTE is available. It reads all of the individual Sales rows and joins them to the data from the AggregatedSales table source. For each row, the date, salesperson and single sales value from the Sales table is joined to the total sales value for the appropriate salesperson. You can see the effect for Jim's sales. The two individual values give the total, 2,390, which appears as the aggregate value for both of his data rows.

WITH AggregatedSales
AS
(
    SELECT
        Salesperson,
        SUM(Value) AS TotalSales
    FROM
        Sales
    GROUP BY
        Salesperson
)
SELECT
    convert(VARCHAR, S.SaleDate, 111) AS SaleDate,
    S.Salesperson,
    S.Value,
    A.TotalSales
FROM
    Sales S
INNER JOIN
    AggregatedSales A
ON
    S.Salesperson = A.Salesperson

/* RESULTS

SaleDate     Salesperson Value     TotalSales
------------ ----------- --------- ----------
2012/06/11   Bob         1295.00   1295.00
2012/06/16   Jim         1495.00   2390.00
2012/06/22   Jim         895.00    2390.00
2012/06/26   Mel         395.00    395.00
2012/06/21   Nic         1095.00   1095.00
2012/06/24   Pat         195.00    195.00
2012/06/16   Sam         95.00     95.00

*/
23 June 2012