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 CTE sã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 Ciclos da Adventure Works.

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

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.

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

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.

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.

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.

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

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.

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.