Muito comum entre Analistas, Cientistas e Engenheiros de dados o uso de CTE’s. Elas são extremamente importantes e úteis para quebrar lógicas de consultas complexas e tornar o código mais legível.

Porém, dentro desse grupo, existem outros recursos que são tão bons quanto e trazem bastante produtividade e facilidade no trabalho de criação de query.

No artigo de hoje quero mostrar como e em que cenários utilizar as Table Expression.

TABLE EXPRESSION: INTRODUÇÃO

Table Expression é um forma de criar expressões de tabela utilizando SQL para unir tabelas em um único resultado.

Essas expressões tabulares precisam ser devidamente nomeadas para criar uma relação válida para que o engine do banco possa reconhecer. Ainda que algumas não necessitem, é recomendado boa prática da linguagem nomear.

Podemos considerar as expressões de tabela como estruturas virtuais, que não armazenam espaço físico, e desaparecem ao final de uma execução de query pelo banco.

As queries internas que compõem  a consulta não são persistidas em nenhum meio físico, elas são unificadas, através de uma operação de MERGE, e após, aplicadas diretamente no objeto que estamos utilizando. A tabela alvo, no caso.

Não existe um ganho extremo de performance com esse recurso, mas logicamente falando, é ótimo para manter a estrutura limpa e o código legível.

Existem quatro tipos de ‘Table Expression’ na linguagem SQL: CTE, VIEW, Table-Valued-Function e Derived Table (subquery table).

Vamos entender cada um dos recursos, começando pela Derived table.

DERIVED TABLE

As tabelas derivadas possuem a construção similar as subqueries(link), mas na maioria esmagadora, são construídas na cláusula FROM, diferente da subquery que tem grande incidência na cláusula WHERE

Table Expression é um forma de criar expressões de tabela utilizando SQL para unir tabelas em um único resultado.

As tabelas derivadas, como podemos imaginar, são totalmente dependentes da query interna. As tabelas da query externa só podem ser selecionadas se estiverem na query interna.

Ao final da construção, é obrigatório atribuir um nome para a query. Veja no exemplo abaixo 

Exemplo de uma tabela derivada em uma consulta.
Exemplo de uma tabela derivada em uma consulta.

Não há nenhuma garantia de ordenamento nesse tipo de construção. Para ordenar a query interna, é preciso inserir o TOP ou OFF-SET FETCH

Falha no uso de ORDER BY por não ter a cláusula TOP ou OFF-SET.
Falha no uso de ORDER BY por não ter a cláusula TOP ou OFF-SET.

Por ser uma construção menor de uma tabela, o SELECT * é possível e não causará problemas.

Demonstração do uso de SELECT * sem penalidade em performance.
Demonstração do uso de SELECT * sem penalidade em performance.

Como uma Derived Table não apresenta impacto no banco, seja positivo ou negativo, o seu uso é mais para facilitar a construção lógica do que performance. Além disso, é melhor para, por exemplo, utilizar colunas com nomes modificados, veja:

Utilizando colunas com nomes modificados em uma Derived Table no SQL Server.
Utilizando colunas com nomes modificados em uma Derived Table no SQL Server.

Embora a Derived Table seja um recurso muito útil, ela tem um grande problema: a legibilidade do código.

As queries internas que compõem  a consulta não são persistidas em nenhum meio físico, elas são unificadas, através de uma operação de MERGE, e após, aplicadas diretamente no objeto que estamos utilizando. A tabela alvo, no caso.

Quanto maior a complexidade da query, pior para ler e debugar caso haja alguma falha de execução. Veja na query abaixo:

Malefício de uma Derived Table: dificuldade na leitura e entendimento da consulta.
Malefício de uma Derived Table: dificuldade na leitura e entendimento da consulta.

Veja que destacado em amarelo eu tenho uma lógica de query construída com diversos JOINs e realizo um JOIN com outra tabela na consulta externa – destacado em vermelho. Em uma situação de múltiplos aninhamentos, seria extremamente difícil decifrar a lógica desta consulta.

Se aumentarmos o número de JOINs, é quase impraticável analisar a query caso haja algum erro. 

JOINS com Derived Table: mostrando a dificuldade de juntar tabelas.
JOINS com Derived Table: mostrando a dificuldade de juntar tabelas.

Para criar expressões de tabelas mais legíveis e fáceis de corrigir, o próximo recurso é mais adequado e ele se chama CTE.

NOTA: não existe uma restrição de uso. Table Expression não causa uma diferença grande na performance geral da query.

As tabelas derivadas permitem o uso de parâmetros em sua construção. Veja que igualo a coluna SalesPersonID com a variável @EMPLID.

Utilizando parâmetros com Derived Table.
Utilizando parâmetros com Derived Table.

Queries normais, com a mesma estrutura, porém maior, teriam o mesmo impacto e o mesmo custo para o banco; de uma forma geral.

CTE

As CTEs possuem as mesmas propriedades de uma Derived Table, com a vantagem de serem mais fáceis de gerenciar.

CTEs são muito utilizadas para quebrar lógicas de queries complexas em partes menores e uni-las ao final. O problema reside no fato de que, dependendo da construção, uma query não poderá ser analisada de forma isolada, por estar utilizando uma CTE anterior como ‘fonte’.

Não existe um ganho extremo de performance com esse recurso, mas logicamente falando, é ótimo para manter a estrutura limpa e o código legível.

Dentre as as Tables Expressions, CTEs são as únicas que permitem recursão.

Veja um exemplo básico de CTE.

Construção de uma CTE
Construção de uma CTE

Se houver a necessidade de passar argumentos para a query, como uma variável para consulta, esta deve vir na query interna.

CTE's também aceitam parâmetro, mas vão na query interna.
CTE’s também aceitam parâmetro, mas vão na query interna.

Como dito no início deste artigo, veja como a CTE organiza o código e diminui a quantidade de linhas. Reconstruindo a mesma query que fizemos na sessão da Derived Table, temos uma consulta mais limpa. 

Demonstração de legibilidade de código, recriando uma query utilizado Derived Table.
Demonstração de legibilidade de código, recriando uma query utilizado Derived Table.

Fica ainda mais fácil criar classificação de clientes – complementando o exemplo anterior.

Um aspecto que vale mencionar ao tratarmos de CTE é a sua capacidade de recursão. Mesmo não sendo performático, é muito útil quando criamos consultas hierárquicas

CTE's recursivas: cuidado com o uso.
CTE’s recursivas: cuidado com o uso.

Vale ressaltar que existe um limite para o nível de recursão no SQL Server. Por padrão ele permite apenas 100 recursões.

Como a estrutura envolve UNION ALL, ambas as queries devem ter as mesmas colunas com o mesmo tipo de dados. Eles devem ser correspondentes.

Ainda que o limite padrão seja 100, o SQL Server permite, através de um hint na query chamado OPTION(MAXRECURSION N) – sendo N, um valor entre 0 até 32767.

Não é muito aconselhável utilizar essa opção, pois a performance é bem degradada.

Além do mais, para executar a recursão, o SQL Server utiliza o TempDB (banco em memória) para armazenar os resultados temporários.

Recursividade na CTE utilizando MAXRECURSION como HINT.
Recursividade na CTE utilizando MAXRECURSION como HINT.

Se por um acaso houver uma CTE recursiva muito grande com o hint habilitado, o TempDB pode crescer vertiginosamente, causando problemas no seu ambiente.

Até o momento, vimos apenas recursos que são ‘executáveis’, não permitindo o reuso. Quero dizer, a tabela derivada e a CTE são estruturas que desaparecem ao final da query e não podem ser reutilizadas a menos que sejam re-executadas. 

Para solucionar essa situação, a linguagem SQL e os bancos de dados possuem outras duas formas de expressões tabulares que são as VIEWS e TVF (table-valued function). Nas próximas seções, vamos analisar como elas funcionam.

VIEWS

VIEWS são queries pré-armazenadas no banco que permitem o reuso sempre que consultadas.

Criação de VIEW a partir da tabela Department.
Criação de VIEW a partir da tabela Department.

Embora pré-armazenadas, o que o banco armazena é a parte lógica da query, economizando espaço em disco e servindo como segurança para tabelas sigilosas.

Somente as VIEWS indexadas são armazenadas fisicamente.

Uma situação particular das VIEWS é que elas não aceitam parâmetros na sua construção.

Mudanças na estrutura física da tabela não afetam as VIEWS, apenas updates nos dados armazenados.

Embora as VIEWS sejam objetos de considerável segurança, existe uma forma de proteger tanto a VIEW quanto a tabela de alterações e inconformidades nos dados.

Quando construímos uma VIEW com SCHEMABINDING, a tabela fonte da VIEW não pode ser alterada sem que a VIEW seja deletada.

Utilizando SCHEMABINDING para proteger a VIEW e a tabela fonte.
Utilizando SCHEMABINDING para proteger a VIEW e a tabela fonte.

Com essa opção de segurança, não é possível alterar a tabela fonte, a menos que a VIEW seja deletada.

Erro de alteração na tabela devido o  SCHEMABINDING.
Erro de alteração na tabela devido o SCHEMABINDING.

Outra recomendação de segurança em VIEWS é bloquear qualquer acesso à dados fora do que foi especificado na criação.

Veja no exemplo abaixo que criei uma VIEW que mostra as pessoas na tabela PERSON.PERSON que estão na tabela HUMANRESOURCES.EMPLOYEE.

Quando tento realizar uma consulta contrária, retorna vazio.

Para criar essa restrição, utilize WITH CHECKOPTION ao final da estrutura.

Restringindo a query apenas às colunas utilizadas na criação da VIEW.
Restringindo a query apenas às colunas utilizadas na criação da VIEW.

VIEWS E ORDENAMENTO

Assim como todas as Table Expression que vimos anteriormente, VIEWS também não podem ser ordenadas em sua criação. A única maneira é criando uma VIEW com TOP ou OFFSET.

Como VIEWS são relacionamentos, não há obrigação de um ordenamento. 

Ainda que seja criada uma VIEW ordenada utilizando TOP ou OFFSET, quando consultamos essa VIEW, continua incerto o ordenamento do resultado. Isso porque, uma nova relação está sendo criada, afinal, estamos consultando a VIEW.

Como garantir um resultado ordenado ao consultar uma VIEW.
Como garantir um resultado ordenado ao consultar uma VIEW.

Para que o resultado seja devidamente ordenado, utilize ORDER BY também na query externa.

Diferente das outras tables expression, VIEWS são as únicas que permitem consultar através de uma DMV, como elas foram criadas. Utilizando a opção WITH ENCRYPTION, o SQL Server passa a esconder a query de criação da VIEW.

Veja a criação dessa VIEW utilizada como exemplo. 

Demonstrando o uso de WITH ENCRYPTION.
Demonstrando o uso de WITH ENCRYPTION.

Agora, utilizando a função de consulta OBJECT_DEFINITION, consigo visualizar como a VIEW foi criada.

Utilizando DMV para consultar a query de criação da VIEW.
Utilizando DMV para consultar a query de criação da VIEW.

Para ocultar a query de criação, ao criar a VIEW, utilize o WITH ENCRYPTION, desse modo.

VIEW criada com WITH ENCRYPTION.
VIEW criada com WITH ENCRYPTION.

Consultando novamente, o próprio banco de dados informa que a VIEW é encriptada.

Resultado da query.
Resultado da query.

TABLE-VALUED FUNCTION

Como último recurso dentro do conjunto que conhecemos como Table Expression, vamos conhecer a Inline Table-Valued Function.

As TVF’s se assemelham muito com as VIEWS, com a diferença que podemos utilizar parâmetros na consulta, customizando o resultado.

Veja o primeiro exemplo de uma TVF.

Criação e consulta na TVF. Veja o ano passado como parâmetro.
Criação e consulta na TVF. Veja o ano passado como parâmetro.

É possível unir uma TVF com uma CTE, desde que ao final da construção, haja uma query selecionando a CTE. Veja

  • Em cinza → a declaração de criação da função;
  • Em Vermelho → a criação da CTE dentro da função;
  • Em Amarelo → o parâmetro de filtro da função criada. Veja que ele vai na CTE;
  • Em Verde → a consulta da CTE dentro da função.
Aninhando uma CTE em uma Tabe-Function.
Aninhando uma CTE em uma Tabe-Function.

Perceba que a query funciona corretamente.

Resultado da query acima.
Resultado da query acima.

Como as TVFs são tabelas, operações de JOIN são permitidas.

Unindo uma CTE com TVF via JOIN.
Resultado do JOIN entre as tabelas.
Resultado do JOIN entre as tabelas.

TABLE EXPRESSION X TEMPORARY TABLE

Ainda que seja criada uma VIEW ordenada utilizando TOP ou OFFSET, quando consultamos essa VIEW, continua incerto o ordenamento do resultado. Isso porque, uma nova relação está sendo criada, afinal, estamos consultando a VIEW.

Pode gerar uma dúvida de qual escolher para executar uma query visando desempenho, uma dica importante é se perguntar se será necessário persistir o resultado em algum local. Se sim, tabelas temporárias ou uma variável de tabela podem ser a solução.

Assim como debatemos casos em que uma subquery é mais rápida e outros casos onde é melhor utilizar o JOIN. Aqui também se aplica a mesma lógica.

OPERADOR APPLY

Criado como uma alternativa ao operador LATERAL do SQL padrão, o SQL Server possui um operador de tabela chamado APPLY, que assim como o JOIN, funciona na cláusula FROM e opera com duas tabelas diferentes.

O APPLY possui dois tipos de opções de uso: CROSS E OUTER APPLY.

O CROSS analisa todas as opções possíveis dentro de uma relação, multiplicando a quantidade de itens da tabela A, pela quantidade da tabela B. Se a tabela A possui 9 linhas e a B 90, o resultado dessa operação será uma tabela com 810 linhas. 

A combinação abaixo ficaria:

  • AF, AG, AH;
  • CF, CG, CH.
Combinação lógica do CROSS APPLY.
Combinação lógica do CROSS APPLY.

O CROSS APPLY possui apenas uma fase de processamento, o OUTER APPLY duas. Com o OUTER, temos a manutenção de itens que não possuem correspondência na relação das tabelas utilizadas – como acontece com os OUTER JOIN.

Existe uma diferença substancial entre APPLY E JOIN. O JOIN funciona como um conjunto, unindo duas tabelas inputadas em um único resultado. Além do mais, não garante ordem, visto que há uma relação.

Já o APPLY, o lado ‘esquerdo’ é avaliado primeiro e depois, o lado direito é aplicado e avaliado linha por linha do lado esquerdo. Isso permite, por exemplo, que o lado direito da construção tenha referências da tabela na esquerda. 

Query utilizando o CROSS APPLY.
Query utilizando o CROSS APPLY.

Para exibição de relações nulas entre as tabelas envolvidas com APPLY, utilize o OUTER.

OUTER APPLY e o NULL no resultado.
OUTER APPLY e o NULL no resultado.

Uma ótima utilização do APPLY é criar uma TVF com o ID dos clientes e o top order para saber quais foram os melhores pedidos. Veja a query abaixo:

Criando uma query com os melhores pedidos dos clientes.
Criando uma query com os melhores pedidos dos clientes.

Veja o resultado:

Os três melhores pedidos do cliente 29825.
Os três melhores pedidos do cliente 29825.

Como podem perceber, o top order do cliente pode ser customizável para qualquer valor, graças a TVF.

CONCLUSÃO

Caso queria criar uma query que não será necessário a sua reutilização, utilize CTE ou DERIVED TABLE.

Quando quiser estruturas reutilizáveis e consultas frequentes, a melhor opção é VIEW ou TVF. Escolha VIEW caso não queria uma construção parametrizada e TVF caso queira.

O APPLY é indicado para situações que há um desejo de aplicar as linhas de uma tabela na outra e retornar o resultado como uma tabela unificada. É uma excelente alternativa em relação ao JOIN.

OLÁ!

Se chegou até aqui e gostou do pequeno tutorial, deixe seu like compartilhe. Ajude o blog a alcançar mais pessoas!

Baixe o arquivo .SQL desse artigo AQUI.

Caso queira receber em primeira mão esse tipo de conteúdo, se inscreva abaixo:

SIGA NAS REDES SOCIAIS.

Obrigado! Volte Sempre!