Clique no banner para conhecer e adquirir o meu treinamento de Bancos de Dados no Azure

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

Visualizações: 25.581 views
Tempo de Leitura: 7 minutos

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!