Usando expressões de tabela comuns
Podemos pensar em uma expressão de tabela comum (CTE) como sendo um conjunto de resultados temporário definido no escopo de execução de uma única instrução SELECT, INSERT, UPDATE, DELETE ou CREATE VIEW. Uma CTE é semelhante a uma tabela derivada que não é armazenada como um objeto e permanece apenas durante a duração de uma consulta. Ao contrário de uma tabela derivada, uma CTE pode ser autorreferenciada e pode ser referenciada várias vezes na mesma consulta.
Uma CTE pode ser usada para:
Criar uma consulta recursiva. Para obter mais informações, consulte Consultas recursivas que usam expressões de tabelas comuns.
Substituir uma exibição quando o uso geral da exibição não for necessário, ou seja, você não precisa armazenar a definição nos metadados.
Habilitar o agrupamento por uma coluna que seja derivada de uma subseleção escalar ou uma função que não seja determinística ou tenha acesso externo.
Referenciar a tabela resultante várias vezes na mesma instrução.
Usar uma CTE oferece as vantagens de legibilidade aprimorada e facilidade de manutenção de consultas complexas. A consulta pode ser dividida em blocos de construção lógicos simples e separados. Esses blocos simples podem ser usados para criar CTEs interinas mais complexas, até que o resultado final seja gerado.
As CTEs podem ser definidas em rotinas definidas pelo usuário, como funções, procedimentos armazenados, gatilhos ou exibições.
Estrutura de uma CTE
Uma CTE é constituída de um nome de expressão representando a CTE, uma lista de colunas opcional e uma consulta que define a CTE. Após a definição da CTE, ela poderá ser referenciada como uma tabela ou exibição em uma instrução SELECT, INSERT, UPDATE ou DELETE. Uma CTE também pode ser usada em uma instrução CREATE VIEW como parte da instrução SELECT que a define.
A estrutura de sintaxe básica de uma CTE é:
WITH expression_name [ ( column_name [,...n] ) ]
AS
( CTE_query_definition )
A lista de nomes de colunas será opcional somente se forem fornecidos nomes distintos para todas as colunas resultantes na definição da consulta.
A instrução para executar a CTE é:
SELECT <column_list>
FROM expression_name;
Exemplo
O exemplo a seguir mostra os componentes da estrutura da CTE: nome da expressão, lista de colunas e consulta. A expressão Sales_CTE tem três colunas (SalesPersonID, SalesOrderID, and OrderDate) e está definida como o número total de ordens de venda e data da ordem de venda mais recente na tabela.
USE AdventureWorks2008R2;
GO
-- Define the CTE expression name and column list.
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
-- Define the CTE query.
(
SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
)
-- Define the outer query referencing the CTE name.
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;
GO
Aqui está um conjunto de resultados parcial:
SalesPersonID TotalSales SalesYear
------------- ----------- -----------
274 4 2001
274 20 2002
274 14 2003
274 10 2004
275 56 2001
275 139 2002
275 169 2003