WITH common_table_expression (Transact-SQL)

As diretrizes a seguir se aplicam a expressões de tabela comuns não recursivas. Para verificar as diretrizes relacionadas a expressões de tabela comuns recursivas, consulte "Diretrizes para definir e usar expressões de tabela comuns recursivas" a seguir.

• Uma CTE deve ser seguida por uma única instrução SELECT, INSERT, UPDATE, MERGE ou DELETE que faça referência a algumas ou a todas as colunas da CTE. Uma CTE também pode ser especificada em uma instrução CREATE VIEW como parte da instrução SELECT que define a exibição.

• É possível ter várias definições de consulta CTE em uma CTE não recursiva. As definições devem ser combinadas por um destes operadores de conjunto: UNION ALL, UNION, INTERSECT ou EXCEPT.

• Uma CTE pode fazer referência a si mesma e a CTEs definidas anteriormente na mesma cláusula WITH. Não é permitido referência antecipada.

• Não é permitida a especificação de mais de uma cláusula WITH em uma CTE. Por exemplo, se uma CTE_query_definition contiver uma subconsulta, essa subconsulta não poderá conter uma cláusula WITH aninhada que defina outra CTE.

• As seguintes cláusulas não podem ser usadas na CTE_query_definition:

  • COMPUTE ou COMPUTE BY

  • ORDER BY (exceto quando uma cláusula TOP for especificada)

  • INTO

  • Cláusula OPTION com dicas de consulta

  • FOR XML

  • FOR BROWSE

• Quando uma CTE for usada em uma instrução que faça parte de um lote, a instrução anterior a ela deverá ser seguida por um ponto-e-vírgula.

• Uma consulta que faça referência a uma CTE pode ser usada para definir um cursor.

• As tabelas em servidores remotos podem ser referenciadas na CTE.

• Ao executar uma CTE, quaisquer dicas que façam referência a uma CTE podem ficar em conflito com outras dicas que forem descobertas quando a CTE acessar suas tabelas subjacentes, da mesma maneira que as dicas que façam referência a exibições em consultas. Quando isso acontece, a consulta retorna um erro. Para obter mais informações, consulte Resolução de exibição.

• Quando uma CTE for o destino de uma instrução UPDATE, todas as referências à CTE na instrução devem ser correspondentes. Por exemplo, se for atribuído à CTE um alias na cláusula FROM, o alias deverá ser usado para todas as outras referências à CTE. Referências ambíguas à CTE podem gerar comportamento de junção inesperado e resultados de consulta não intencionais. Para obter mais informações, consulte UPDATE (Transact-SQL).

As seguintes diretrizes aplicam-se à definição de uma expressão de tabela comum recursiva:

• A definição da CTE recursiva deve conter pelo menos duas definições de consulta de CTE, um membro de ancoragem e um membro recursivo. É possível definir vários membros de ancoragem e membros recursivos; porém, todas as definições de consulta de membro de ancoragem devem ser colocadas antes da primeira definição de membro recursivo. Todas as definições de consulta CTEsão membros de ancoragem, a menos que façam referência à própria CTE.

• Os membros de ancoragem devem ser combinados por um destes operadores de conjunto: UNION ALL, UNION, INTERSECT ou EXCEPT. UNION ALL é o único operador de conjunto permitido entre o último membro de ancoragem e o primeiro membro recursivo e ao combinar vários membros recursivos.

• O número de colunas nos membros de ancoragem e recursivos deve ser o mesmo.

• O tipo de dados de uma coluna no membro recursivo deve ser o mesmo que o tipo de dados da coluna correspondente no membro de ancoragem.

• A cláusula FROM de um membro recursivo deve fazer referência apenas uma vez à CTE expression_name.

• Os seguintes itens não são permitidos na CTE_query_definition de um membro recursivo:

  • SELECT DISTINCT

  • GROUP BY

  • HAVING

  • Agregação escalar

  • TOP

  • LEFT, RIGHT, OUTER JOIN (INNER JOIN é permitido)

  • Subconsultas

  • Uma dica aplicada a uma referência recursiva para uma CTE dentro de uma CTE_query_definition.

As seguintes diretrizes aplicam-se ao uso de uma expressão de tabela comum recursiva:

• Todas as colunas retornadas pela CTE recursiva aceitam valores nulos, independentemente da possibilidade de nulidade das colunas retornadas pelas instruções SELECT participantes.

• Uma CTE recursiva incorretamente composta pode causar um loop infinito. Por exemplo, se a definição de consulta do membro recursivo retornar os mesmos valores para as colunas pai e filha, um loop infinito será criado. Para evitar um loop infinito, é possível limitar o número de níveis de recursão permitidos para uma instrução específica, usando a dica MAXRECURSION e um valor entre 0 e 32.767 na cláusula OPTION da instrução INSERT, UPDATE, MERGE, DELETE ou SELECT. Isso permite controlar a execução da instrução até que você resolva o problema de código que está criando o loop. O padrão para todo o servidor é 100. Quando 0 for especificado, nenhum limite será aplicado. Apenas um valor MAXRECURSION pode ser especificado por instrução. Para obter mais informações, consulte dicas de consulta (Transact-SQL).

• Uma exibição que contém uma expressão de tabela comum recursiva não pode ser usada para atualizar dados.

• É possível definir cursores em consultas usando CTEs. CTE é o argumento select_statement que define o conjunto de resultados do cursor. Apenas cursores de somente avanço rápido e estáticos (instantâneo) são permitidos para CTEs recursivas. Se outro tipo de cursor for especificado em uma CTE recursiva, o tipo de cursor será convertido em estático.

• As tabelas em servidores remotos podem ser referenciadas na CTE. Se o servidor remoto for referenciado no membro recursivo da CTE, um spool será criado para cada tabela remota, de maneira que as tabelas possam ser acessadas localmente várias vezes. Se essa for uma consulta CTE, Index Spool/Lazy Spools será exibido no plano de consulta e terá o predicado adicional WITH STACK. Essa é uma maneira de confirmar a recursão correta.

• O SQL Server 2008 não permite funções analíticas e de agregação na parte recursiva da CTE.

O exemplo a seguir mostra o número de funcionários subordinados diretamente a cada gerente na Adventure Works Cycles.

USE AdventureWorks;
GO
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;
GO

O exemplo a seguir mostra o número médio de funcionários subordinados a gerentes.

WITH DirReps (Manager, DirectReports) AS 
(
    SELECT ManagerID, COUNT(*) AS DirectReports
    FROM HumanResources.Employee
    GROUP BY ManagerID
) 
SELECT AVG(DirectReports) AS [Average Number of Direct Reports]
FROM DirReps 
WHERE DirectReports>= 2 ;
GO

O exemplo a seguir mostra o número total de ordens de venda e a data da ordem de venda mais recente na tabela SalesOrderHeader para cada vendedor. Na instrução em execução, a CTE é referenciada duas vezes: uma para retornar as colunas selecionadas para o vendedor e novamente para recuperar detalhes semelhantes para o gerente do vendedor. Os dados para o vendedor e o gerente são retornados em uma única linha.

USE AdventureWorks;
GO
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;
GO

O exemplo a seguir mostra a lista hierárquica de gerentes e os funcionários subordinados a eles.

USE AdventureWorks;
GO
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS 
(
    SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
    FROM HumanResources.Employee e
        INNER JOIN DirectReports d
        ON e.ManagerID = d.EmployeeID 
)
SELECT ManagerID, EmployeeID, EmployeeLevel 
FROM DirectReports ;
GO

O exemplo a seguir mostra os gerentes e os funcionários subordinados a eles. O número de níveis retornado está limitado a dois.

USE AdventureWorks;
GO
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS 
(
    SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
    FROM HumanResources.Employee e
        INNER JOIN DirectReports d
        ON e.ManagerID = d.EmployeeID 
)
SELECT ManagerID, EmployeeID, EmployeeLevel 
FROM DirectReports 
WHERE EmployeeLevel <= 2 ;
GO

O exemplo a seguir é construído com base no exemplo C com a adição dos nomes do gerente e dos funcionários, e seus respectivos cargos. A hierarquia de gerentes e funcionários é evidenciada pelo recuo de cada nível.

USE AdventureWorks;
GO
WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)
AS (SELECT CONVERT(varchar(255), c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        1,
        CONVERT(varchar(255), c.FirstName + ' ' + c.LastName)
    FROM HumanResources.Employee AS e
    JOIN Person.Contact AS c ON e.ContactID = c.ContactID 
    WHERE e.ManagerID IS NULL
    UNION ALL
    SELECT CONVERT(varchar(255), REPLICATE ('| ' , EmployeeLevel) +
        c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        EmployeeLevel + 1,
        CONVERT (varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' + 
                 LastName)
    FROM HumanResources.Employee as e
    JOIN Person.Contact AS c ON e.ContactID = c.ContactID
    JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
    )
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM DirectReports 
ORDER BY Sort;
GO

MAXRECURSION pode ser usado para impedir que uma CTE recursiva mal formada entre em um loop infinito. O exemplo a seguir cria um loop infinito intencionalmente e usa a dica MAXRECURSION para limitar o número de níveis de recursão a dois.

USE AdventureWorks;
GO
--Creates an infinite loop
WITH cte (EmployeeID, ManagerID, Title) as
(
    SELECT EmployeeID, ManagerID, Title
    FROM HumanResources.Employee
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT cte.EmployeeID, cte.ManagerID, cte.Title
    FROM cte 
    JOIN  HumanResources.Employee AS e 
        ON cte.ManagerID = e.EmployeeID
)
--Uses MAXRECURSION to limit the recursive levels to 2
SELECT EmployeeID, ManagerID, Title
FROM cte
OPTION (MAXRECURSION 2);
GO

Depois que o erro de codificação for corrigido, MAXRECURSION não será mais necessário. O exemplo a seguir mostra o código corrigido.

USE AdventureWorks;
GO
WITH cte (EmployeeID, ManagerID, Title)
AS
(
    SELECT EmployeeID, ManagerID, Title
    FROM HumanResources.Employee
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT  e.EmployeeID, e.ManagerID, e.Title
    FROM HumanResources.Employee AS e
    JOIN cte ON e.ManagerID = cte.EmployeeID
)
SELECT EmployeeID, ManagerID, Title
FROM cte;
GO

O exemplo a seguir mostra a hierarquia de assemblies e componentes do produto necessários para montar a bicicleta para ProductAssemblyID = 800.

USE AdventureWorks;
GO
WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS
(
    SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty,
        b.EndDate, 0 AS ComponentLevel
    FROM Production.BillOfMaterials AS b
    WHERE b.ProductAssemblyID = 800
          AND b.EndDate IS NULL
    UNION ALL
    SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty,
        bom.EndDate, ComponentLevel + 1
    FROM Production.BillOfMaterials AS bom 
        INNER JOIN Parts AS p
        ON bom.ProductAssemblyID = p.ComponentID
        AND bom.EndDate IS NULL
)
SELECT AssemblyID, ComponentID, Name, PerAssemblyQty, EndDate,
        ComponentLevel 
FROM Parts AS p
    INNER JOIN Production.Product AS pr
    ON p.ComponentID = pr.ProductID
ORDER BY ComponentLevel, AssemblyID, ComponentID;
GO

O exemplo a seguir atualiza o valor de VacationHours em 25 por cento para todos os funcionários subordinados direta ou indiretamente ao ManagerID 12. A expressão de tabela comum retorna uma lista hierárquica de funcionários subordinados diretamente ao ManagerID 12 e funcionários subordinados a esses funcionários, e assim por diante. Somente as linhas retornadas pela expressão de tabela comum são modificadas.

USE AdventureWorks;
GO
WITH DirectReports(EmployeeID, NewVacationHours, EmployeeLevel)
AS
(SELECT e.EmployeeID, e.VacationHours, 1
  FROM HumanResources.Employee AS e
  WHERE e.ManagerID = 12
  UNION ALL
  SELECT e.EmployeeID, e.VacationHours, EmployeeLevel + 1
  FROM HumanResources.Employee as e
  JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
)
UPDATE HumanResources.Employee
SET VacationHours = VacationHours * 1.25
FROM HumanResources.Employee AS e
JOIN DirectReports AS d ON e.EmployeeID = d.EmployeeID;
GO

O exemplo a seguir usa vários membros de ancoragem e recursivos para retornar todos os ancestrais de uma pessoa específica. Uma tabela é criada e valores inseridos para estabelecer a genealogia familiar retornada pela CTE recursiva.

-- Genealogy table
IF OBJECT_ID('Person','U') IS NOT NULL DROP TABLE Person;
GO
CREATE TABLE Person(ID int, Name varchar(30), Mother int, Father int);
GO
INSERT Person VALUES(1, 'Sue', NULL, NULL);
INSERT Person VALUES(2, 'Ed', NULL, NULL);
INSERT Person VALUES(3, 'Emma', 1, 2);
INSERT Person VALUES(4, 'Jack', 1, 2);
INSERT Person VALUES(5, 'Jane', NULL, NULL);
INSERT Person VALUES(6, 'Bonnie', 5, 4);
INSERT Person VALUES(7, 'Bill', 5, 4);
GO
-- Create the recursive CTE to find all of Bonnie's ancestors.
WITH Generation (ID) AS
(
-- First anchor member returns Bonnie's mother.
    SELECT Mother 
    FROM Person
    WHERE Name = 'Bonnie'
UNION
-- Second anchor member returns Bonnie's father.
    SELECT Father 
    FROM Person
    WHERE Name = 'Bonnie'
UNION ALL
-- First recursive member returns male ancestors of the previous generation.
    SELECT Person.Father
    FROM Generation, Person
    WHERE Generation.ID=Person.ID
UNION ALL
-- Second recursive member returns female ancestors of the previous generation.
    SELECT Person.Mother
    FROM Generation, Person
    WHERE Generation.ID=Person.ID
)
SELECT Person.ID, Person.Name, Person.Mother, Person.Father
FROM Generation, Person
WHERE Generation.ID = Person.ID;
GO