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

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

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:

Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 1 has been exhausted before statement completion.

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!

SQL, sql server, sql server 2008, sql server 2008 R2, Oracle, Oracle Database, Oracle 11g, Oracle 10g, Oracle 12c, MySQL, Firebird, Consultoria, Consultor, Programador, Programação. Desenvolvedor, Analista de Sistemas, DBA, Criação de website, Criação de Sistema Web, Vitória, Vila Velha, Guarapari, Espírito Santo, ES, Consultoria SQL em VItória, Treinamento, Curso, Prestação de serviço, prestar serviço, freelancer, freela, banco de dados, consultoria em banco de dados, consultor de banco de dados

2 Comments

Deixe uma resposta