SQL CHIT CHAT … Blog sobre Sql Server

Query optimiser irá descartar o TOP/ORDER BY já que o resultado de uma expressão de tabela é sempre uma tabela – selecionar TOP(100 PERCENT) não faz nenhum sentido de qualquer maneira. A idéia por trás das estruturas de tabelas é derivada de um conceito na Teoria Relacional da Base de Dados conhecido como Relação.

ITVF (Inline Table Valued Functions)

ITVFs são expressões de tabelas reutilizáveis que suportam parâmetros de entrada. As funções podem ser tratadas como vistas parametrizadas.

CTE (Common Table Expressions)

Expressões comuns de tabelas são similares a tabelas derivadas mas com várias vantagens importantes;

A CTE é definida usando uma instrução WITH, seguida por uma definição de expressão de tabela. Para evitar a ambiguidade (TSQL usa a palavra-chave WITH para outros propósitos, ou seja, WITH ENCRYPTION etc.) a expressão que precede a cláusula CTE’s WITH DEVE ser terminada com uma semicoluna. Isto não é necessário se a cláusula WITH for a primeira declaração em um lote, ou seja, em uma definição VIEW/ITVF)

NOTE: Semi-coluna, o terminador da declaração é suportado pelo padrão ANSI e é altamente recomendado para ser usado como parte da prática de programação TSQL.

CTE recursiva

SQL Server suporta capacidades de consulta recursiva implementadas através de CTEs recursivas desde a versão 2005(Yukon).

Elementos de um CTE recursivo

1. Membro(s) da âncora – Definições da consulta que;
   1. devolve uma tabela de resultados relacionais válida
   2. é executado APENAS UMA VEZ no início da execução da consulta
   3. é posicionado sempre antes da primeira definição recursiva de membro
   4. o último membro da âncora deve ser seguido pelo operador UNION ALL. O operador combina o último membro de âncora com o primeiro membro recursivo
2. operador UNION ALL multi-set. O operador opera em
3. Membro(s) recursivo(s) – Consultar definições que;
   1. retorna uma tabela de resultados relacional válida
   2. tem referência ao nome CTE. A referência ao nome CTE representa logicamente o resultado anterior definido em uma seqüência de execuções. i.e. O primeiro resultado “anterior” definido em uma seqüência é o resultado que o membro âncora retornou.
4. Invocação CTE – Declaração final que invoca a recorrência
5. Mecanismo de segurança – A opção MAXRECURSION previne o sistema de banco de dados dos loops infinitos. Este é um elemento opcional.

Verificação de terminação

O membro recursivo do CTE não tem verificação explícita de terminação de recursividade.
Em muitas linguagens de programação, nós podemos projetar um método que se chama a si mesmo – um método recursivo. Todo método recursivo precisa ser terminado quando uma determinada condição é satisfeita. Isto é terminação de recursividade explícita. Após este ponto, o método começa a retornar valores. Sem o ponto de terminação a recursividade pode acabar se chamando “endlessly”.
A verificação de terminação recursiva de membros do CTE está implícita , o que significa que a recursividade pára quando nenhuma linha é retornada da execução anterior do CTE.

Aqui está um exemplo clássico de uma recursividade em programação imperativa. O código abaixo calcula o fatorial de um inteiro usando uma chamada recursiva de função(método).

Código completo do programa do console pode ser encontrado aqui.

MAXRECURSION

Como mencionado acima, CTEs recursivas assim como qualquer operação recursiva pode causar loops infinitos se não for projetada corretamente. Esta situação pode ter um impacto negativo no desempenho da base de dados. O motor do Sql Server tem um mecanismo à prova de falhas que não permite execuções infinitas.

Por defeito, o número de vezes que um membro recursivo pode ser invocado é limitado a 100 (isto não conta a execução da âncora uma vez fora). O código falhará na 101ª execução do membro recursivo.

Msg 530, Nível 16, Estado 1, Linha xxx
A declaração terminou. A recursividade máxima 100 foi esgotada antes da conclusão do comando.

O número de recursões é gerenciado pela opção de consulta MAXRECURSION n. A opção pode substituir o número padrão de recursões máximas permitidas. O parâmetro (n) representa o nível de recursividade. 0<==n <=32767

Importante nota:: MAXRECURSION 0 – desactiva o limite de recursividade!

Figure 1 mostra um exemplo de um CTE recursivo com os seus elementos

Figure 1, elementos CTE recursivos

A recursividade recursiva é bastante diferente da recursividade tradicional e imperativa. Além das diferentes estruturas de código, podemos observar a diferença entre a verificação de terminação explícita e implícita. No exemplo CalculateFactorial, o ponto de terminação explícita é claramente definido pela condição: se (número == 0) então retornar 1.
No caso de CTE recursiva acima, o ponto de terminação é implicitamente definido pela operação INNER JOIN, mais especificamente pelo resultado da expressão lógica na sua cláusula ON: ON e.MgrId = c.EmpId. O resultado da operação de tabela aciona o número de recidivas. Isto ficará mais claro nas seções seguintes.

Utilizar CTE recursivo para resolver a hierarquia de empregados

Existem muitos cenários em que podemos usar CTEs recursivos, ou seja, para separar elementos, etc. O cenário mais comum que encontrei durante muitos anos de sequenciamento foi a utilização de CTE recursivos para resolver vários problemas hierárquicos.

A árvore hierárquica de Empregados é um exemplo clássico de um problema hierárquico que pode ser resolvido utilizando CTEs recursivos.

Exemplo

Vamos dizer que temos uma organização com 12 empregados. Aplicam-se as seguintes regras de negócio;

• Um empregado deve ter uma identificação única, EmpId
  • enforçado por: Limitação chave primária na coluna EmpId
• Um empregado pode ser gerido por 0 ou 1 gerente.
  • enforcedor por: PK no EmpId, FK no MgrId e coluna NULLable MgrId

>

• Um gerente pode gerenciar um ou mais funcionários.
  • enforçado por:: Foreign Key constraint(self referenced) on MgrId column
• Um gerente não pode gerenciar a si mesmo.
  • enforced by: Restrição CHECK na coluna MgrId

A hierarquia de árvore é implementada em uma tabela chamada dbo.Employees. Os scripts podem ser encontrados aqui.

Figure 2, tabela Empregados

Vamos apresentar o modo como os CTE recursivos operam respondendo à pergunta: Quem são os subordinados diretos e indiretos do gerente com EmpId = 3?

Da árvore hierárquica da Figura 2 podemos ver claramente que o gerente (EmpId = 3) gerencia diretamente os funcionários; EmpId=7, EmpId=8 e EmpId=9 e gerencia indiretamente; EmpId=10, EmpId=11 e EmpId=12.

Figure 3 mostra a hierarquia EmpId=3 e o resultado esperado. O código pode ser encontrado aqui.

Figure 3, EmpId=3 subordinados diretos e indiretos

Então, como obtivemos o resultado final.

A parte recursiva na iteração atual sempre faz referência ao seu resultado anterior a partir da iteração anterior. O resultado é uma expressão de tabela(ou tabela virtual) chamada cte1(a tabela do lado direito do INNER JOIN). Como podemos ver, cte1 também contém a parte de âncora. Logo na primeira execução (a primeira iteração), a parte recursiva não pode fazer referência ao seu resultado anterior porque não houve iteração anterior. É por isso que na primeira iteração apenas a parte âncora executa e apenas uma vez durante todo o processo. O conjunto de resultados da consulta âncora dá à parte recursiva seu resultado anterior na segunda iteração. A âncora actua como um volante se você 🙂

O resultado final é construído através de iterações i.e Anchor resultado + resultado da iteração 1 + resultado da iteração 2 …

A sequência lógica de execução

A consulta de teste é executada seguindo a sequência lógica abaixo:

1. A instrução SELECT fora da expressão cte1 invoca a recursividade. A consulta âncora executa e retorna uma tabela virtual chamada cte1. A parte recursiva retorna uma tabela vazia uma vez que não tem seu resultado anterior. Lembre-se, as expressões na abordagem baseada em set são avaliadas todas de uma vez.
   Figure 4, valor cte1 após a 1ª iteração
2. A segunda iteração começa.Esta é a primeira recursividade. O papel da âncora desempenhou o seu papel na primeira iteração e a partir de agora retorna apenas conjuntos vazios. Entretanto, a parte recursiva pode agora referenciar seu resultado anterior(valor cte1 após a primeira iteração) no operador INNER JOIN. A operação de tabela produz o resultado da segunda iteração como mostrado na figura abaixo.
   FIgure 5, valor cte1 após a segunda iteração
3. Segunda iteração produz um conjunto não vazio, então o processo continua com a terceira iteração – a segunda recursividade. Elemento recursivo agora referencia o resultado cte1 da segunda iteração.
   
   FIgure 6, valor cte1 após a terceira iteração
4. Acontece uma coisa interessante na quarta iteração – a terceira tentativa de recursividade. Seguindo o padrão anterior, o elemento recursivo usa o resultado cte1 da iteração anterior. Entretanto, desta vez não há linhas retornadas como resultado da operação INNER JOIN, e o elemento recursivo retorna um conjunto vazio. Este é o ponto de encerramento implícito mencionado anteriormente. Neste caso, a avaliação da expressão lógica de INNER JOIN dita o número de recursões.
   Porque o último resultado cte1 é um conjunto de resultados vazio, a 4ª iteração(ou 3ª recursividade) é “cancelada” e o processo é finalizado com sucesso.
   
   Figure 7, A iteração final
   O cancelamento lógico da 3ª recursão (a última recursão que produziu um conjunto de resultados vazio não conta) ficará mais claro na seção seguinte, recursiva de análise do plano de execução CTE.Podemos adicionar OPTION(MAXRECURSION 2) opção de consulta ao final da consulta que limitará o número de recursões permitidas a 2. A consulta produzirá o resultado correto provando que somente duas recursões são necessárias para esta tarefa.Nota: Do ponto de vista da execução física, o conjunto de resultados é progressivamente(como bolhas de linhas para cima) enviado para os buffers de rede e de volta para a aplicação cliente.

Finalmente, a resposta à pergunta acima é :
Existem seis funcionários que direta ou indiretamente se reportam ao Emp = 3. Três funcionários, EmpId=7, EmpId=8 e EmpId=9 são subordinados diretos e EmpId=10, EmpId=11 e EmpId=12 são subordinados indiretos.

Sabendo a mecânica dos CTE recursivos, podemos resolver facilmente os seguintes problemas.

>

• find todos os empregados que estão hierarquicamente acima do EmpId = 10 (código aqui)
• find EmpId=8 ‘s directos e os subordinados de segundo nível(código aqui)

No segundo exemplo controlamos a profundidade da hierarquia restringindo o número de recursões.
Elemento de ancoragem dá-nos o primeiro nível de hierarquia, neste caso, os subordinados directos. Cada recursividade move então um nível hierárquico para baixo a partir do primeiro nível. No exemplo, o ponto de partida é EmpId=8 e os seus subordinados diretos. A primeira recursividade move mais um nível abaixo da hierarquia onde EmpId=8 ‘s subordinados do segundo nível “vivos”.

Problema de referência circular

Uma das coisas interessantes com hierarquias é que os membros de uma hierarquia podem formar um loop fechado onde o último elemento da hierarquia faz referência ao primeiro elemento. O loop fechado também é conhecido como referência circular.
Em casos como este, o ponto de terminação implícito, como a operação INNER JOIN explicada anteriormente, simplesmente não funcionará porque sempre retornará um conjunto de resultados não vazios para que a próxima recursividade continue. A parte da recursividade continuará rolando até atingir o fail-safe do Sql Server, a opção de consulta MAXRECURSION.

Para demonstrar situação de referência circular usando ambiente de teste previamente configurado, precisaremos

• Remover restrições de chave primária e estrangeira da tabela dbo.Employees para permitir os cenários de loops fechados.
• Criar uma referência circular (EmpId=10 irá gerenciar seu gerenciador indireto , EmpId = 3)
• Extender a consulta de teste usada nos exemplos anteriores, para poder analisar a hierarquia dos elementos no loop fechado.

A consulta de teste estendida pode ser encontrada aqui.

Antes de continuar com o exemplo de referência circular, vamos ver como funciona a consulta de teste estendida. Comente a cláusula WHERE predicates(as duas últimas linhas) e execute a consulta contra a tabela dbo.Employee original

Figure 8, Detecting existence of circular loops in hierarchies

O resultado da consulta estendida é exatamente o mesmo que o resultado apresentado no experimento anterior na Figura 3. A saída é estendida para incluir as seguintes colunas

• pth – Representa graficamente a hierarquia atual. Inicialmente, dentro da parte da âncora, simplesmente adiciona o primeiro subordinado a MgrId=3, o gerenciador do qual estamos começando. Agora, cada elemento recursivo toma o valor pth anterior e adiciona o próximo subordinado a ele.
• recLvl – representa o nível atual de recursividade. A execução da âncora é contada como recLvl=0
• isCircRef – detecta a existência de uma referência circular na hierarquia atual(linha). Como parte de um elemento recursivo, ele procura pela existência de um EmpId que foi previamente incluído na cadeia pth.
  i.e se a pth anterior se parece com 3->8->10 e a recursividade atual adiciona ” ->3 “, (3->8 >10 ->3) significando que EmpId=3 não só é um superior indireto ao EmpId=10, mas também é subordinado do EmpId=10 – Eu sou o chefe ou seu chefe, e você é meu chefe tipo de situação 😐

Vamos agora fazer as mudanças necessárias na dbo.Funcionários para ver a consulta de teste estendida em action.

Remover restrições PK e FK para permitir referências circulares e adicionar um “bad boy circular ref” à tabela.

Executar a consulta de teste estendida, e analisar os resultados (não se esqueça de descomentar a cláusula WHERE previamente comentada no final do script)
O script executará 100 recursões antes de ser interrompido pela MAXRECURSION padrão. O resultado final será restrito a duas recursões … E cte1.recLvl <= 2; que é necessário para resolver a hierarquia do EmpId=3.

Figure 9 mostra uma hierarquia de loop fechado, o número máximo permitido de recursões esgotadas e o output que mostra o loop fechado.
Figure 10, Circular reference detected

A few notes about the circular reference script.
O script é apenas uma ideia de como encontrar loops fechados em hierarquias. Ele relata apenas a primeira ocorrência de uma referência circular – tente remover a cláusula WHERE e observar o resultado.
Na minha opinião, o script (ou uma versão similar do script) pode ser usado em ambiente de produção para fins de resolução de problemas ou como prevenção de criar referências circulares em uma hierarquia existente. No entanto, ele precisa ser assegurado por MAXRECURSION n apropriada, onde n é a profundidade esperada da hierarquia.

Este script não é relacional e se baseia em uma técnica de travessia. É sempre a melhor abordagem para usar restrições declarativas (PK, FK, CHECK…) para prevenir qualquer loop fechado nos dados.

Análise do plano de execução

Este segmento explica como o Sql Server’s query optimiser(QO) implementa um CTE recursivo. Há um padrão comum que o QO usa ao construir o plano de execução. Execute a consulta de teste original e inclua o plano de execução real

Como a consulta de teste, o plano de execução tem dois ramos: o ramo âncora e o ramo recursivo. O operador de concatenação, que implementa o operador UNION ALL, conecta os resultados das duas partes que formam o resultado da consulta.

Tentemos conciliar a seqüência lógica de execução mencionada anteriormente e a implementação real do processo.

Figure 11, Recursive CTE execution plan

Seguir o fluxo de dados (da direita para a esquerda) o processo parece:

Elemento de ancoragem (executado apenas uma vez)

1. Operador Clustered Index Scan – o sistema executa o scan de índice. Neste exemplo, ele aplica a expressão MgrId = @EmpId como um predicado residual. Linhas selecionadas (colunas EmpId e MgrId) são passadas (linha por linha) de volta para o operador anterior.
2. Compute Scalar O operador adiciona uma coluna à saída. Neste exemplo, o nome da coluna adicionada é . Isto representa o Número de Recursões. A coluna tem valor inicial de 0; =0
3. Concatenação – combina as entradas dos dois ramos. Na primeira iteração, o operador recebe as linhas apenas do ramo de âncora. Ele também muda os nomes das colunas de saída. Neste exemplo, os nomes das novas colunas são:
   1. = ou * * contém o número de recursões atribuídas no ramo recursivo. Ele não tem valor na primeira iteração.
   2. = EmpId(da parte da âncora) ou EmpId(da parte recursiva)
   3. = MgrId(da parte da âncora) ou MgrId (da parte recursiva)
4. Index Spool (Lazy Spool) Este operador armazena o resultado recebido do operador Concatenação em uma mesa de trabalho. Ele tem a propriedade “Logical Operation” definida como “Lazy Spool”. Isto significa que o operador retorna suas linhas de entrada imediatamente e não acumula todas as linhas até obter o resultado final definido (Eager Spool) . A mesa de trabalho é estruturada como um índice agrupado com a coluna chave – o número de recursividade. Como a chave de índice não é única, o sistema adiciona um unívoco interno de 4 bytes à chave de índice para garantir que todas as linhas no índice sejam, do ponto de vista da implementação física, identificáveis de forma única. O operador também tem a propriedade “With Stack” definida como “True”, o que torna esta versão do operador de spool um Spool de Pilha Um operador de Spool de Pilha tem sempre dois componentes – um Spool de índice que constrói a estrutura de índice e um Spool de tabela que atua como consumidor das linhas armazenadas na mesa de trabalho que foi construída pelo Spool de índice.
   Nesta etapa, o operador do Spool de índice retorna as linhas ao operador SELECT e armazena as mesmas linhas na mesa de trabalho.
5. operador SELECT retorna EmpId e MgrId ( , ). Exclui do resultado. As linhas são enviadas para o buffer de rede conforme chegam dos operadores a jusante

Após esgotar todas as linhas do operador Index Scan, o operador Concatenação muda de contexto para o ramo recursivo. O ramo de âncora não será executado novamente durante o processo.

Elemento recursivo

1. Spool de tabela (Lazy Spool). O operador não tem entradas e, como mencionado em (4) atua como um consumidor das linhas produzidas pelo Spool Índice e armazenado em uma mesa de trabalho agrupada. Possui a propriedade “Nó Primário” definida como 0 que aponta para o Id do Nó de Spool de Índice. Ele destaca a dependência dos dois operadores. O operador
   1. remove as linhas que leu na recursividade anterior. Esta é a primeira recursividade e não há nenhuma linha lida anteriormente para ser apagada. A mesa de trabalho contém três linhas (Figura 4).
   2. Ler as linhas ordenadas pela chave de índice + unívoco em ordem decrescente. Neste exemplo, a primeira linha lida é EmpId=9, MgrId=3.

   Finalmente, o operador renomeia os nomes das colunas de saída. =, = e torna-se .
   NOTE: O operador de spool de tabela pode ser observado como a expressão cte1 no lado direito do INNER JOIN (figura 4)

2. Compute Scalar O operador adiciona 1 ao número atual de recursões anteriormente armazenado na coluna .O resultado é armazenado em uma nova coluna, . = + 1 = 0 + 1 = 1. O operador sai três colunas, as duas do spool da tabela ( e ) e o operador
3. Nested Loop(I) recebe linhas de sua entrada externa, que é o Compute Scalar da etapa anterior, e então usa – representa EmpId do operador Spool da tabela, como um predicado residual no operador Index Scan posicionado na entrada interna do Loop. A entrada interna executa uma vez para cada linha da entrada externa.
4. Operador de varredura de índice retorna todas as linhas qualificadas da tabela dbo.Employees (duas colunas; EmpId e MgrId) para o operador de loop aninhado.
5. Loop aninhado(II): O operador combina da entrada externa e EmpId e MgrId da entrada interna e passa as três linhas de colunas para o próximo operador.
6. Assert operator é usado para verificar as condições que requerem que a consulta seja abortada com uma mensagem de erro. No caso de CTEs recursivos , o operador assert implementa a opção de consulta “MAXRECURSION n”. Ele verifica se a parte recursiva atingiu ou não o número permitido (n) de recursões. Se o número atual de recursões, (ver passo 7) for maior que (n), o operador retorna 0 causando um erro de tempo de execução. Neste exemplo, o Sql Server usa seu valor padrão de MAXRECURSION 100. A expressão é parecida: CASO QUANDO > 100 ENTÃO 0 ELSE NULL Se decidirmos excluir o failsafe adicionando MAXRECURSION 0, o operador afirmado não será incluído no plano.
7. Concatenação combina entradas dos dois ramos. Desta vez ele recebe apenas entradas da parte recursiva e saídas das colunas/linhas como mostrado no passo 3.
8. Index Spool (Lazy Spool) adiciona a saída do operador de concatenação à mesa de trabalho e depois a passa para o operador SELECT. Neste ponto, a mesa de trabalho contém o total de 4 linhas: três linhas da execução da âncora e uma da primeira recursividade. Seguindo a estrutura de índice agrupado da mesa de trabalho, a nova linha é armazenada no final da mesa de trabalho

9. O processo agora é retomado a partir do passo 6. O operador do spool da tabela remove as linhas lidas anteriormente (as três primeiras linhas) da mesa de trabalho e lê a última linha inserida, a quarta linha.

Conclusion

CTE(Common table expressions) é um tipo de expressão de tabela disponível no Sql Server. Uma CTE é uma expressão de tabela independente que pode ser nomeada e referenciada uma ou mais vezes na consulta principal.
Uma das utilizações mais importantes das CTEs é a escrita de consultas recursivas. As CTEs recursivas seguem sempre a mesma estrutura – consulta âncora, operador UNION ALL multi-set, membro recursivo e a declaração que invoca a recursividade. CTE recursiva é a recursividade declarativa e como tal tem propriedades diferentes da sua contrapartida imperativa, por exemplo, a verificação da terminação da recursividade declarativa é de natureza implícita – o processo de recursividade pára quando não há linhas retornadas no cte.

anterior.