SQL Server – Como criar consultas recursivas com a CTE (Common Table Expressions)

Olá pessoal!
Como vocês estão ?

Nesse artigo eu gostaria de demonstrar a vocês Como criar consultas recursivas com a CTE (Common Table Expressions ou expressões de tabela comuns) no SQL Server. O recurso de CTE está disponível desde a versão 2005, e até hoje, muitas pessoas não conhecem esse recurso tão interessante e útil no dia a dia.

Introdução

Uma CTE tem o uso bem similar ao de uma subquery ou tabela derivada, com a vantagem do conjunto de dados poder ser utilizado mais de uma vez na consulta, ganhando performance (nessa situação) e também, melhorando a legibilidade do código. Por estes motivos, o uso da CTE tem sido bastante difundido como substituição à outras soluções citadas.

Exemplo de consulta com CTE:

Exemplo de consulta com Subquery:

Além disso, uma CTE fornece a significativa vantagem de ser capaz de se autorreferenciar, criando, portanto uma CTE recursiva, que é executada repetidamente para retornar subconjuntos de dados até que o resultado completo seja obtido.

A CTE recursiva é muito utilizada para retornar dados hierárquicos, como por exemplo, exibir funcionários em um organograma, no qual podem ter vários níveis hierárquicos e vou demonstrar aqui, um exemplo de como a CTE recursiva funciona.

Exemplos de CTE Recursiva

Exemplo de CTE recursiva utilizando uma sequência de números:

Resultado:

Exemplo de CTE recursiva utilizando hierarquia:

Criação da massa de testes – Funcionários da empresa CLR Corporation

Estrutura da empresa fictícia

Dada a estrutura acima, quero criar os níveis de hierarquia desses funcionários. Como faríamos isso sem usar o CTE ?

Exemplo com hierarquia manual

Uma forma muito comum de se criar uma hierarquia de dados no SQL Server para poucos níveis, é através de inserções manuais.

Embora seja bem simples (basicamente, Ctrl+C e Ctrl+V), quando precisamos automatizar esse trabalho ou temos muitos níveis na nossa hierarquia (podendo até ser variáveis), essa solução acaba não atendendo muito bem, nos levando a buscar uma solução mais robusta.

Exemplo de criação de hirarquia manual:

Resultado:

Exemplo com LOOP WHILE

Visando criar uma mais robusta que a anterior e que funcione com N níveis de hierarquia, podemos utilizar um LOOP WHILE para isso, que vai criar uma iteração em cada nível a partir da âncora (primeiro nível) até o insert não encontrar mais registros a serem inseridos.

Exemplo de inserção utilizando LOOP:

Resultado:

Como vocês puderam observar, utilizei um loop while para conseguir consultar a própria tabela e assim, encadear os dados e montar a hierarquia.

Mas e a CTE recursiva? Ela poderia retornar esse mesmo resultado com apenas 1 SELECT?
R: É claro! Utilizando esse recurso, podemos criar uma consulta que vai retornar o resultado esperado com apenas uma consulta.

Exemplo com CTE Recursivo

Resultado:

Controlando o grau de recursão com MAXRECURSION

Agora que você já viu como utilizar a CTE recursiva e como ela pode ser prática, chegou a hora de entender o risco de se construir uma CTE recursiva em “loop infinito”. Isso acontece quando você não programa bem a recursão da sua query e ela pode acabar atingindo N níveis e até travar a sua instância e tanto consumir recursos tentando resolver a consulta.

Para evitar esse tipo de situação, existe a query hint MAXRECURSION, que permite especificar o nível máximo de recursão. Ao atingir esse nível, o SQL irá abortar o restante da execução e mostrar uma mensagem de erro com severidade 16, conforme demonstrado abaixo:

Mensagem de erro:

Resultado:

Vejam que utilizando o hint OPTION(MAXRECURSION 1), apenas o nível primário e mais 1 nível de hierarquia foram retornados (níveis 1 e 2). Caso utilizássemos o valor MAXRECURSION 2, o nível primário e mais 2 níveis de hierarquia seriam retornados (nível 1 ao nível 3).

Como a exceção gerada pelo hint MAXRECURSION é de severidade 16, ela causa uma interrupção no restante da rotina e caso seja um job, esse job irá retornar uma falha. Caso você não queira esse comportamento e deseja apenas limitar o nível de recursão, mas sem provocar erro na rotina, basta encapsular a sua CTE em um bloco de TRY..CATCH:

Observação: O parâmetro do hint MAXRECURSION suporta valores de 1 a 32.767. Caso você queira utilizar um nível de recursão acima desse valor, basta não utilizar esse hint ou especificar o valor 0, que significa sem limite de recursividade.

Uma outra forma de demonstrar um uso de CTE recursiva bem interessante é para gerar sequências de números:

Exemplo de CTE recursiva para gerar uma sequência de 200 números:

Resultado:

Utilizando esse código acima, foi possível limitar os 200 registros utilizando o hint MAXRECURSION(199), limitando a 199 níveis de recursividade. Entretanto, é possível garantir essa limitação de uma outra forma (e sem gerar uma exceção):

Resultado:

Então reparem que, mesmo especificando que o nível de recursão é 249, ou seja, o nível máximo de recursão são 250 registros (e de fato, deveriam ter sido gerados 250 registros), como eu limitei a quantidade em 200 registros dentro da query da CTE, foi possível retornar apenas os 200 registros, fazendo com que o hint MAXRECURSION seja dispensável neste caso.

Com isso, foi possível demonstrar que é possível limitar os registros de um CTE recursivo (em determinadas situações) mesmo sem utilizar o hint MAXRECURSION.

Conclusão

Nesse artigo, pude demonstrar o que é uma CTE, em que situações podemos utilizá-la, como ela é construída e algumas das vantagens que ela possui sobre outras soluções, como tabelas derivadas e subquery.

Além disso, foi possível explicar como uma CTE recursiva funciona, como ela pode ser montada e alguns exemplos onde ela pode ser especialmente útil.

Espero que tenham gostado desse artigo e que ele tenha sido útil para vocês!
Um abraço e até a próxima!