Usaremos as Common Table Expressions ou CTEs do SQL Server para facilitar junções e subconsultas complexas. Também fornece uma maneira de consultar dados hierárquicos, como uma hierarquia organizacional. Este artigo fornece uma visão geral completa do CTE, tipos de CTE, vantagens, desvantagens e como usá-los no SQL Server.

O que é CTE no SQL Server?

Um CTE (Common Table Expression) é um conjunto de resultados único que existe apenas durante a consulta . Ele nos permite fazer referência a dados dentro de um único escopo de execução da instrução SELECT, INSERT, UPDATE, DELETE, CREATE VIEW ou MERGE. É temporário porque seu resultado não pode ser armazenado em lugar nenhum e será perdido assim que a execução da consulta for concluída. Ele veio primeiro com a versão SQL Server 2005. Um DBA sempre preferiu usar CTE como alternativa a uma subconsulta/visualização. Eles seguem o padrão ANSI SQL 99 e são compatíveis com SQL.

Sintaxe CTE no SQL Server

A sintaxe CTE inclui um nome CTE, uma lista de colunas opcional e uma instrução/consulta que define a expressão de tabela comum (CTE). Depois de definir o CTE, podemos usá-lo como visualização em uma consulta SELECT, INSERT, UPDATE, DELETE e MERGE.

A seguir está a sintaxe básica do CTE no SQL Server:

 WITH cte_name (column_names) AS (query) SELECT * FROM cte_name; 

Nesta sintaxe:

• Especificamos primeiro o nome do CTE que será mencionado posteriormente em uma consulta.
• A próxima etapa é criar uma lista de colunas separadas por vírgulas. Ele garante que o número de colunas nos argumentos de definição de CTE e o número de colunas na consulta sejam iguais. Se não tivermos definido as colunas dos argumentos do CTE, serão utilizadas as colunas de consulta que definem o CTE.
• Depois disso, usaremos a palavra-chave AS após o nome da expressão e definiremos uma instrução SELECT cujo conjunto de resultados preencherá o CTE.
• Por fim, usaremos o nome CTE em uma consulta como instrução SELECT, INSERT, UPDATE, DELETE e MERGE.

Deve-se ter isso em mente ao escrever a definição da consulta CTE; não podemos usar as seguintes cláusulas:

1. ORDER BY, a menos que você também use a cláusula TOP
2. EM
3. Cláusula OPTION com dicas de consulta
4. PARA NAVEGAR

A imagem abaixo é a representação da definição da consulta CTE.

Aqui, a primeira parte é uma expressão CTE que contém uma consulta SQL que pode ser executada de forma independente em SQL. E a segunda parte é a consulta que utiliza o CTE para exibir o resultado.

Exemplo

Vamos entender como o CTE funciona no SQL Server usando vários exemplos. Aqui, vamos usar uma tabela ' cliente 'para uma manifestação. Suponha que esta tabela contenha os seguintes dados:

Neste exemplo, o nome CTE é clientes_em_novayork , a subconsulta que define o CTE retorna as três colunas nome do cliente, e-mail, e estado . Como resultado, o CTE clients_in_newyork retornará todos os clientes que moram no estado de Nova York.

Depois de definir o CTE clients_in_newyork, nós o referenciamos no SELECIONAR extrato para obter os detalhes dos clientes que estão localizados em Nova York.

 WITH customers_in_NewYork AS (SELECT * FROM customer WHERE state = 'New York') SELECT c_name, email, state FROM customers_in_NewYork; 

Depois de executar a instrução acima, ele fornecerá a seguinte saída. Aqui, podemos ver que o resultado retorna apenas as informações dos clientes localizados no estado de Nova York.

CTE múltiplo

Em alguns casos, precisaremos criar várias consultas CTE e juntá-las para ver os resultados. Podemos usar o conceito de múltiplos CTEs neste cenário. Precisamos usar o operador vírgula para criar várias consultas CTE e mesclá-las em uma única instrução. O operador de vírgula ',' deve ser precedido pelo nome do CTE para distinguir vários CTE.

Vários CTEs nos ajudam a simplificar consultas complexas que eventualmente são unidas. Cada peça complexa tinha seu próprio CTE, que poderia então ser referenciado e unido fora da cláusula WITH.

NOTA: A definição de vários CTE pode ser definida usando UNION, UNION ALL, JOIN, INTERSECT ou EXCEPT.

A sintaxe abaixo explica isso mais claramente:

 WITH cte_name1 (column_names) AS (query), cte_name2 (column_names) AS (query) SELECT * FROM cte_name UNION ALL SELECT * FROM cte_name; 

Exemplo

Vamos entender como vários CTE funcionam no SQL Server. Aqui, vamos usar o acima ' cliente 'mesa para uma demonstração.

Neste exemplo, definimos os dois nomes de CTE clientes_em_novayork e clientes_na_california . Em seguida, o conjunto de resultados de subconsultas desses CTEs preenche o CTE. Por fim, utilizaremos os nomes dos CTEs em uma consulta que retornará todos os clientes que estão localizados em Nova Iorque e Estado da Califórnia .

 WITH customers_in_NewYork AS (SELECT * FROM customer WHERE state = 'New York'), customers_in_California AS (SELECT * FROM customer WHERE state = 'California') SELECT c_name, email, state FROM customers_in_NewYork UNION ALL SELECT c_name, email, state FROM customers_in_California; 

Estado de Nova York e Califórnia.

Por que precisamos do CTE?

Assim como as visualizações de banco de dados e tabelas derivadas, os CTEs podem facilitar a gravação e o gerenciamento de consultas complexas, tornando-as mais legíveis e simples. Podemos realizar essa característica dividindo as consultas complexas em blocos simples que podem ser reutilizados na reescrita da consulta.

Alguns de seus casos de uso são fornecidos abaixo:

• É útil quando precisamos definir uma tabela derivada várias vezes em uma única consulta.
• É útil quando precisamos criar uma alternativa para uma visualização no banco de dados.
• É útil quando precisamos realizar o mesmo cálculo várias vezes em vários componentes de consulta simultaneamente.
• É útil quando precisamos usar funções de classificação como ROW_NUMBER(), RANK() e NTILE().

Algumas de suas vantagens são apresentadas a seguir:

tabela ascii em c

• CTE facilita a manutenção do código.
• CTE aumenta a legibilidade do código.
• Aumenta o desempenho da consulta.
• O CTE possibilita implementar consultas recursivas facilmente.

Tipos de CTE no SQL Server

O SQL Server divide o CTE (Expressões de Tabela Comum) em duas categorias amplas:

1. CTE recursivo
2. CTE não recursivo

CTE recursivo

Uma expressão de tabela comum é conhecida como CTE recursiva que faz referência a si mesma. Seu conceito é baseado na recursão, que é definida como ' a aplicação de um processo ou definição recursiva repetidamente .' Quando executamos uma consulta recursiva, ela itera repetidamente em um subconjunto de dados. É simplesmente definido como uma consulta que chama a si mesma. Existe uma condição final em algum momento, então ela não se chama infinitamente.

Um CTE recursivo deve ter um UNIÃO TODOS declaração e uma segunda definição de consulta que faz referência ao próprio CTE para ser recursiva.

Exemplo

Vamos entender como funciona o CTE recursivo no SQL Server. Considere a afirmação abaixo, que gera uma série dos primeiros cinco números ímpares:

 WITH odd_num_cte (id, n) AS ( SELECT 1, 1 UNION ALL SELECT id+1, n+2 from odd_num_cte where id <5 ) select * from odd_num_cte; < pre> <p>When we execute this recursive CTE, we will see the output as below:</p> <img src="//techcodeview.com/img/sql-server-tutorials/86/common-table-expression-sql-server-5.webp" alt="CTE in SQL Server"> <p>The below example is the more advanced recursive CTE. Here, we are going to use the &apos; <strong>jtp_employees</strong> &apos; table for a demonstration that contains the below data:</p> <img src="//techcodeview.com/img/sql-server-tutorials/86/common-table-expression-sql-server-6.webp" alt="CTE in SQL Server"> <p>This example will display the hierarchy of employee data. Here table provides a reference to that person&apos;s manager for each employee. The reference is itself an employee id within the same table.</p> <pre> WITH cte_recursion (EmpID, FirstName, LastName, MgrID, EmpLevel) AS ( SELECT EmployeeID, FirstName, LastName, ManagerID, 1 FROM jtp_employees WHERE ManagerID IS NULL UNION ALL SELECT emp.EmployeeID, emp.FirstName, emp.LastName, emp.ManagerID, r.EmpLevel + 1 FROM jtp_employees emp INNER JOIN cte_recursion r ON emp.ManagerID = r.EmpID ) SELECT FirstName + &apos; &apos; + LastName AS FullName, EmpLevel, (SELECT FirstName + &apos; &apos; + LastName FROM jtp_employees WHERE EmployeeID = cte_recursion.MgrID) AS Manager FROM cte_recursion ORDER BY EmpLevel, MgrID </pre> <p>This CTE will give the following output where we can see the hierarchy of employee data:</p> <img src="//techcodeview.com/img/sql-server-tutorials/86/common-table-expression-sql-server-7.webp" alt="CTE in SQL Server"> <h3>Non-Recursive CTE</h3> <p>A common table expression that doesn&apos;t reference itself is known as a non-recursive CTE. A non-recursive CTE is simple and easier to understand because it does not use the concept of recursion. According to the CTE Syntax, each CTE query will begin with a &apos; <strong>With</strong> &apos; clause followed by the CTE name and column list, then AS with parenthesis.</p> <h2>Disadvantages of CTE</h2> <p>The following are the limitations of using CTE in SQL Server:</p> <ul> <li>CTE members are unable to use the keyword clauses like Distinct, Group By, Having, Top, Joins, etc.</li> <li>The CTE can only be referenced once by the Recursive member.</li> <li>We cannot use the table variables and CTEs as parameters in stored procedures.</li> <li>We already know that the CTE could be used in place of a view, but a CTE cannot be nested, while Views can.</li> <li>Since it&apos;s just a shortcut for a query or subquery, it can&apos;t be reused in another query.</li> <li>The number of columns in the CTE arguments and the number of columns in the query must be the same.</li> </ul> <hr></5>

Este CTE fornecerá a seguinte saída onde podemos ver a hierarquia dos dados dos funcionários:

CTE não recursivo

Uma expressão de tabela comum que não faz referência a si mesma é conhecida como CTE não recursiva. Um CTE não recursivo é simples e mais fácil de entender porque não utiliza o conceito de recursão. De acordo com a sintaxe CTE, cada consulta CTE começará com um ' Com 'seguida pelo nome do CTE e lista de colunas, depois AS entre parênteses.

Desvantagens do CTE

A seguir estão as limitações do uso do CTE no SQL Server:

• Os membros do CTE não podem usar cláusulas de palavras-chave como Distinct, Group By, Tendo, Top, Joins, etc.
• O CTE só pode ser referenciado uma vez pelo membro recursivo.
• Não podemos usar variáveis ​​de tabela e CTEs como parâmetros em procedimentos armazenados.
• Já sabemos que o CTE poderia ser usado no lugar de uma visualização, mas um CTE não pode ser aninhado, enquanto as visualizações podem.
• Como é apenas um atalho para uma consulta ou subconsulta, não pode ser reutilizado em outra consulta.
• O número de colunas nos argumentos CTE e o número de colunas na consulta devem ser iguais.