Thiago Zavaschi R2 www.zavaschi.com

Entendendo as Common Table Expressions – CTE – Parte 1

Antes de mais nada devo dizer que não é um recurso novo, mas muitos desenvolvedores nunca ouviram falar sobre, ou se ouviram desistiram de tentar usar. Este recurso existe desde a versão 2005 do SQL Server e continua existindo nas versões posteriores (2008, 2008 R2 “Kilimanjaro”).

A uma primeira vista parece que as coisas “complicaram”, mas depois de escrever uma ou duas CTE’s é facilmente perceptível o quão fácil (e vantajoso) é trabalhar com as mesmas.

Antes de dar uma descrição formal de uma CTE, vou executar um dos exemplos mais simples que pode ser feito.

;WITH Simples(valor, nome)
AS
(
    SELECT 1, 'thiago'
)
SELECT * FROM Simples

E o resultado é:

valor       nome
----------- ------
1           thiago

(1 row(s) affected)

Ahmm, ok, eu poderia ter feito o mesmo usando simplesmente algo como:

SELECT 1, 'thiago' 

Mas a vantagem (a primeira das muitas) é que você pode referenciar várias vezes a cte em sua query. Mas antes de demonstrar isso, creioq eu cabe a necessidade de uma definição formal do que seja uma Common Table Expression. Definição segundo o Books Online do SQL Server 2008:

Uma CTE (Common Table Expression) pode ser encarada como um result set que tem seu escopo definido pela execução de um única cláusula SELECT, INSERT, UPDATE, DELETE ou CREATE VIEW. O result set da CTE existe apenas durante a execução da query.
Uma CTE tem duas grandes capacidades: pode se auto referenciar (CTE recursiva) e pode ser referenciada várias vezes na mesma query.

A sintaxe de uma CTE é demonstrada a seguir:

WITH expression_name [ ( column_name [,...n] ) ]
AS
( CTE_query_definition )

A lista de nomes de colunas é opcional somente se a query da CTE retornar colunas com nomes únicos. Contudo, é uma boa prática definir os nomes.

O comando para rodar a CTE seria:

SELECT <column_list>
FROM expression_name;

Um exemplo de uma CTE sendo referenciada várias vezes na mesma query:

;WITH VariasVezes(valor)
AS
(
    SELECT 1
    UNION
    SELECT 33
    UNION
    SELECT 8
)
SELECT
(SELECT MAX(valor) FROM VariasVezes) +
(SELECT SUM(valor) FROM VariasVezes) AS val

Perceba que no select que consome a CTE eu a referenciei (“VariasVezes”) duas vezes. Cuidado que nas funções agregadas o conteúdo da CTE sofre scan para cada função agregada, e, dependendo, uma abordagem diferente pode ser mais performática, mas ganha-se muito com a legibilidade da query. Observe os scans no plano de execução da CTE VariasVezes.

Para facilitar a análise, segue o plano de execução em modo texto (instrução SET SHOWPLAN_TEXT).

StmtText
----------------------------------------------------------------------------------------------------------
  |--Compute Scalar(DEFINE:([Expr1012]=[Expr1004]+[Expr1010]))
       |--Nested Loops(Inner Join)
            |--Stream Aggregate(DEFINE:([Expr1004]=MAX([Union1003])))
            |    |--Concatenation
            |        |--Constant Scan(VALUES:(((1))))
            |         |--Constant Scan(VALUES:(((33))))
            |         |--Constant Scan(VALUES:(((8))))
            |--Compute Scalar(DEFINE:([Expr1010]=CASE WHEN [Expr1019]=(0) THEN NULL ELSE [Expr1020] END))
                 |--Stream Aggregate(DEFINE:([Expr1019]=Count(*), [Expr1020]=SUM([Union1009])))
                      |--Concatenation
                          |--Constant Scan(VALUES:(((1))))
                           |--Constant Scan(VALUES:(((33))))
                           |--Constant Scan(VALUES:(((8))))

Essa característica já seria o suficiente para tornar o uso de CTEs muito interessante, mas na minha opinião a capacidade de auto-referenciar (queries recursivas) é que torna CTE um recurso tão poderoso.

Queries Recursivas com Common Table Expressions

Como já mencionei as CTEs podem se auto referenciar, isto é, dentro da própria definição da CTE.
Já ministrei diversos treinamentos em que CTE era um dos itens da ementa, e percebi que o melhor jeito de entender o funcionamento das mesmas é através de exemplos. Começarei com alguns simples e vou até alguns mais elaborados, com o passo a passo de cada execução para facilitar. :)

Vamos pensar um pouco, em que seria interessante a utilização de uma query recursiva? O exemplo clássico seria o cálculo de fatorial (faremos mais adiante), geração de números sequênciais, etc, etc.

Mas para usar uma CTE recursiva basta apenas referenciar ela mesma? Sim e não, na verdade não. É necessário a utilização do operador UNION ALL além da introdução de dois novos termos/conceitos: Membro Âncora e o Membro Recursivo. Segue a estruturação na forma de um exemplo que gera números sequênciais de 1 até 10:

;WITH NumerosSequenciais(num)
AS
(
     SELECT 1 AS num -- Este é o membro âncora, será o primeiro a ser executado. 
     UNION ALL -- Fará a ligação do membro âncora com o membro recursivo.
     SELECT num + 1 AS num FROM NumerosSequenciais
     WHERE num < 10 -- perceba que o WHERE é bastante importante para delimitar a parada da recursão.
)
SELECT * FROM NumerosSequenciais

Uma coisa comum, que vejo muitos se confundirem (o que é razoavél, uma vez que essa recursão é um pouco diferente das demais), é achar que o membro âncora será o critério de parada, e isso NÃO é verdade. Na realidade, ele é o primeiro a ser executado. O critério de parada é o membro recursivo parar de fornecer novas linhas de retorno para serem “processadas”. Veja a execução do passo a passo mais adiante.

Segue mais um exemplo, mas agora manipulando um elemento do tipo VARCHAR. Um ponto importante a se observar é que não funcionará sem a utilização da função CONVERT, e se deve ao fato de que os (os? veja adiante) membros âncoras e recursicos (plural novamente? veja adiante)  devem retornar o mesmo tipo de dado em cada coluna (no caso, VARCHAR(4000)).

;WITH Nome(nome)
AS
(
    SELECT nome = CONVERT(VARCHAR(4000),'Thiago')
    UNION ALL
    SELECT CONVERT(VARCHAR(4000),nome + 'Zavaschi') FROM Nome
    WHERE LEN(nome) < 30
)
SELECT nome FROM Nome

O resultado da query recursiva acima é:

Thiago
ThiagoZavaschi
ThiagoZavaschiZavaschi
ThiagoZavaschiZavaschiZavaschi

Queries recursivas com mais de um membro âncora/recursivo

Aqui é que as coisas podem parecer confusas (e até são de certo modo), tenho que confessar que nunca (que eu me lembre) precisei usar mais de um membro recursivo na mesma CTE, mas devemos conhecer todas as possibilidades!

Vamos voltar a query anterior que concatenava ‘Zavaschi’ ao nome ‘Thiago’ e adicionarei um novo âncora.

Mas e como fazer isso? Basta adicionar mais um SELECT que não faça referência à propria CTE (senão seria um membro recursivo, hehe). o resultado fica algo parecido com:

;WITH Nome(nome)
AS
(
    SELECT nome = CONVERT(VARCHAR(4000),'Thiago')
    UNION ALL
    SELECT nome = CONVERT(VARCHAR(4000),'José')
    UNION ALL
    SELECT CONVERT(VARCHAR(4000),nome + 'Zavaschi') FROM Nome
    WHERE LEN(nome) < 30
)
SELECT nome FROM Nome

Conseguem imaginar o que irá acontecer? Na verdade o que eu acho que um desenvolvedor tem que se preocupar é a ORDEM com que será retornado os registros (supondo que já haja um entendimento sobre CTEs).

O resultado desta query é:

nome
-------------------------------------
Thiago
José
JoséZavaschi
JoséZavaschiZavaschi
JoséZavaschiZavaschiZavaschi
JoséZavaschiZavaschiZavaschiZavaschi
ThiagoZavaschi
ThiagoZavaschiZavaschi
ThiagoZavaschiZavaschiZavaschi

(9 row(s) affected)

Por que isso ocorre? Ou melhor, “me explica o que ocorreu”. Vamos lá! Obs: vou ir negritando os registros e colocando a query que foi “executada” para facilitar no entendimento do output encontrado.

1) Como a “parte” que é executada primeiro é a da âncora, ou das âncoras, e por ordem no UNIOL ALL, temos o ‘Thiago’ e depois o ‘José’.

SELECT nome = CONVERT(VARCHAR(4000),'Thiago')
UNION ALL
SELECT nome = CONVERT(VARCHAR(4000),'José')

Thiago
José
JoséZavaschi
JoséZavaschiZavaschi
JoséZavaschiZavaschiZavaschi
JoséZavaschiZavaschiZavaschiZavaschi
ThiagoZavaschi
ThiagoZavaschiZavaschi
ThiagoZavaschiZavaschiZavaschi

2) Foram executados os âncoras agora é a vez da execução do membro recursivo.

SELECT CONVERT(VARCHAR(4000),nome + 'Zavaschi') FROM Nome
    WHERE LEN(nome) < 30

O conteúdo do “nome” atual é “José” (última âncora selecionada), perceba que no FROM temos referenciada a própria CTE!

A execução da query seria algo como (de maneira BEM simplificada):

SELECT ‘José’ + 'Zavaschi' WHERE LEN(‘José’ + 'Zavaschi') < 30

Esse registro retornou alguma linha? Sim. então continua processando esta query recursiva.

Thiago
José
JoséZavaschi
JoséZavaschiZavaschi
JoséZavaschiZavaschiZavaschi
JoséZavaschiZavaschiZavaschiZavaschi
ThiagoZavaschi
ThiagoZavaschiZavaschi
ThiagoZavaschiZavaschiZavaschi

3) Mas qual é o conteúdo do “nome” neste momento? “JoséZavaschi”.

A query recursiva retorna mais alguma linha (atentar para o WHERE)? Sim. entao retorna o valor presente em “nome” e continua.
O processo se mantém enquanto o conteúdo de “nome” atender à nossa restrição no WHERE (o WHERE é feito com o conteúdo de nome da iteracao anterior, ANTES, da concatenação).

Thiago
José
JoséZavaschi
JoséZavaschiZavaschi
JoséZavaschiZavaschiZavaschi
JoséZavaschiZavaschiZavaschiZavaschi
ThiagoZavaschi
ThiagoZavaschiZavaschi
ThiagoZavaschiZavaschiZavaschi

4) o Conteúdo de “nome” agora é:  JoséZavaschiZavaschiZavaschiZavaschi que possui mais de 30 caracteres, então a query recursiva não retorna mais registros e se encerra…ria, caso só tivéssemos uma única âncora. Mas o processo continua para a âncora anterior e assim por diante. E é por isto que temos a saída do Thiago, ThiagoZavaschi, e assim por diante.

Thiago
José
JoséZavaschi
JoséZavaschiZavaschi
JoséZavaschiZavaschiZavaschi
JoséZavaschiZavaschiZavaschiZavaschi
ThiagoZavaschi
ThiagoZavaschiZavaschi
ThiagoZavaschiZavaschiZavaschi

5) Quando a query recursiva não retornar mais nada, a CTE verifica se há alguma outra âncora “esperando”, se não há então já temos o result set que servirá de base para o SELECT, INSERT, DELETE, etc., que estiver após a CTE. Caso tenha uma outra âncora, esta será processada e assim por diante.

---

E para mais de um membro recursivo, como ficaria? Bem não vou fazer execução detalha, pois ficaria muito grande e seria muito repetitivo, mas vejam os seguintes scripts (e os testem).

;WITH Nome(nome)
AS
(
    SELECT nome = CONVERT(VARCHAR(4000),'Thiago')
    UNION ALL
    SELECT CONVERT(VARCHAR(4000),nome + 'Zavaschi') FROM Nome
    WHERE LEN(nome) < 30
    UNION ALL
    SELECT CONVERT(VARCHAR(4000),nome + 'Silva') FROM Nome
    WHERE LEN(nome) < 30
)
SELECT nome FROM Nome

Para esse teremos (omitindo o output):
(39 row(s) affected)

E se colocarmos mais uma âncora? Vai dobrar o número de resultados, certo? Errado. Esqueceu do WHERE LEN(nome) < 30?

;WITH Nome(nome)
AS
(
    SELECT nome = CONVERT(VARCHAR(4000),'Thiago')
    UNION ALL
    SELECT nome = CONVERT(VARCHAR(4000),'José')
    UNION ALL
    SELECT CONVERT(VARCHAR(4000),nome + 'Zavaschi') FROM Nome
    WHERE LEN(nome) < 30
    UNION ALL
    SELECT CONVERT(VARCHAR(4000),nome + 'Silva') FROM Nome
    WHERE LEN(nome) < 30
)
SELECT nome FROM Nome

Se utilizarmos os mesmos valores para âncoras e nas partes recursivas aí sim a adição de um âncora dobraria o total atual de registros retornados.

--

Limite de Recursividade

Para este caso utilizarei um dos exemplos já mostrados:

;WITH NumerosSequenciais(num)
AS
(
     SELECT 1 AS num -- Este é o membro âncora, será o primeiro a ser executado. 
     UNION ALL -- Fará a ligação do membro âncora com o membro recursivo.
     SELECT num + 1 AS num FROM NumerosSequenciais
     WHERE num < 10 -- perceba que o WHERE é bastante importante para delimitar a parada da recursão.
)
SELECT * FROM NumerosSequenciais

É conhecido que o resultado desta CTE será uma sequência de 1 até 10. A query a seguir retornaria o que?.

;WITH NumerosSequenciais(num)
AS
(
     SELECT 1 AS num -- Este é o membro âncora, será o primeiro a ser executado. 
     UNION ALL -- Fará a ligação do membro âncora com o membro recursivo.
     SELECT num + 1 AS num FROM NumerosSequenciais
     WHERE num < 1000 -- perceba que o WHERE é bastante importante para delimitar a parada da recursão.
)
SELECT * FROM NumerosSequenciais

O que mudou foi o nosso “critério de parada” permitindo listar até o número 1000, e é isso que vai acontecer, certo?

Errado. A execução será interrompida em um certo momento e teremos um output semelhante a:

1
2
3
…
98
99
100
101
Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

“Ahhhhm, não entendi.”

O SQL Server por padrão tem um mecanismo que serve para evitar que “descuidados” larguem queries recursivas infinitas, o que pode compromoter o desempenho do ambiente. É possível colocar um outro valor até o máximo de 32767. Basta adicionar ao final da query o hint: OPTION (maxrecursion <n>). No nosso exemplo anterior ficaria:

;WITH NumerosSequenciais(num)
AS
(
     SELECT 1 AS num -- Este é o membro âncora, será o primeiro a ser executado. 
     UNION ALL -- Fará a ligação do membro âncora com o membro recursivo.
     SELECT num + 1 AS num FROM NumerosSequenciais
     WHERE num < 1000 -- perceba que o WHERE é bastante importante para delimitar a parada da recursão.
)
SELECT * FROM NumerosSequenciais
OPTION (maxrecursion 3000)

Ainda assim a sua recursividade passa do limite de 32767? Então avalie:

É REALMENTE necessária uma recursão maior que isso?

Não há uma maneira alternativa (e menos custosa) de executar a mesma tarefa? Normalmente há.

Nunca precisei na prática, mas é possível colocar “ilimitadas” recursões: basta colocar o valor do “maxrecursion” para 0.
Mas lembro: eu, Thiago Zavaschi, não vejo isso como uma boa prática.

Continua no próximo post..