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.
ÍNDICE:
- TABLE EXPRESSION: INTRODUÇÃO
- DERIVED TABLE
- CTE
- VIEWS
- TABLE-VALUED FUNCTION
- TABLE EXPRESSION X TEMPORARY TABLE
- OPERADOR APPLY
- CONCLUSÃO
- OLÁ!
- SIGA NAS REDES SOCIAIS.
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
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.
Por ser uma construção menor de uma tabela, o SELECT * é possível e não causará problemas.
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:
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:
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.
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.
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.
Se houver a necessidade de passar argumentos para a query, como uma variável para consulta, esta deve vir 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.
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.
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.
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.
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.
Com essa opção de segurança, não é possível alterar a tabela fonte, a menos que a VIEW seja deletada.
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.
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.
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.
Agora, utilizando a função de consulta OBJECT_DEFINITION, consigo visualizar como a VIEW foi criada.
Para ocultar a query de criação, ao criar a VIEW, utilize o WITH ENCRYPTION, desse modo.
Consultando novamente, o próprio banco de dados informa que a VIEW é encriptada.
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.
É 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.
Perceba que a query funciona corretamente.
Como as TVFs são tabelas, operações de JOIN são permitidas.
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.
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.
Para exibição de relações nulas entre as tabelas envolvidas com APPLY, utilize o OUTER.
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:
Veja o resultado:
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 e 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:
ManageBI 