Ola! esse artigo está melhor escrito aqui. Mas fique a vontade para lê-lo completamente.
TABLE EXPRESSION
São tabelas que originam outras tabelas baseados em uma query interna que fornece o resultado para query externa.
A query interna não pode ter order by na sua declaração para ordenamento. Mas se utilizar as cláusulas top e offset – fetch, passa a ser permitido.
Este primeiro exemplo de demonstração, vimos uma tabela derivada utilizando order by para suportar o uso do offset fetch.
TABLE EXPRESSION X TEMPORARY TABLE
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 anterior onde 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.
Ainda que não pareça, tanto a query interna quanto a externa são partes de uma relação e todas as colunas devem ter seus devidos nomes e que sejam únicos. Se for atribuído um novo nome de coluna na query interna, este deve ser referido na externa.
Um aspecto que podemos citar é que o resultado da query interna não fica armazenado fisicamente. Assim, tanto a externa quanto a interna sofrem uma operação de MERGE unificando o resultado.
O único caso em que uma table expression fica armazenada fisicamente é quando temos uma view indexada.
Tabelas derivadas
A tabela derivada tem na sua criação o mesmo formato de uma subquery, porém é obrigatório receber um nome ao final da sua construção.
Como dito acima e por ser uma subcategoria de table expression, veja o plano de execução desta query:
A query acima foi executada toda junta, como uma tabela derivada deve ser executada. Agora repare quando executamos apenas a query interna. Sim, aqui, assim como nas subqueries, também é possível!
Reparem que mesmo executando a query interna isoladamente, o plano de execução não mudou em absolutamente nada.
Agora, vamos supor que queremos um filtro mais avançado baseado num determinado número de linhas que será retornado utilizando a função row_number. Para isso, precisamos do seguinte recurso, uma vez que logicamente, não seria possível ocorrer com uma query normal já que a row_number é executada antes do select.
A desvantagem de utilizar uma tabela derivada, veja abaixo:
Este tipo de query pode se tornar bem mais complexa e repetitiva porque todas as colunas da query mais interna devem ser passadas para a externa até chegar ao topo e isso induz muito ao erro.
Como alternativa a este problema, vamos ver o próximo recurso chamado C.T.E
C.T.E
Um dos grandes recursos da linguagem SQL é este. Preenche com sucesso as lacunas da tabela derivada, como vimos no tópico anterior.
Nela temos a possibilidade de elaborar qualquer tipo de query bastando selecionar as colunas e referenciar a c.t.e na cláusula from da query.
Outro ponto positivo que podemos destacar no uso de c.t.e é a sua versatilidade e facilidade de construção podendo inclusive ter quantas acharmos necessárias e unir com joins.
Abaixo, um exemplo simples para fixar o conteúdo
RECURSÃO DE C.T.E
Diferente das tabelas derivadas que não permitem a auto referência na própria query, utilizando a C.T.E não temos este impedimento podendo assim referenciar a C.T.E na própria construção da tabela.
Não esqueça de executar o código que está no script.
Quando formos criar a recursão na C.T.E é importante saber que temos duas “classes” uma chamada de membro âncora e a outra chamada de membro recursivo.
No exemplo abaixo, a primeira query circulada de vermelho é o nosso membro âncora dentro da CTE. Pode ser executado com uma query auto contida e é este quem cria o resultado inicial para o membro recursivo da query.
Já a query circulada em preto é a query recursiva, que referencia a CTE tendo seu resultado unificado pelo union all.
O operador de conjunto UNION ALL unifica o resultado retornado pela query âncora com a query recursiva e este finaliza quando o resultado desta operação retorna vazio.
A imagem abaixo mostra apenas um trecho do plano de execução da query onde trouxe a quantidade de vezes que esta query foi executada até que retornasse o resultado final:
Por padrão, o SQL Server não permite mais do que 100 operações de recursão por uma query ou qualquer outra operação. Se passar do valor, a query irá falhar e retornar erro.
O problema da C.T.E recursiva é que sua performance não é das melhores e não temos muita liberdade de aplicar filtros, utilizar index ou controlar como as tabelas trabalham na execução da query.
VIEWS E TABLE-VALUE FUNCTIONS
Como parte final deste post, vamos ver os dois únicos objetos dentro do grupo de table expression que ficam armazenados dentro do banco.
Este dois são soluções para CTEs e tabelas derivadas que não ficam armazenadas no banco e ao final da execução e retorno do resultado, desaparecem. Além do mais, temos o controle de quem acessa e quais informações visualiza, permitindo um controle maior sobre os dados confidenciais.
A diferença entre uma view e uma função de tabela é que a última aceita um parâmetro de entrada que é criado na função e utilizado na sua execução, diferente da view que não aceita.
Criação de uma view.
Em alguns casos, o SQL Server demora um pouco para commitar uma operação, por isso as linhas vermelhas acusando erro.
Se quisermos garantir alguns padrões de segurança para uma view, podemos utilizar duas opções para sua criação schemabinding e check. Com o schemabinding atrelamos a view em um schema de tabela e trava qualquer mudança na view que não parta da tabela.
Com o check, travamos a view a qualquer mudança que seja fora do escopo do filtro where.
Exemplo de tentativa de atualização de uma view com check:
TABLE-VALUED FUNCTION
Muito similares a uma view também armazenando no banco a definição e não os dados físicos.
A diferença entre uma T.V.F e uma view é que aqui, temos permissão para passar um valor como parâmetro.
Aceitam parâmetros de variáveis na sua criação.
Sempre que possível atribuir um nome para a table expression.
Também possui restrição quanto ao order by; Usar somente na query que chama a T.V.F.
Criando uma função de tabela.
Abaixo, como executamos o select.
Criando uma função com duas variáveis. Neste caso, quando formos executar um select neste tipo de função, é obrigatório passar o valor para as duas variáveis.
Veja abaixo, após a criação
Também é possível realizar um join entre uma T.V.F e uma tabela física, veja abaixo.
Ainda dentro deste assunto sobre tabelas derivadas, quero destacar que elas não são utilizadas apenas para select e visualização de dados. Podem ser um excelente recurso tanto para update quanto para delete.
Abaixo um exemplo de como utilizar para uma operação de update.
A mesma lógica poderia ser aplicada para o delete caso fosse do desejo.
RESUMO
As tabelas derivadas são um excelente e performático recurso para consulta e criação de queries, principalmente as C.T.E’s.
As Views e as T.V.F podem ser utilizadas para situações em que uma determinada tabela tenha dados sensíveis e por regra, é preciso restringir o acesso a estes.
Nenhuma delas, salvo as views indexadas (post futuro), são armazenadas de fato no disco. O que temos aqui são definições para a criação destas e quando invocadas por um select, retornam seu resultado.
E se quiser baixar o script do que foi visto aqui, segue o link!
Espero que gostem, saúde!
ManageBI 