Common Table Expressions

Common Table Expressions (CTE) или обобщенное табличное выражение, впервые появилось в версии SQL Server 2005, и это простой способ разбить сложный запрос T-SQL на несколько запросов, что придаёт больше гибкости и управляемости. CTE во многом очень похожи на представления. В отличие от представления, которое можно создать один раз и потом использовать в других запросах, CTE привязан только к одному запросу. В Books Online есть несколько отличных примеров CTE, включая и рекурсивные CTE. Вместо того, чтобы продемонстрировать их устройство на своих примерах, в этой статье будут использоваться примеры из Books Online. Чтобы попробовать эти примеры у себя, используйте один из ранних образов базы данных AdventureWorks.

Начнем со следующего простого примера:

WITH DirReps(ManagerID, DirectReports) AS
(
    SELECT ManagerID, COUNT(*) 
    FROM HumanResources.Employee AS e
    WHERE ManagerID IS NOT NULL
    GROUP BY ManagerID
)
SELECT ManagerID, DirectReports
FROM DirReps
ORDER BY ManagerID

Этот запрос идентичен более простому запросу:

SELECT ManagerID, COUNT(*) AS DirectReports
FROM HumanResources.Employee AS e
WHERE ManagerID IS NOT NULL
GROUP BY ManagerID
ORDER BY ManagerID

SQL Server фактически избавляется от CTE, и оба запроса создают один и тот же план исполнения:

  |--Compute Scalar(DEFINE:([Expr1002]=CONVERT_IMPLICIT(int,[Expr1005],0)))
       |--Stream Aggregate(GROUP BY:([e].[ManagerID]) DEFINE:([Expr1005]=Count(*)))
            |--Index Seek(OBJECT:([HumanResources].[Employee].[IX_Employee_ManagerID] AS [e]), SEEK:([e].[ManagerID] IsNotNull) ORDERED FORWARD)

CTE бывают полезны, когда запрос включает несколько экземпляров одного и того же подзапроса. Например, запрос ниже вернёт количество заказов и дату последнего заказа по каждому сотруднику Adventure Works, а затем выдаст ту же информацию для менеджера каждого из сотрудников. Вместо повторения подзапроса, который возвращает количество заказов и дату последнего заказа, можно использовать CTE, что делает запрос более простым и понятным:

WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)
AS
(
    SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
    FROM Sales.SalesOrderHeader
    GROUP BY SalesPersonID
)
SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,
    E.ManagerID, OM.NumberOfOrders, OM.MaxDate
FROM HumanResources.Employee AS E
    JOIN Sales_CTE AS OS
    ON E.EmployeeID = OS.SalesPersonID
    LEFT OUTER JOIN Sales_CTE AS OM
    ON E.ManagerID = OM.SalesPersonID
ORDER BY E.EmployeeID

Этот запрос можно написать и без CTE:

SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,
    E.ManagerID, OM.NumberOfOrders, OM.MaxDate
FROM HumanResources.Employee AS E
    JOIN (
        SELECT SalesPersonID, COUNT(*) AS NumberOfOrders, MAX(OrderDate) AS MaxDate
        FROM Sales.SalesOrderHeader
        GROUP BY SalesPersonID
        ) AS OS
    ON E.EmployeeID = OS.SalesPersonID
    LEFT OUTER JOIN (
        SELECT SalesPersonID, COUNT(*) AS NumberOfOrders, MAX(OrderDate) AS MaxDate
        FROM Sales.SalesOrderHeader
        GROUP BY SalesPersonID
        ) AS OM
    ON E.ManagerID = OM.SalesPersonID
ORDER BY E.EmployeeID

В качестве альтернативы можно использовать представление, хотя для этого потребуется явно его создать (и потом удалить, если не нужно его хранить). Представления менее удобны и более затратны (DDL не бесплатен), чем CTE. Если вы создаёте и удаляете представления «на лету», вы должны быть осторожны, и не использовать имя, которое будет конфликтовать с другими сеансами, в которых также может в это время создаваться такое же представление.

CREATE VIEW Sales_View (SalesPersonID, NumberOfOrders, MaxDate) AS
SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
FROM Sales.SalesOrderHeader
GROUP BY SalesPersonID
GO
SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,
    E.ManagerID, OM.NumberOfOrders, OM.MaxDate
FROM HumanResources.Employee AS E
    JOIN Sales_View AS OS
    ON E.EmployeeID = OS.SalesPersonID
    LEFT OUTER JOIN Sales_View AS OM
    ON E.ManagerID = OM.SalesPersonID
ORDER BY E.EmployeeID;
GO
DROP VIEW Sales_View
GO

Все три варианта создают один и тот же план запроса:

  |--Nested Loops(Left Outer Join, WHERE:([E].[ManagerID]=[Sales].[SalesOrderHeader].[SalesPersonID]))
       |--Sort(ORDER BY:([E].[EmployeeID] ASC))
       |    |--Nested Loops(Inner Join, OUTER REFERENCES:([Sales].[SalesOrderHeader].[SalesPersonID]))
       |         |--Compute Scalar(DEFINE:([Expr1005]=CONVERT_IMPLICIT(int,[Expr1021],0)))
       |         |    |--Hash Match(Aggregate, HASH:([Sales].[SalesOrderHeader].[SalesPersonID]), RESIDUAL:(...) DEFINE:([Expr1021]=COUNT(*), [Expr1006]=MAX([Sales].[SalesOrderHeader].[OrderDate])))
       |         |         |--Clustered Index Scan(OBJECT:([Sales].[SalesOrderHeader].[PK_SalesOrderHeader_SalesOrderID]))
       |         |--Clustered Index Seek(OBJECT:([HumanResources].[Employee].[PK_Employee_EmployeeID] AS [E]), SEEK:([E].[EmployeeID]=[Sales].[SalesOrderHeader].[SalesPersonID]) ORDERED FORWARD)
       |--Table Spool
            |--Compute Scalar(DEFINE:([Expr1010]=[Expr1010], [Expr1011]=[Expr1011]))
                 |--Compute Scalar(DEFINE:([Expr1010]=CONVERT_IMPLICIT(int,[Expr1022],0)))
                      |--Hash Match(Aggregate, HASH:([Sales].[SalesOrderHeader].[SalesPersonID]), RESIDUAL:(...) DEFINE:([Expr1022]=COUNT(*), [Expr1011]=MAX([Sales].[SalesOrderHeader].[OrderDate])))
                           |--Clustered Index Scan(OBJECT:([Sales].[SalesOrderHeader].[PK_SalesOrderHeader_SalesOrderID]))

Обратите внимание, что в плане запроса результат CTE вычисляется дважды (по одному разу для каждой ссылки). Там есть два просмотра таблицы SalesOrderHeader и два хэш-агрегата. Даже при использовании CTE (или представления) SQL Server будет вычислять результат дважды. Не дайте ввести вас в заблуждение этому Table Spool. Эта буферизация просто кэширует результат CTE, чтобы избежать вычисления всего агрегата один раз для каждого сотрудника.

Можно заставить SQL Server вычислить результат CTE только один раз, явно материализовав его с помощью табличной переменной, временной таблицы или индексированного представления. Вот пример использования табличной переменной:

DECLARE @Sales_Data TABLE (SalesPersonID INT, NumberOfOrders INT, MaxDate DATETIME)

INSERT @Sales_Data
SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
FROM Sales.SalesOrderHeader
GROUP BY SalesPersonID
SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,
    E.ManagerID, OM.NumberOfOrders, OM.MaxDate
FROM HumanResources.Employee AS E
    JOIN @Sales_Data AS OS
    ON E.EmployeeID = OS.SalesPersonID
    LEFT OUTER JOIN @Sales_Data AS OM
    ON E.ManagerID = OM.SalesPersonID
ORDER BY E.EmployeeID

Этот вариант запроса состоит из двух операторов и, следовательно, имеет два плана. Первый план материализует результат CTE в табличную переменную, а второй план дважды её читает.

  |--Table Insert(OBJECT:(@Sales_Data), SET:([SalesPersonID] = [Sales].[SalesOrderHeader].[SalesPersonID],[NumberOfOrders] = [Expr1007],[MaxDate] = [Expr1008]))
       |--Top(ROWCOUNT est 0)
            |--Compute Scalar(DEFINE:([Expr1007]=CONVERT_IMPLICIT(int,[Expr1012],0)))
                 |--Hash Match(Aggregate, HASH:([Sales].[SalesOrderHeader].[SalesPersonID]), RESIDUAL:(...) DEFINE:([Expr1012]=COUNT(*), [Expr1008]=MAX([Sales].[SalesOrderHeader].[OrderDate])))
                      |--Clustered Index Scan(OBJECT:([Sales].[SalesOrderHeader].[PK_SalesOrderHeader_SalesOrderID]))

  |--Nested Loops(Left Outer Join, WHERE:([E].[ManagerID]=[OM].[SalesPersonID]))
       |--Nested Loops(Inner Join, OUTER REFERENCES:([OS].[SalesPersonID]))
       |    |--Sort(ORDER BY:([OS].[SalesPersonID] ASC))
       |    |    |--Table Scan(OBJECT:(@Sales_Data AS [OS]))
       |    |--Clustered Index Seek(OBJECT:([HumanResources].[Employee].[PK_Employee_EmployeeID] AS [E]), SEEK:([E].[EmployeeID]=[OS].[SalesPersonID]) ORDERED FORWARD)
       |--Table Scan(OBJECT:(@Sales_Data AS [OM]))

Явная материализация результата CTE (как и просмотра или подзапроса) не всегда обеспечивает лучшую производительность. Во-первых, мы должны учитывать стоимость создания и заполнения временной таблицы. Если стоимость самого CTE не слишком велика, может оказаться дешевле просто вычислить результат CTE нужное число раз. Во-вторых, бывает, что оптимизатор может найти более удачный план, в котором не понадобиться получать все строки CTE. Например, предположим, что мы хотим вычислить число заказов только по одному сотруднику:

DECLARE @EID INT

SET @EID = 268;
WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)
AS
(
    SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
    FROM Sales.SalesOrderHeader
    GROUP BY SalesPersonID
)
SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,
    E.ManagerID, OM.NumberOfOrders, OM.MaxDate
FROM HumanResources.Employee AS E
    JOIN Sales_CTE AS OS
    ON E.EmployeeID = OS.SalesPersonID
    LEFT OUTER JOIN Sales_CTE AS OM
    ON E.ManagerID = OM.SalesPersonID
WHERE E.EmployeeID = @EID

Этот запрос дает другой план:

  |--Nested Loops(Left Outer Join, OUTER REFERENCES:([E].[ManagerID]))
       |--Nested Loops(Inner Join)
       |    |--Clustered Index Seek(OBJECT:([HumanResources].[Employee].[PK_Employee_EmployeeID] AS [E]), SEEK:([E].[EmployeeID]=[@EID]) ORDERED FORWARD)
       |    |--Compute Scalar(DEFINE:([Expr1005]=CONVERT_IMPLICIT(int,[Expr1021],0)))
       |         |--Stream Aggregate(DEFINE:([Expr1021]=Count(*), [Expr1006]=MAX([Sales].[SalesOrderHeader].[OrderDate])))
       |              |--Clustered Index Scan(OBJECT:([Sales].[SalesOrderHeader].[PK_SalesOrderHeader_SalesOrderID]), WHERE:([Sales].[SalesOrderHeader].[SalesPersonID]=[@EID]) )
       |--Compute Scalar(DEFINE:([Expr1010]=[Expr1010], [Expr1011]=[Expr1011]))
            |--Compute Scalar(DEFINE:([Expr1010]=CONVERT_IMPLICIT(int,[Expr1022],0)))
                 |--Stream Aggregate(DEFINE:([Expr1022]=Count(*), [Expr1011]=MAX([Sales].[SalesOrderHeader].[OrderDate])))
                      |--Clustered Index Scan(OBJECT:([Sales].[SalesOrderHeader].[PK_SalesOrderHeader_SalesOrderID]), WHERE:([E].[ManagerID]=[Sales].[SalesOrderHeader].[SalesPersonID]) )

Исходный план получал число заказов и дату последнего заказа для всех сотрудников, а этот план получает это для одного сотрудника и его руководителя. Фильтрацию при сканировании кластерного индекса таблицы SalesOrderHeader обеспечивают предикаты WHERE. И этот план использует скалярные Stream Aggregate вместо Hash Match агрегатов в исходном плане. Наконец, в этом примере оптимизатор выбрал просмотр, но, если бы данных в таблице было больше, а предикаты были бы более избирательными, он выбрал бы поиск по индексу, что избавляет план от дорогого просмотра всей таблицы.