las tablas temporales de SQL Server son un tipo especial de tablas que se escriben en la base de datos TempDB y actúan como tablas regulares, proporcionando un lugar de trabajo adecuado para el procesamiento de datos intermedios antes de guardar el resultado en una tabla regular, ya que solo puede vivir durante la edad de conexión de la base de datos.,
Las tablas temporales de SQL se pueden utilizar para mejorar el rendimiento de los procedimientos almacenados acortando el tiempo de transacción, lo que le permite preparar registros que modificará en la tabla temporal de SQL Server, luego abrir una transacción y realizar los cambios.
Hay cuatro tipos principales para las tablas temporales de SQL:
-
una tabla temporal de SQL Server Local, que se nombra a partir de un símbolo # (por ejemplo, #TempShipments), que solo puede ser referenciada por la sesión actual de la base de datos y descartada por su desconexión,
-
una tabla temporal de SQL Global, que se nombra a partir de ## (por ejemplo,, ## TempShipments), que puede ser referenciada por cualquier proceso en la base de datos actual y descartada cuando la sesión original de la base de datos que creó esa tabla temporal se desconecta o hasta que la última instrucción que hacía referencia a la tabla temporal ha dejado de usarla, ya que cualquier persona que tenga acceso a la base de datos TempDB del sistema cuando se crea esa tabla temporal SQL global podrá usar esa tabla,
-
una tabla temporal de SQL Server persistente que se nombra comenzando con un prefijo TempDB como TempDB.DBO.TempShipments
-
y una Variable de tabla que comienza con un prefijo @ (p. ej., @TempShipments)
El motor de base de datos de SQL Server puede distinguir entre las mismas tablas temporales de SQL creadas mientras se ejecuta el mismo procedimiento almacenado muchas veces simultáneamente añadiendo un sufijo numérico generado por el sistema al nombre de la tabla temporal de SQL Server. Esta es la razón por la que el nombre de la tabla local SQL temp no puede exceder los 116 caracteres.,
aunque tanto las tablas temporales de SQL Server como las variables de tabla se almacenan en la base de datos TempDB, hay muchas diferencias entre ellas, como:
-
Las tablas temporales de SQL se crean mediante la instrucción CREATE TABLE T-SQL, pero las variables de tabla se crean mediante la instrucción DECLARE @name Table T-SQL.
-
Puede modificar las tablas temporales de SQL Server después de crearlas, pero las variables de tabla no admiten ninguna instrucción DDL como la instrucción ALTER.
-
Las tablas temporales SQL no se pueden usar en funciones definidas por el usuario, pero las variables de tabla sí.,
-
Las tablas temporales de SQL Server admiten transacciones explícitas definidas por el usuario, pero las variables de tabla no pueden participar en dichas transacciones.
-
Las tablas temporales de SQL admiten la adición de índices agrupados y no agrupados después de la creación de la tabla temporal de SQL Server e implícitamente mediante la definición de restricción de clave primaria o restricción de Clave Única durante la creación de tablas, pero las variables de tabla solo admiten la adición de dichos índices implícitamente mediante la definición de restricción de clave primaria o restricción de Clave Única durante la creación de tablas.,
-
Las tablas temporales SQL se pueden eliminar explícitamente, pero las variables de tabla no se pueden eliminar explícitamente, teniendo en cuenta que ambos tipos se eliminan automáticamente cuando se desconecta la sesión en la que se crean.
-
Las tablas temporales de SQL Server pueden ser tablas temporales locales a nivel del lote o procedimiento almacenado en el que la tabla declarada o tablas temporales globales donde se puede llamar fuera del ámbito del lote o procedimiento almacenado, pero las variables de tabla solo se pueden llamar dentro del lote o procedimiento almacenado en el que se declara.,
Además de eso, las estadísticas de nivel de columna de SQL Server se generan automáticamente contra las tablas temporales de SQL, ayudando al optimizador de consultas de SQL Server a generar el mejor plan de ejecución, obteniendo el mejor rendimiento al consultar esa tabla temporal de SQL Server. Pero debe tener en cuenta que modificar las tablas temporales SQL muchas veces en su código puede llevar a que las estadísticas se desactualicen. Esto requeriría actualizar manualmente estas estadísticas, o habilitar el indicador de seguimiento 2371., En este artículo, veremos cómo podemos beneficiarnos de la capacidad de agregar índices agrupados y no agrupados en las tablas temporales de SQL Server.
definir restricciones de clave primaria y clave única durante la creación de tablas temporales de SQL permitirá que SQL Server Query Optimizer siempre pueda usar estos índices. Aun así, estos índices evitan insertar valores no únicos en estas columnas, lo que no es el mejor caso en todos los escenarios, que pueden requerir valores no únicos. En este caso, es mejor definir explícitamente un índice agrupado o no agrupado que podría configurarse como un índice No único., Agregar índices a las tablas temporales de SQL mejorará su rendimiento si el índice se elige correctamente, de lo contrario, puede causar una degradación del rendimiento. Además, no todas las tablas temporales de SQL Server necesitan agregar índices, ya que depende de muchas cosas, como la forma en que se llamará a esta tabla temporal de SQL, se unirá a otras tablas enormes o si formará parte de un procedimiento almacenado complejo.,
comencemos nuestra demo, en la que probaremos el rendimiento de rellenar y recuperar datos de tablas temporales de SQL Server que contienen registros de 100k, sin ningún índice, con un índice no agrupado y con un índice agrupado. Nos concentraremos en Comprobar el tiempo consumido por cada caso y el plan de ejecución generado. Para evaluar el tiempo consumido, declararemos la variable @ StartTime antes de cada ejecución, estableceremos su valor en GETDATE () y al final de cada ejecución, imprimiremos la diferencia de fecha (en ms) entre la hora actual y la hora de inicio.,
el siguiente script creará las tres tablas temporales SQL mencionadas anteriormente; tabla temporal sin índice, tabla temporal con índice no agrupado y tabla temporal con índice agrupado y la llenará con registros 100k de la tabla de prueba CountryInfo luego recuperará estos registros de las tablas:
ejecutando el script anterior, el resultado nos mostrará que en nuestro caso, agregar un índice no agrupado es peor que tener la tabla sin índice por 1.,E tiempo en nuestro caso como en la siguiente comparación de tiempo en ms:
comprobando el plan de ejecución generado usando la aplicación ApexSQL Plan después de la ejecución, veremos que, como no tenemos joins con tablas enormes o consultas complejas, la recuperación de datos de las tres tablas consume los mismos recursos (1%) y difiere en el operador que se usa para recuperar los datos; escaneo de tabla en el caso de la tabla temporal sin índice, Index Seek en el caso de la tabla temporal con índice no agrupado y clustered index seek en el caso de la tabla temporal con índice agrupado.,
también puede derivar del plan de ejecución, que la tabla con índice no agrupado tomó el mayor tiempo (1063 ms) y los recursos (47% de la ejecución total) durante el proceso de inserción de la tabla opuesta a la tabla con índice agrupado inserción que tomó menos tiempo (827 ms) y los recursos (32% de la ejecución total):
en el script anterior que creamos un índice no agrupado después de llenar la tabla temporal y el índice agrupado antes de llenar la tabla temporal., ¿Pero es diferente Cuando creamos el índice antes o después de llenar la tabla temporal?, Para responder a esta pregunta vamos a realizar la siguiente prueba en la que comprobaremos el tiempo consumido en todos los casos; agregar un índice no agrupado antes de llenar la tabla temporal, agregar un índice no agrupado después de llenar la tabla temporal, agregar un índice agrupado antes de llenar la tabla temporal y agregar un índice agrupado después de llenar la tabla temporal:
del resultado generado al ejecutar el script anterior se desprende claramente que es mejor crear el índice no agrupado después de llenar la tabla, ya que es un 1,2% más rápido, y crear el índice agrupado antes de llenar la tabla, ya que es 2.,5% más rápido, debido al mecanismo que se utiliza para llenar las tablas y crear los índices:
comprobando el plan de ejecución, el resultado nos mostrará que crear el índice agrupado antes de la inserción consume el 15,7% de la ejecución total, donde crearlo después de la inserción consumirá el 22% de la ejecución total., Por otro lado, la creación de los índices no agrupados después de la inserción consume el 23% de los recursos en comparación con el 25% que se consume al crearlos antes del proceso de inserción:
conclusión
en SQL Server, las tablas temp de SQL, que se almacenan en la base de datos TempDB, son ampliamente utilizadas para proporcionar un lugar adecuado para el procesamiento de datos intermedios antes de guardar el resultado en una tabla base., También se utiliza para acortar la duración de las transacciones de larga duración con el bloqueo de la tabla base mínima al tomar los datos, procesarlos y, finalmente, abrir una transacción para realizar el cambio en la tabla base. Este enfoque es aplicable para agregar índices no agrupados y agrupados a las tablas temporales de SQL Server, que pueden mejorar el rendimiento de la recuperación de datos de estas tablas si los índices se eligen correctamente.,
- Author
- Recent Posts
He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.,
Además, está contribuyendo con sus consejos SQL en muchos blogs.
Ver todos los mensajes por Ahmad Yasin
- como monitor de Azure Datos de Fábrica – 15 de enero de 2021
- Usar Fuente de Control en Azure Datos de Fábrica – 12 de enero, 2021
- el Uso de Plantillas en Azure Datos de Fábrica – 8 de enero de 2021