SQL-¿Cómo capturar los valores identity recién generados después de una inserción de varias filas?

Hace poco recibí una pregunta muy interesante, ¿alguien preguntó si hay una forma de capturar los valores identity recién generados después de una inserción de varias filas en una tabla?

Respuesta: Utilizando la cláusula de OUTPUT.

SQL admite una cláusula OUTPUT, que puede utilizar para devolver información afectada por una instrucción INSERT, UPDATE, DELETE o MERGE.

Cualquier referencia a las columnas en la tabla que se está modificando debe ser calificada con la palabra clave INSERTED o DELETED, la palabra clave es solo un prefijo que especifica qué valores se deben generar.

INSERTED devuelve el valor después de UPDATE, INSERT o MERGE.

DELETED devuelve el valor antes de UPDATE, DELETE o MERGE.

El laboratorio.

Preparemos el entorno, para ello ejecute el siguiente script para crear una tabla:

Clausula OUTPUT en Declaraciones INSERT

En este ejemplo, vamos a insertar 20 registros en la tabla que hemos creado y mostramos los datos insertados al usuario.

La cláusula OUTPUT se coloca entre INSERT INTO y VALUES, tenga en cuenta que estoy usando la palabra clave INSERTED como prefijo.

Resultados:

Clausula OUTPUT En Declaraciones DELETE

En este ejemplo, vamos a eliminar todos los registros donde 'myValue_2' es mayor que 8 y mostramos los datos eliminados al usuario.

En una declaración DELETE, se agrega la cláusula OUTPUT entre la cláusula DELETE y WHERE, tenga en cuenta que estoy usando la palabra clave DELETED como prefijo.

Resultados:

Clausula OUTPUT En Declaraciones UPDATE

En este ejemplo, vamos a actualizar "myValue_1" donde "myValue_1" es igual a "myValue_2" y mostraremos los datos actualizados al usuario.

En una declaración de UPDATE , agrega la cláusula OUTPUT entre la cláusula SET y WHERE, tenga en cuenta que estoy usando ambas palabras clave, esto es muy importante ya que DELETE se usa para generar el valor anterior y INSERTED se usa para generar el valor actual.

Resultados:

Bonus: OUTPUT A una tabla temporal.

En este ejemplo, vamos a generar los datos insertados en una tabla temporal.(#MyAudit).

Resultados:

¡Diviértanse aprendiendo!

SQL-¿Cómo generar una secuencia de fechas entre dos fechas dadas?

Hace poco recibí una pregunta muy interesante, ¿una de mis conexiones en LinkedIn me preguntó si sabía cómo generar una secuencia de fechas entre dos fechas?

Lo primero que me vino a la mente fue crear una tabla de calendario, ya que son muy útiles, el me dijo que eso suena bien, pero realmente quiero saber cómo escribir la consulta.

Esto es lo que estaba buscando:

Exploremos tres métodos diferentes que generarán la secuencia.

Método 1: WHILE loop

There is usually a set based alternative that will perform much better.

Método 2: CTE Recursiva

Método 3: Cross Join y Top

Los métodos anteriores producen el siguiente resultado:

Déjame saber si conoces otros métodos en la sección de comentarios :).

¡Diviértanse aprendiendo!

IN vs EXISTS - ¿Cuál es más rápido?

En teoría, EXISTS debe ser más rápido porque solo comprueba si existe una fila o no (Verdadero o Falso), por otro lado, IN debe recopilar el conjunto de resultados antes de probar la condición.

¿Es cierta la afirmación anterior?

Según mis pruebas de laboratorio, la respuesta es "Depende", básicamente el optimizador trata EXISTS e IN de la misma manera cuando es posible, pero depende del esquema de la tabla, datos y la forma en la que se escribió la consulta.

¿Cómo usarlo?

IN: (Subconsulta| Expresión[ ,...n ])

• Expresión: puedes pasar una lista de literales IN (1,2,3, N) o un solo valor IN (1)

• Subconsulta: es una subconsulta que tiene un conjunto de resultados de una columna. EN (SELECT A FROM B)

EXISTS: (Subconsulta)

• Subconsulta: es una subconsulta para comparar la existencia de la fila (VERDADERO o FALSO), el conjunto de resultados no importa.

El Lab (IN vs EXISTS)

Preparemos el entorno, para ello ejecute el siguiente script para crear dos tablas:

Tenemos dos tablas, "MyMainTable" (300K filas) es nuestra tabla principal y "MySubTable" (25% de datos aleatorios de "MyMainTable") esta es la tabla que vamos a utilizar para filtrar los datos.

Veamos cómo EXISTS y IN se comportan.

Note que he agregado una división entre cero en la declaración EXISTS, es probable que estés esperando un error, pero lo interesante es que la lista de campos en una declaración EXISTS no se ejecutará y la consulta se ejecutará.

He forzado esas consultas para que no se ejecuten en paralelo, esto es para obtener un plan de ejecución muy simple que podamos comparar fácilmente.

Aquí están los planes de ejecución y son idénticos , dos clustered index scan y un hash match.

Los IOs también son exactamente iguales.

Así que el optimizador de consultas trató ambas consultas de la misma manera. No hay diferencias de rendimiento aquí.

Agreguemos algunos índices y veamos cómo se comportan.

Los planes de ejecución son idénticos, aunque ahora tenemos un index scan, un stream aggregate y un merge join debido a los nuevos índices.

Los IOs también son exactamente iguales.

Hasta ahora, el IN y el EXISTS parecen funcionar de manera idéntica, sin importar si hay un índice o no, incluso cuando hay valores nulos en la subconsulta o en la tabla externa.

¿Qué hay de NOT IN vs NOT EXISTS?

Veamos cómo NO EXISTS y NOT IN se comportan. Tenga en cuenta que ya he creado algunos índices.

Una vez más, los IOs y los planes de ejecución son exactamente iguales.

Hasta ahora parecen actuar de forma idéntica.

Espera ... El problema potencial con valores NULL.

Si tiene un valor NULL en el resultado de la subconsulta o en la expresión, IN y EXISTS darán el mismo conjunto de resultados, pero NOT IN se comporta de manera diferente.

Vamos a alterar las columnas en nuestras tablas.

Veamos cómo NOT EXISTS y NOT IN se comportan una vez más.

Los planes de ejecución son muy diferentes, se puede ver que el NOT IN tiene mal desempeño.

Los IOs muestran un rendimiento muy malo con el NOT IN. Tenga en cuenta que incluso con este mal rendimiento seguimos recuperando el mismo número de filas.

¿Qué pasa si insertamos un valor NULL?

Insertemos un valor NULL.

El plan no cambió, aun podemos ver el mismo mal desempeño para el NOT IN.

Los IOs también son exactamente iguales... Espera, ¿qué pasó aquí?

El NOT IN no devolvió filas !!! ¿Cómo es esto posible? La respuesta es muy simple, por favor ver más abajo.

IN se convierte en OR (=).

NOT IN se convierte AND (<>).

Por otro lado, NOT EXISTS funciona bien ya que solo verifica si existe la fila o no (Verdadero o Falso)

Regla de oro.

• Si va a utilizar una subconsulta, debe utilizar EXISTS / NOT EXISTS.

• Si va a utilizar una lista de valores literales, debe utilizar IN / NOT IN.

¡Diviértanse aprendiendo!