Redgate Hub (Español)

Las tablas temporales son solo eso. Se utilizan con mayor frecuencia para proporcionar un espacio de trabajo para los resultados intermedios al procesar datos dentro de un lote o procedimiento. También se utilizan para pasar una tabla desde una función con valores de tabla, para pasar datos basados en tablas entre procedimientos almacenados o, más recientemente en forma de parámetros con valores de tabla, para enviar tablas completas de solo lectura desde aplicaciones a rutinas de SQL Server. o pasar tablas temporales de solo lectura como parámetros. Una vez finalizado su uso, se descartan automáticamente.

Antes de profundizar demasiado en la tecnología, le aconsejo que utilice variables de tabla siempre que sea posible. Son fáciles y SQL Server hace el trabajo por usted. También tienden a causar menos problemas a un sistema OLTP que trabaja duro. Solo ocasionalmente, es posible que deba ajustarlos para obtener un buen rendimiento de ellos en las uniones, pero lo explicaré en un momento, sin embargo, si está realizando un procesamiento más complejo en datos temporales o es probable que use más de lo razonablemente pequeño cantidades de datos en ellos, entonces es probable que las tablas temporales locales sean una mejor opción.

Variables de tabla

Las variables de tabla se usan dentro del alcance de la rutina o lote dentro del cual están definidos, y fueron creados originalmente para hacer posibles las funciones con valores de tabla. Sin embargo, son buenos para muchos de los usos a los que se destinó la mesa temporal tradicional. Se comportan como otras variables en sus reglas de alcance. Una vez fuera de alcance, se eliminan. Son mucho más fáciles de trabajar y bastante seguros, y también activan menos recompilaciones en las rutinas en las que se utilizan que si utilizara tablas temporales. Las variables de tabla requieren menos recursos de bloqueo, ya que son «privadas» para el proceso que las creó. Las reversiones de transacciones no las afectan porque las variables de la tabla tienen un alcance limitado y no forman parte de la base de datos persistente, por lo que son útiles para crear o almacenar datos que deberían sobrevivir a las reversiones, como las entradas de registro. La desventaja de las variables de tabla es que a menudo se eliminan antes de que pueda investigar su contenido para depurarlas, o usarlas para probar diferentes expresiones SQL de forma interactiva.

Si su aplicación es conservadora y sus volúmenes de datos son ligeros Nunca querrás nada más. Sin embargo, puede encontrar problemas. Una dificultad es que solo se puede hacer referencia a las variables de tabla en su ámbito local, por lo que no puede procesarlas utilizando SQL dinámico como lo haría con una tabla temporal o un parámetro con valores de tabla. Esto se debe a que no puede hacer referencia a una variable de tabla definida externamente dentro de SQL dinámico que luego ejecuta a través de la instrucción EXEC o el sp_ExecuteSQL procedimiento almacenado porque el SQL dinámico se ejecuta fuera del alcance. de la variable de la tabla. Por supuesto, puede crear y luego usar la variable de tabla dentro del SQL dinámico porque la variable de tabla estaría dentro del alcance. Sin embargo, una vez que se ejecute el SQL dinámico, no habrá ninguna variable de tabla

También hay algunas anomalías a tener en cuenta. No puede, por ejemplo, cambiar la definición de la tabla después de la instrucción DECLARE inicial. En SQL Server 2000, una variable de tabla no puede ser el destino de una instrucción SELECT INTO o una INSERT EXEC (ahora corregida); No puede llamar a funciones definidas por el usuario desde CHECK restricciones, valores DEFAULT y columnas calculadas en la variable de tabla. Las únicas restricciones que se le permiten más allá de las restricciones CHECK son PRIMARY KEY, UNIQUE KEY y NULL / NOT NULL

Sin embargo, los problemas más complicados vienen con el aumento del tamaño de las tablas, porque, antes de SQL Server 2016 , no se podía declarar un índice explícitamente, y los índices que aplicaban las restricciones UNIQUE y PRIMARY KEY no tenían índices de distribución mantenidos. Ahora puede crear ciertos tipos de índices en línea con la definición de la tabla, pero las estadísticas de distribución aún no se mantienen en ellos. El Optimizador de consultas asume que solo hay una fila en la tabla. Tampoco puede generar planes de consulta paralelos para una expresión SQL que está modificando el contenido de la tabla. Para sortear parcialmente la restricción del índice, puede usar restricciones para hacer lo mismo. Lo más esencial es la restricción de clave principal que le permite imponer un índice agrupado, pero las restricciones únicas son útiles para el rendimiento. El Optimizador de consultas las utilizará con gusto si están presentes.

El mayor problema con las variables de tabla es que las estadísticas no se mantienen en las columnas. Esto significa que el optimizador de consultas tiene que adivinar el tamaño y la distribución de los datos y, si se equivoca, verá un rendimiento deficiente en las uniones: si esto sucede, es poco lo que puede hacer en otros que volver a utilizar tablas temporales locales clásicas. A partir de SQL Server 2019, Microsoft introdujo una nueva función llamada Compilación diferida de variables de tabla que resuelve este problema. Para obtener más información, lea este artículo de Greg Larsen.

Si no está utilizando SQL Server 2019, una cosa que puede intentar es agregar OPTION (RECOMPILE) a la declaración que involucra la unión de la variable de tabla con otras tablas. . Al hacer esto, SQL Server podrá detectar el número de filas en la recompilación porque las filas ya se habrán llenado. Esto no resuelve por completo el problema, ya que el optimizador aún no tiene estadísticas de distribución y, por lo general, cuando la distribución está sesgada, puede producir un plan incorrecto. En esta demostración, la combinación se redujo en el tiempo en tres cuartas partes simplemente agregando la OPCIÓN (RECOMPILAR)

Ahora, si puede hacer que lo que entra en las tablas sea único, puede usar una restricción de clave primaria en estas mesas. Esto permitió que el optimizador usara una búsqueda de índice agrupado en lugar de un escaneo de tabla y el tiempo de ejecución fue demasiado rápido para medirlo.

Comience con variables de tabla, pero vuelva a usar tablas temporales locales si encuentra problemas de rendimiento. Algunas personas son lo suficientemente atrevidas como para dar consejos en términos del número de filas en una tabla, y he visto que se ofrecen 100 o 1000 como máximo; pero he visto que las variables de tabla mucho más grandes funcionan perfectamente satisfactoriamente con el tiempo, y las mucho más pequeñas dan problemas. Sin embargo, en tablas más pequeñas, el problema es menos detectable.

Parámetros con valores de tabla

El parámetro con valores de tabla (TVP) es un tipo especial de variable de tabla que amplía su uso. Cuando las variables de tabla se pasan como parámetros, la tabla se materializa en la base de datos del sistema TempDB como una variable de tabla y se pasa por referencia, un puntero a la tabla en TempDB.

Desde entonces se han utilizado parámetros con valores de tabla SQL Server 2008 para enviar varias filas de datos a una rutina Transact-SQL o a un lote a través de sp_ExecuteSQL. Su valor particular para el programador es que se pueden usar dentro del código TSQL como así como en la aplicación del cliente, por lo que son buenos para enviar tablas de cliente al servidor. Desde TSQL, puede declarar variables con valores de tabla, insertar datos en ellas y pasar estas variables como parámetros con valores de tabla a funciones y procedimientos almacenados. Su utilidad más general está limitada por el hecho de que solo se pasan como de solo lectura. No puede hacer declaraciones UPDATE, DELETE o INSERT en una tabla con valores en el cuerpo de una rutina.

Necesita crear un tipo de tabla definido por el usuario y definir una estructura de tabla para usarlos. Aquí hay un ejemplo simple de su uso en TSQL

Al igual que con las variables de tabla, el parámetro con valores de tabla deja de existir una vez que está fuera del alcance, pero la definición de tipo permanece hasta que se elimina explícitamente.Al igual que las variables de tabla, no adquieren bloqueos cuando los datos se completan desde un cliente y las estadísticas no se mantienen en columnas de parámetros con valores de tabla. No puede utilizar un parámetro con valores de tabla como destino de una instrucción SELECT INTO o INSERT EXEC. Como era de esperar, un parámetro con valores de tabla puede estar en la cláusula FROM de SELECT INTO o en la INSERT EXEC cadena o procedimiento almacenado.

El TVP resuelve el problema común de querer pasar una variable local a SQL dinámico que luego es ejecutado por un sp_ExecuteSQL. Está mal documentado por Microsoft, así que te mostraré un ejemplo trabajado para que comiences

Antes de pasar a describir las tablas temporales más tradicionales y su uso, necesitaremos profundizar en el lugar donde se llevan a cabo las mesas temporales. TempDB.

TempDB

Las tablas temporales y las variables de tabla se crean en la base de datos TempDB, que en realidad es solo otra base de datos con recuperación simple: con TempDB, solo se realiza un registro mínimo suficiente para permitir la reversión y otras sutilezas ACID. La diferencia especial de TempDB es que cualquier objeto, como tablas, se borra durante el inicio. Debido a que TempDB siempre usa el modelo de recuperación simple, las transacciones completadas se borran del registro de registro en el siguiente punto de control de TempDB y solo se conservan las transacciones en vivo. Todo esto significa que las tablas temporales se comportan como cualquier otro tipo de tabla base en el sentido de que se registran y almacenan como ellos. En la práctica, es probable que las tablas temporales permanezcan almacenadas en la memoria caché, pero solo si se usan con frecuencia: lo mismo que con una tabla base. TempDB opera un sistema llamado reutilización de objetos temporales, que almacenará en caché una parte de los objetos temporales con el plan, si hay suficiente memoria. Esto puede explicar la leyenda de que los objetos temporales solo existen en la memoria. La verdad como siempre es depende ….

Muchas otras cosas suceden en TempDB: el motor de la base de datos puede usarlo para colocar tablas de trabajo para comprobaciones DBCC, para crear o reconstruir índices, cursores, por ejemplo. Las tablas intermedias en las consultas descritas como «hash», «ordenaciones» y «spools» se materializan en TempDB, por ejemplo, junto con las necesarias para varias operaciones «físicas» en la ejecución de sentencias SQL. También se utiliza como almacén de versiones para aislamiento de instantáneas, conjuntos de resultados activos múltiples (MARS), disparadores y creación de índices en línea.

Debido a que las tablas temporales se almacenan como tablas base, hay una o dos cosas de las que debes tener cuidado. Debe, por ejemplo, tener el permiso CREATE TABLE en TempDB para crear una tabla normal. Para evitarle problemas, esto se asigna de forma predeterminada a la función DBO (propietario de la base de datos), pero es posible que deba hacerlo explícitamente para los usuarios a los que no se les asignó la función DBO. Todos los usuarios tienen permisos para crear tablas temporales locales o globales en TempDB porque esto se les asigna a través del GUEST contexto de seguridad del usuario.

La tabla temporal clásica viene en dos sabores, la tabla temporal global o compartible, con el prefijo ##, y la tabla temporal local, cuyo nombre tiene el prefijo #. Las tablas temporales locales se parecen menos a las tablas normales que las tablas temporales globales: no puede crear vistas sobre ellos ni asociar activadores con ellos. Es un poco complicado determinar qué proceso, sesión o procedimiento los creó. Te ayudaremos un poco con eso más tarde. Lo más importante es que son más seguras que una tabla temporal global, ya que solo el proceso propietario puede verla.

Otra rareza de la tabla temporal local (y el procedimiento almacenado temporal local) es que tiene un nombre diferente en los metadatos al que le da en su rutina o lote. Si la misma rutina es ejecutada simultáneamente por varios procesos, el Motor de base de datos debe poder distinguir entre las tablas temporales locales con nombres idénticos creadas por los diferentes procesos. Para ello, agrega una cadena numérica a cada nombre de tabla temporal local, rellenado a la izquierda con caracteres de subrayado. Aunque especifique el nombre corto como #MyTempTable, lo que realmente se almacena en TempDB se compone del nombre de tabla especificado en la instrucción CREATE TABLE y el sufijo. Debido a este sufijo, los nombres de las tablas temporales locales deben tener 116 caracteres o menos.

Si está interesado en ver lo que está sucediendo, puede ver las tablas en TempDB de la misma manera que lo haría con cualquier otro mesa. Incluso puede usar sp_help trabajar en tablas temporales solo si las invoca desde TempDB.

o puede encontrarlos en las vistas del sistema de TempDB sin cambiar las bases de datos.

O el esquema de información

Aún mejor, puede averigüe qué proceso y qué usuario se aferra a enormes tablas temporales en TempDB y se niega a ceder el espacio

No puede usar tipos de datos definidos por el usuario en tablas temporales a menos que los tipos de datos existan en TempDB; es decir, a menos que los tipos de datos se hayan creado explícitamente

Tablas de usuario en TempDB

En uso normal, creará tablas temporales o variables de tabla sin pensar demasiado en ello. Sin embargo, es interesante que TempDB esté ahí para cualquier tipo de actividad de sandbox. Puede crear tablas base, vistas o cualquier otra cosa que desee. Puede crear esquemas, procedimientos almacenados, etc. Es poco probable que desee hacer esto, pero ciertamente es posible ya que TempDB es solo otra base de datos. Tuve que reiniciar mi SQL Server de desarrollo después de probarme esto instalando AdventureWorks en él. Esto significa que es posible crear una tabla base en TempDB, una especie de ..er … tabla permanente temporal. A diferencia de la mesa temporal global, tendrías que hacer todas tus tareas domésticas en ella: estás solo. Lo mismo ocurre con las rutinas. La ventaja de hacer esto es que cualquier procesamiento que realice utiliza la recuperación simple de TempDB, de modo que, si no puede limpiar, SQL Server actúa como madre en el próximo inicio: aunque esto podría llevar mucho tiempo. La siguiente etapa es tener lo que yo llamo una tabla «temporal persistente». En esta tabla, los datos en sí son volátiles cuando el servidor se reinicia, pero la tabla en sí persiste. Probablemente, la forma más común de crear una tabla temporal persistente es recrear al inicio una tabla temporal global. Esto se puede hacer automáticamente cuando se recuperan todas las bases de datos y se registra el mensaje «Recuperación completada». Aunque se trata de un «temporal global», no se elimina cuando todas las conexiones que lo utilizan han desaparecido, porque el proceso que lo ejecuta nunca desaparece. Podría decirse que es mejor crear este tipo de tabla de trabajo en la base de datos que la usa, aunque, si está utilizando la recuperación completa, el trabajo temporal permanecerá en el registro. Por supuesto, puede crear un archivo ordinario tabla en TempDB. Puede crear estas tablas persistentes en el inicio definiendo un procedimiento almacenado en el maestro que crea la tabla temporal global

¿Por qué usar este tipo de tabla híbrida? Hay, por ejemplo, un número de técnicas para pasar tablas entre procedimientos a través de tablas persistentes de una manera multiproceso segura, a fin de realizar una serie de procesamiento de los datos. Estos se refieren a tablas con clave de proceso (consulte Cómo compartir datos entre procedimientos almacenados : Mesa Process-Keyed de Erland Sommarskog ). Inicialmente sorprenderán a cualquier DBA experimentado, pero son una solución eficaz y segura para un problema perenne, cuando se hacen correctamente.

Además de las tablas temporales, también hay varios tipos de tablas. que no se derivan directamente de tablas base, como tablas «falsas» y tablas derivadas: algunas de ellas son tan fugaces que es mejor considerarlas efímeras en lugar de temporales. El CTE utiliza tablas efímeras que están «en línea» o «derivadas» y no se materializan. BOL se refiere a ellos como «conjuntos de resultados con nombre temporal». Existen solo dentro del alcance de la expresión. En un CTE, tienen la ventaja sobre las tablas derivadas en que se puede acceder a ellas más de una vez.

Tabla temporal local

Con la tabla temporal local (nombres que comienzan con #), lo que sucede debajo del capó es sorprendentemente similar a las variables de la tabla. Al igual que con las variables de tabla, las tablas temporales locales son privadas para el proceso que las creó. Por lo tanto, no se pueden utilizar en vistas y no se pueden asociar activadores con ellos.

Son más útiles que las variables de tabla si le gusta usar SELECT INTO para crearlas, pero soy un poco cauteloso acerca de usar SELECT INTO en un sistema que probablemente requiera modificación, prefiero crear mis tablas temporales explícitamente, junto con todas las restricciones que se necesitan.

No se puede saber fácilmente qué sesión o procedimiento se ha creado estas tablas. Esto se debe a que, si el mismo procedimiento almacenado es ejecutado simultáneamente por varios procesos, el Motor de base de datos necesita poder distinguir las mismas tablas creadas por los diferentes procesos. El motor de base de datos hace esto agregando internamente un sufijo numérico relleno a la izquierda a cada nombre de tabla temporal local. El nombre completo de una tabla temporal almacenada en la vista sys.objects en TempDB se compone del nombre de la tabla especificado en la instrucción CREATE TABLE y el sufijo numérico generado por el sistema. Para permitir el sufijo, el nombre de la tabla especificado para un nombre temporal local debe tener menos de 116 caracteres.

Obtienes un orden interno con las tablas temporales locales; se eliminan automáticamente cuando salen del alcance, a menos que se eliminen explícitamente mediante DROP TABLE. Su alcance es más generoso que el de una variable de tabla, por lo que no tendrá problemas para hacer referencia a ellos dentro de lotes o en SQL dinámico. Las tablas temporales locales se eliminan automáticamente al final de la sesión o procedimiento actual. Dejarlo caer al final del procedimiento que lo creó puede causar problemas: una tabla temporal local que se crea dentro de un procedimiento almacenado o una sesión se quita cuando finaliza, por lo que el proceso que llamó al procedimiento almacenado no puede hacer referencia a ella. creó la tabla. Sin embargo, puede ser referenciado por cualquier procedimiento almacenado anidado ejecutado por el procedimiento almacenado que creó la tabla. Si el procedimiento anidado hace referencia a una tabla temporal y existen dos tablas temporales con el mismo nombre en ese momento, ¿contra qué tabla se resuelve la consulta?

Como curiosidad, también puede crear procedimientos almacenados temporales locales con el mismo alcance y duración que una tabla temporal local. No puede hacer lo mismo con otras rutinas.

Tablas temporales globales.

Al igual que las tablas temporales locales, las tablas temporales globales (comienzan con ##) se eliminan automáticamente cuando finaliza la sesión que creó la tabla: Sin embargo, debido a que las tablas globales no son privados para el proceso que lo creó, deben persistir a partir de entonces hasta que la última instrucción Transact-SQL que hacía referencia activa a la tabla en el momento en que finalizó la sesión de creación haya terminado de ejecutarse y los bloqueos se hayan eliminado. Cualquiera que tenga acceso a TempDB en el momento en que existen estas tablas temporales globales puede consultar, modificar o eliminar directamente estos objetos temporales.

Puede asociar reglas, valores predeterminados e índices con tablas temporales, pero no puede crear vistas en tablas temporales o desencadenadores asociados con ellos. Puede utilizar un tipo de datos definido por el usuario al crear una tabla temporal solo si el tipo de datos existe en TempDB

Los procedimientos almacenados pueden hacer referencia a tablas temporales que se crean durante la sesión actual. Dentro de un procedimiento almacenado, no puede crear una tabla temporal, soltarla y luego crear una nueva tabla temporal con el mismo nombre.

Aunque esto funciona….

… esto no t

Puede, utilizando tablas temporales locales, forzar involuntariamente la recompilación en el procedimiento almacenado cada vez que se utilice. Esto no es bueno porque es poco probable que el procedimiento almacenado funcione bien. Para evitar la recompilación, evite hacer referencia a una tabla temporal creada en una llamada o un procedimiento almacenado llamado: si no puede hacerlo, coloque la referencia en una cadena que luego se ejecutará usando EXECUTE instrucción o sp_ExecuteSQL procedimiento almacenado. Además, asegúrese de que la tabla temporal se crea en el procedimiento almacenado o desencadenador antes de que se haga referencia a ella y se elimine después de estas referencias.No cree una tabla temporal dentro de una declaración de control de flujo como IF... ELSE o WHILE.

Puede crear procedimientos almacenados temporales globales, pero todavía no he encontrado un uso para ellos. Las funciones temporales globales no están permitidas.

Conclusiones

En cualquier área de juegos compartida, tenga mucho cuidado con la forma en que balancea ese bate. Mientras lee esto, se habrá dado cuenta de que hay mucha actividad en TempDB, y puede causar estragos en todo el SQL Server al usar procesos de larga ejecución que llenan tablas temporales, del tipo que sean, con cantidades innecesarias de datos. De hecho, le he dado pistas en este artículo sobre cómo realmente, realmente, alterar su DBA mediante el uso desconsiderado de ese valioso recurso compartido, el TempDB. (En los viejos tiempos antes de S2005, usar SELECT INTO con una mesa enorme era la gran arma en V (Vergeltungswaffe)

Siempre desconfío de proporcionar más consejos generalizados, pero siempre prefiero que mis bases de datos usen variables de tabla y TVP siempre que sea posible. Requieren menos recursos y es menos probable que los conserve cuando haya terminado con ellos. Me gusta usarlos al máximo , con verificaciones y restricciones de columnas y tablas. Es posible que encuentres momentos en los que se agotan, especialmente cuando el tamaño de las tablas aumenta. En casos como este, o donde no es práctico usar variables de tabla debido a su alcance restringido, Usaré tablas temporales locales. Se necesitan muchos labios fruncidos y sacudir la cabeza antes de aceptar una tabla temporal global o una tabla temporal persistente. Tienen algunos usos válidos y perfectamente razonables, pero confían en el programador para hacer las tareas de limpieza necesarias