Expressão de tabela comum do servidor SQL

A expressão de tabela comum ou o CTE é o conjunto de resultados nomeado, que foi introduzido no SQL Server 2005. A expressão comum da tabela atua como uma tabela virtual com registros e colunas criadas durante a execução de uma consulta com CTE e liberada após a conclusão da consulta. Pode ser referenciado em qualquer instrução Select, Inserir, Atualizar ou Excluir. Isso é usado para criar uma visão também.

CTE pode ser definido pela sintaxe abaixo.

[COM [… ]]
Nome de CTE [(Nome das colunas [,…])]
Como (consulta de CTE)
Selecione * de CTE

Exemplo:

Com cte_name (coluna1, column2, column3)
Como
(
Selecione Column1, Column2, Column3
Da Tabela 1
Onde coluna 1> 500
)

De acordo com o exemplo, depois de definir o cte cte_name, podemos usar o CTE imediatamente após defini -lo como uma tabela. Abaixo está um exemplo:

Selecione em cte_name

Ele retornará a saída de três colunas, coluna1, coluna2 e coluna3.

Pode ser usado nas declarações de inserção, excluir, atualizar e mesclar também. Vamos mostrar um exemplo de cada um abaixo.

Múltiplo CTE

Múltiplo CTE pode ser usado em uma única consulta.

Com cte_name1 (coluna1, column2, column3)
Como
(
Selecione Column1, Column2, Column3
Da Tabela 1
Onde coluna 1> 100
)
COMO
(
Selecione * de cte_name2
onde coluna2> 200
)
Selecione * de cte_name2

A consulta acima retornará os registros da tabela Tabela1 em que a coluna 1 é maior que 100 e a coluna 2 é maior que 200.

Exclua usando CTE

O CTE pode ser muito útil para excluir registros de uma tabela.

Com cte_name (coluna1, column2, column3)
Como
(
Selecione Column1, Column2, Column3
Da Tabela 1
Onde coluna 1> 100
)
Exclua de cte_name

A declaração acima excluirá os registros da tabela base: Tabela Tabela1, onde o valor da coluna 1 é superior a 100.

Esta também é a maneira eficiente de eliminar entradas duplicadas de uma mesa. Abaixo está o exemplo.

Com cte_name (id, column1, column2, column3, rn)
Como
(
Selecione ID, Column1, Column2, Column3, Row_Number () sobre (Partição por ID Order by ID) como RN
Da Tabela 1
)
Exclua de cte_name
Onde cte_name. Rn> 1

Isso excluirá todas as linhas duplicadas da tabela Tabela1.

Insira usando CTE

Podemos inserir um conjunto de dados específico que é definido em um CTE em outra mesa. Olhe para o exemplo abaixo.

Com cte_insert (id, column1, column2, column3)
Como
(
Selecione ID, Column1, Column2, Column3
Da Tabela 1
Onde coluna 1> 200
)
/* Para inserção em uma tabela existente Dest_table*/
Inserir em dest_table (column1, column2, column3)
Selecione Column1, Column2, Column3 de CTE_INSERT
/ * Para criar uma nova tabela dest_table_new e insira os dados do CTE */
Selecione Column1, Column2, Column3
Em dest_table_new

A declaração acima criará a tabela com as três colunas- coluna1, coluna2, column3 e inserir dados nela.

Atualizar usando CTE

O conceito de atualização usando CTE é o mesmo que inserção e exclusão. Vamos verificar o exemplo abaixo.

Com cte_update (id, column1, column2, column3)
Como
(
Selecione ID, Column1, Column2, Column3
Da Tabela 1
Onde coluna 1> 200
)
/* Atualize a tabela base- Tabela1, do CTE para aumentar o valor da coluna1 por 100*/
Atualize CTE_UPDATE
Set Column1 = Column1+100
/*Atualize outra tabela - DEST_TABLE, usando o valor de CTE*/
Atualizar a
defina a.coluna 1 = b.coluna1
de dest_table a
Junte -se a cte_update b
com um.id = b.eu ia

Merge usando CTE

Consulte o exemplo abaixo para obter uma melhor compreensão.

Com src_cte (id, column1, column2, column3)
COMO
(
Selecione ID, Column1, Column2, Column3 de SRC_TABLE
)
Mesclar
tgt_tbl como alvo
Usando SRC_CTE como fonte
On (alvo.id = fonte.eu ia)
Quando correspondido então
Atualizar Definir destino.Coluna1 = fonte.Coluna1,
alvo.Coluna2 = fonte.Coluna2,
alvo.Coluna3 = fonte.Coluna3
Quando não comparado então
Inserir valores (coluna 1, colunla, colunl3) (fonte.Coluna1, fonte.Column2, fonte.Column3);

Na consulta acima, estamos tentando carregar dados incrementalmente do SRC_TABLE para o TGT_TABLE.

Como CTE, tabela temp e variável temp são adiadas no servidor SQL?

A partir dos últimos exemplos, conhecemos os usos do CTE e tivemos uma idéia clara do que é CTE. Agora, a diferença entre o CTE é e a tabela de temperatura e a variável temp é:

• • O CTE sempre precisa de memória, mas as tabelas de temperatura precisam de um disco. A variável de tabela usa ambos. Portanto, não devemos usar o CTE quando houver mais volume de dados.
  • O escopo da variável da tabela é apenas para o lote e o escopo da tabela de temperatura é para a sessão e o escopo do CTE é apenas para a consulta.

Conclusão

O CTE pode ser útil quando você precisar gerar conjunto de resultados temporários e pode ser acessado em instrução selecionada, inserir, atualizar, excluir e mesclar. Pode ser muito otimizado em termos de CPU e usos de memória.