CTE - Comom Table Expression
AS CTE – ou Comon Table Expression – são um recurso muito útil e pouco apreciado do SQL Server, introduzido na versão 2005. Sim! Na 2005! Por isso eu disse pouco apreciado. Quem olha para uma query escrita com CTE pela primeira vez, pode achar o código complexo e difícil de desenvolver. Mas, quando você pega o jeito, fica fácil perceber como é simples, fácil e útil trabalhar com elas.
Uma CTE é um conjunto de resultados utilizados na execução dos comandos SELECT, INSERT, UPDATE, DELETE ou CREATE VIEW. Esse resultado só existe durante a execução da query. Uma das maiores qualidades da CTE é a habilidade de se auto referenciar (CTE recursiva) ou ser referenciada várias vezes na mesma query.
Esta é a sintaxe da CTE:
;WITH expressao_cte ([coluna1, coluna2, coluna4...])
AS
(SELECT ...),
outra_expressao_cte ([coluna1, coluna3, ...])
AS
(SELECT ...)
Comando...
Detalhando a sintaxe:
Toda CTE começa com WITH. Eu acrescentei um “;” no início, porque é necessário que o comando anterior à CTE tenha terminado adequadamente. Então, para facilitar, eu sempre acrescento o “;”. Esta regra é muito útil quando a CTE é desenvolvida na aplicação.
A lista de colunas é opcional, desde que o nome das colunas do (SELECT...) sejam únicos. Pode ser uma boa prática defini-los.
Uma CTE deve ter pelo menos uma expressão, e pode ter quantas expressões forem necessárias para chegar ao resultado desejado. Separamos cada expressão com vírgula. A última expressão, antes do comando final, não tem vírgula.
O nome da expressão segue as mesmas regras da criação de tabelas, views, etc. Eu, particularmente, gosto de usar o prefixo CTE_, que facilita identificar o que é CTE e o que é tabela ou view nos comandos.
O comando final de uma CTE será SELECT, INSERT, UPDATE ou DELETE.
Esta é a sintaxe mais simples que podemos ter numa CTE:
É óbvio que executar apenas a expressão SELECT 'Olá mundo', GETDATE() traria o mesmo resultado. Mas este é um bom ponto de partida.
Vamos utilizar algumas tabelas do BD AdventureWorks2014 para exemplificar as opções de sintaxe da CTE.
Como a CTE funciona?
O SQL Server executa uma CTE sequencialmente, de cima para baixo. Neste exemplo, o comando SELECT da tabela Product foi executado, seu resultado armazenado com o nome CTE_Product e finalmente exibido para o usuário no SELECT final.
E neste caso?
O SQL Server executou a primeira expressão, e armazenou o resultado sob o nome CTE_Product. Na segunda expressão, o SQL leu o conteúdo de CTE_Product, contou os registros e guardou o resultado em CTE_Counting. Finamente, o resultado de CTE_Counting foi exibido para o usuário.
Nesta query, nós referenciamos a CTE_Product dentro da CTE_Counting. Essa possibilidade permite quebrarmos queries complexas em códigos menores e mais simples de trabalhar.
Podemos referenciar a primeira CTE no código da segunda, da terceira, da quarta até a enésima CTE do comando. Porém, não podemos referenciar a segunda CTE no código da primeira. Ou seja, a CTE só pode ser referenciada após ser executada.
Vamos analisar esta query:
A primeira CTE carrega o código do vendedor e o total de vendas por ano.
A segunda CTE carrega o código do vendedor e o valor total de quotas por ano.
O SELECT final une o resultado das duas CTEs.
Neste script, acrescentamos a clásula INTO #FinalQuery, utilizando o SELECT final para criar uma tabela temporária.
Como mencionado anteriormente, o comando final do CTE pode ser um SELECT, INSERT, UPDATE ou DELETE. Neste exemplo, utilizamos o conteúdo da CTE, combinado com o conteúdo de uma tabela, para executar um update:
Queries recursivas utilizando CTEs
Além de facilitar o desenvolvimento de queries complexas, quebrando-as em queries menores e mais simples, as CTEs são muito utilizadas quando precisamos de resultados recursivos. Resultados recursivos podem ser utilizados para gerarmos sequências, valores fatoriais, hierarquias, etc.
Para começar, este é um exemplo clássico de recursividade da CTE:
Simples? Com certeza. Vamos analisar passo a passo:
O uso do operador UNION ALL é necessário, pois o primeiro SELECT do UNION será o resultado do CTE, e o segundo SELECT poderá referenciar o CTE.
Temos dois novos termos:
Membro âncora: ponto de partida da contagem da CTE.
Membro recursivo: ponto de conclusão da contagem do CTE.
O exemplo acima foi delimitado a 7 para gerar uma imagem bonita. Só que dificilmente usaríamos um sequencial tão pequeno. Neste caso, basta aumentar o número recursivo. Quem sabe, para 1000?
Ops! O que aconteceu?
Nada demais, a query apenas extrapolou o limite padrão de recursividade de uma CTE. Este é um mecanismo de proteção do SQL Server, para evitar a execução de queries recursivas infinitas. Para executar uma query com recursividade superior a 100 – o limite padrão – utilizamos o hint OPTION (maxrecursion <N>). Assim:
O hint OPTION (maxrecursion <N>) pode receber qualquer valor até 32.767. E se colocar 0 (zero)? Bom, aí a CTE continuará executando até chegar ao valor definido no WHERE. Se não definir um valor, então ficará rodando eternamente, consumindo processamento do servidor. Isso não é uma boa idéia...
Então a recursividade só vale para números? Não!
Neste exemplo, utilizamos um tipo VARCHAR. Outro pré-requisito é o uso da função CONVERT, que explicita para o SQL Server qual o tipo de datatype usado na recursividade.
Neste exemplo, vamos utilizar a CTE para realizar o Split de um texto. Eu recebi este exemplo de outro DBA, e com algumas adaptações, pode ser muito útil para trabalhar com arquivos de texto com delimitadores.
