Expresión de la tabla común del servidor SQL

La expresión de la tabla común o el CTE es el conjunto de resultados con nombre, que se introdujo en SQL Server 2005. La expresión de la tabla común actúa como una tabla virtual con registros y columnas que se crea durante la ejecución de una consulta con CTE y se libera después de completar la consulta. Se puede hacer referencia dentro de cualquier instrucción Seleccionar, Insertar, Actualizar o Eliminar. Esto se usa para crear una vista también.

CTE se puede definir por la sintaxis a continuación.

[CON [… ]]
Nombre de CTE [(Nombre de las columnas [, ...])]
Como (consulta de CTE)
Seleccionar * de CTE

Ejemplo:

Con cte_name (columna1, columna2, columna3)
Como
(
Seleccionar columna1, columna2, columna3
De la tabla1
Donde columna1> 500
)

Según el ejemplo, después de definir el CTE CTE_Name, podemos usar el CTE inmediatamente después de definirlo como una tabla. A continuación se muestra un ejemplo:

Seleccionar de cte_name

Devolverá la salida de tres columnas, columna1, columna2 y columna3.

También se puede usar en las declaraciones Insertar, Eliminar, actualizar y fusionar. Mostraremos un ejemplo de cada uno a continuación.

Múltiples CTE

Se pueden usar múltiples CTE en una sola consulta.

Con CTE_NAME1 (columna1, columna2, columna3)
Como
(
Seleccionar columna1, columna2, columna3
De la tabla1
Donde columna1> 100
)
COMO
(
Seleccione * de CTE_Name2
donde columna 2> 200
)
Seleccione * de CTE_Name2

La consulta anterior devolverá los registros de la tabla 1 donde la columna1 es mayor que 100 y la columna 2 es mayor que 200.

Eliminar con CTE

CTE puede ser muy útil para eliminar registros de una mesa.

Con cte_name (columna1, columna2, columna3)
Como
(
Seleccionar columna1, columna2, columna3
De la tabla1
Donde columna1> 100
)
Eliminar de cte_name

La declaración anterior eliminará los registros de la tabla base: Tabla 1 donde el valor de la columna1 es más de 100.

Esta es también la forma eficiente de eliminar las entradas duplicadas de una mesa. A continuación se muestra el ejemplo.

Con cte_name (id, column1, column2, column3, rn)
Como
(
Seleccione ID, columna1, columna2, columna3, row_number () Over (Partition by ID Order por ID) como RN
De la tabla1
)
Eliminar de cte_name
Donde cte_name. RN> 1

Esto eliminará todas las filas duplicadas de la tabla 1.

Insertar usando CTE

Podemos insertar un conjunto de datos específico que se define en un CTE en otra tabla. Mira el siguiente ejemplo.

Con cte_insert (id, columna1, columna2, columna3)
Como
(
Seleccionar ID, columna1, columna2, columna3
De la tabla1
Donde columna1> 200
)
/* para la inserción en una tabla existente Dest_table*/
Insertar en Dest_Table (columna1, columna2, columna3)
Seleccionar columna1, columna2, columna3 de cte_insert
/ * Para crear una nueva tabla des_table_new e inserte los datos del CTE */
Seleccionar columna1, columna2, columna3
En des_table_new

La declaración anterior creará la tabla con las tres columnas: columna1, columna2, columna3 e insertar datos en ella.

Actualizar usando CTE

El concepto de actualización usando CTE es el mismo que la inserción y la eliminación. Vamos a ver el ejemplo a continuación.

Con CTE_UPDATE (ID, columna1, columna2, columna3)
Como
(
Seleccionar ID, columna1, columna2, columna3
De la tabla1
Donde columna1> 200
)
/* Actualizar la tabla base- Tabla1, de CTE para aumentar el valor de la columna1 por 100*/
Actualizar CTE_UPDATE
Establecer columna1 = columna1+100
/*Actualizar otra tabla - Dest_table, usando el valor de CTE*/
actualizar un
establecer un.columna1 = b.columna1
de dest_table a
Únete a CTE_UPDATE B
en un.ID = B.identificación

Fusionarse con CTE

Consulte el ejemplo a continuación para obtener una mejor comprensión.

Con src_cte (id, columna1, columna2, columna3)
COMO
(
Seleccionar ID, Columna1, Columna2, Columna3 de SRC_Table
)
UNIR
TGT_TBL como objetivo
Usando src_cte como fuente
En el blanco.id = fuente.identificación)
Cuando se combina entonces
Actualizar el objetivo establecer.Columna1 = fuente.Columna1,
objetivo.Columna2 = fuente.Columna2,
objetivo.Columna3 = fuente.Columna3
Cuando no se coincide entonces entonces
Insertar (columna1, columna2, columna3) valores (fuente.Columna1, fuente.Columna2, fuente.Columna3);

En la consulta anterior, estamos tratando de cargar datos de forma incremental desde el src_table a la tgt_table.

Cómo se difunden CTE, Temp Tabla y Temp Variable en el servidor SQL?

De los últimos ejemplos, conocemos los usos de CTE y tuvimos una idea clara de lo que es CTE. Ahora, la diferencia entre CTE es y TEMP TABLE y TEMP variable es:

• • CTE siempre necesita memoria, pero las tablas temperadas necesitan un disco. La variable de tabla usa ambos. Entonces, no debemos usar CTE cuando hay más volumen de datos.
  • El alcance de la variable de tabla es solo para el lote y el alcance de la tabla temporal es para la sesión y el alcance de CTE es solo para la consulta.

Conclusión

CTE puede ser útil cuando necesite generar resultados de resultados temporales y se puede acceder en la declaración Seleccionar, Insertar, Actualizar, Eliminar y Fusionar. Puede estar muy optimizado en términos de uso de CPU y memoria.