Podemos usar SELECT…INTO en SQL Server para crear una nueva tabla desde una fuente de tabla. SQL Server usa los atributos de las expresiones en la lista SELECT para definir la estructura de la nueva tabla.

Antes de SQL Server 2005, el uso de SELECT…INTO en el código de producción era un ‘olor a código’ de rendimiento porque adquiría bloqueos de esquema en las tablas del sistema de la base de datos, haciendo que SQL Server pareciera no responder mientras se ejecutaba la consulta. Esto se debe a que es una declaración DDL en una transacción implícita, que inevitablemente es de larga duración ya que los datos se insertan dentro de la misma declaración SQL. Sin embargo, este comportamiento se corrigió en SQL Server 2005, cuando cambió el modelo de bloqueo.

SELECT…INTO se hizo popular porque era una forma más rápida de insertar datos que usar INSERT INTO…SELECT…. Esto se debió principalmente a que la operación SELECT…INTO se registró de forma masiva, cuando fue posible. Aunque INSERT INTO ahora se puede registrar de forma masiva, aún puede ver esta ventaja de rendimiento en SQL Server 2012 y 2014, porque SELECT…INTO se puede paralelizar en estas versiones, mientras que la compatibilidad con la paralelización de INSERT INTO solo surgió en SQL Server 2016. Sin embargo, con SELECT…INTO, todavía tiene la tarea de definir todos los índices y restricciones requeridos, y así sucesivamente, en la nueva tabla.

Una recomendación para evitar el uso de SELECT…INTO, para el código de producción, se incluye como una regla de análisis de código en el símbolo del sistema SQL (PE003).

Creación de tablas utilizando la instrucción SELECT INTO

La función SELECT…INTO en SQL Server fue diseñada para almacenar o ‘conservar’ una fuente de tabla como parte de un proceso. Aquí hay un ejemplo simple:

Sin embargo, una fuente de tabla puede ser muchas cosas distintas a una tabla convencional, como una función definida por el usuario, una OpenQuery, una OpenDataSource, una OPENXML cláusula, una tabla derivada, una tabla unida, una tabla dinámica, una fuente de datos remota, una variable de tabla o una función variable. Es con estas fuentes de tabla más exóticas que la sintaxis SELECT…INTO se vuelve más útil.

La gente suele utilizar SELECT…INTO con el malentendido de que es una forma rápida de copiar tablas, por lo que sorprende que ninguno de los índices, restricciones, columnas calculadas o activadores definidos en la tabla de origen se transfiere a la nueva tabla. Tampoco se pueden especificar en la instrucción SELECT…INTO. Tampoco hace nada sobre la nulabilidad o la conservación de columnas calculadas. Todas estas tareas deben realizarse retrospectivamente con los datos en su lugar, lo que inevitablemente lleva tiempo.

Sin embargo, puede usar la función IDENTITY (tipo de datos, semilla, incremento) para configurar un campo de identidad, y es posible, cuando la fuente es una sola tabla, convertir una columna de la tabla de destino en una columna de identidad. Es este hecho el que probablemente lleva a los desarrolladores a asumir que transferirá otros atributos de columna.

Además, tampoco puede crear tablas particionadas, columnas dispersas o cualquier otro atributo heredado de una tabla fuente. ¿Cómo es posible que los datos provengan de una consulta que involucre muchas combinaciones o de alguna fuente de datos externa exótica?

Desde SQL 2012 SP1 CU10, SELECT…INTO puede ejecutarse en paralelo. Sin embargo, desde SQL Server 2016, se permite la inserción paralela en la instrucción INSERT INTO…SELECT convencional, con ciertas restricciones, por lo que cualquier ventaja de rendimiento de usar SELECT… INTO ahora está bastante disminuido. El proceso INSERT INTO también se puede acelerar si se puede registrar de forma masiva, en lugar de recuperarlo por completo, mediante la configuración del modelo de recuperación a registro simple o masivo, insertando en una tabla vacía o en un montón y estableciendo la TABLOCK sugerencia para la tabla.

A continuación se resumen algunas de las restricciones y limitaciones al usar SELECT…INTO.

• La propiedad IDENTITY de una columna se transfiere, pero no si:
  • La instrucción SELECT contiene tablas unidas (usando JOIN o UNION), GROUP BY cláusula o función agregada.Si necesita evitar que una propiedad IDENTITY se transfiera a la nueva tabla, pero necesita los valores de la columna, vale la pena agregar una JOIN a su fuente de tabla en una condición que nunca es verdadera, o un UNION que no proporciona filas.
  • El IDENTITY la columna aparece más de una vez en la SELECT lista
  • La IDENTITY forma parte de una expresión
  • La columna IDENTITY es de una fuente de datos remota
• No puede SELECT…INTO ya sea un parámetro con valores de tabla o una variable de tabla como destino, aunque puede seleccionarlos FROM.
• Incluso si su fuente es una tabla particionada, la nueva tabla se crea en el grupo de archivos predeterminado. Sin embargo, en SQL Server 2017, es posible especificar el grupo de archivos en el que se crea la tabla de destino, a través de la cláusula ON.
• Puede especificar una ORDER BY cláusula, pero generalmente se ignora. Debido a esto, el orden de IDENTITY_INSERT no está garantizado.
• Cuando una columna calculada se incluye en el SELECT lista, la columna correspondiente en la nueva tabla no es una columna calculada. Los valores de la nueva columna son los valores que se calcularon en el momento en que se ejecutó SELECT…INTO.
• Al igual que con un CREATE TABLE instrucción, si una instrucción SELECT…INTO está contenida en una transacción explícita, las filas subyacentes en las tablas del sistema afectadas son bloqueado exclusivamente hasta que la transacción se confirme explícitamente. Mientras tanto, esto resultará en bloqueos en otros procesos que usan estas tablas del sistema.

Existe cierta confusión sobre los problemas que pueden ocurrir con el uso de SELECT…INTO usando tablas temporales. SELECT…INTO se ha ganado una reputación algo injusta por esto, pero era parte de un problema más general que involucraba la contención de pestillos en tempdb bajo una carga pesada de creación y eliminación de tablas temporales pequeñas. Cuando SELECT…INTO se adoptó con entusiasmo, podría aumentar enormemente este tipo de actividad. El problema podría solucionarse fácilmente en SQL Server 2000 en adelante con la introducción del uso del indicador de seguimiento TF1118, que ya no es necesario a partir de SQL Server 2016 en adelante. Para obtener una explicación completa, consulte Conceptos erróneos sobre TF 1118.

Resumen

En resumen, SELECT…INTO es una buena forma de hacer una tabla- fuente persistente temporalmente como parte de un proceso, si no le importan las restricciones, índices o columnas especiales. No es una buena forma de copiar una tabla porque solo se pueden copiar los elementos más básicos del esquema de la tabla. A lo largo de los años, ha habido factores que han aumentado o disminuido la atracción de SELECT…INTO, pero en general es una buena idea evitar su uso siempre que sea posible. En su lugar, cree una tabla de forma explícita, con la gama completa de características que posee la tabla que están diseñadas para garantizar que los datos sean coherentes.