¿Cuándo debo usar una variable de tabla frente a una tabla temporal en el servidor SQL?

Su pregunta muestra que ha sucumbido a algunos de los conceptos erróneos comunes que rodean las variables de tabla y las tablas temporales.

He escrito una respuesta bastante extensa en el sitio de DBA analizando las diferencias entre los dos tipos de objetos. Esto también responde a su pregunta sobre el disco y la memoria (no vi ninguna diferencia significativa en el comportamiento entre los dos).

Sin embargo, con respecto a la pregunta en el título sobre cuándo usar una variable de tabla versus una tabla temporal local, no siempre tienes otra opción. En funciones, por ejemplo, solo es posible usar una variable de tabla y si necesita escribir en la tabla en un ámbito secundario, entonces solo una #temptabla servirá (los parámetros con valores de tabla permiten acceso de solo lectura ).

A continuación se presentan algunas sugerencias sobre dónde tiene opciones (aunque el método más confiable es simplemente probar ambos con su carga de trabajo específica).

1. Si necesita un índice que no se puede crear en una variable de tabla, entonces, por supuesto, necesitará una #temporarytabla. Sin embargo, los detalles de esto dependen de la versión. Para SQL Server 2012 y versiones anteriores, los únicos índices que se podían crear en las variables de la tabla eran los creados implícitamente mediante una restricción UNIQUEo . PRIMARY KEYSQL Server 2014 introdujo la sintaxis de índice en línea para un subconjunto de las opciones disponibles en CREATE INDEX. Esto se ha ampliado desde entonces para permitir condiciones de índice filtradas. INCLUDESin embargo, todavía no es posible crear índices con columnas -d o índices de almacén de columnas en variables de tabla.

2. Si va a agregar y eliminar repetidamente una gran cantidad de filas de la tabla, utilice una #temporarytabla. Eso admite TRUNCATE(lo cual es más eficiente que DELETEpara tablas grandes) y, además, las inserciones posteriores que siguen a TRUNCATEpueden tener un mejor rendimiento que las que siguen a DELETE como se ilustra aquí .

3. Si va a eliminar o actualizar una gran cantidad de filas, entonces la tabla temporal puede funcionar mucho mejor que una variable de tabla, si puede utilizar el uso compartido de conjuntos de filas (consulte "Efectos del uso compartido de conjuntos de filas" a continuación para ver un ejemplo).
4. Si el plan óptimo que utiliza la tabla varía según los datos, utilice una #temporarytabla. Esto admite la creación de estadísticas que permiten que el plan se recopile dinámicamente de acuerdo con los datos (aunque para las tablas temporales almacenadas en caché en procedimientos almacenados, el comportamiento de recompilación debe entenderse por separado).
5. Si es poco probable que el plan óptimo para la consulta que utiliza la tabla cambie alguna vez, entonces puede considerar una variable de tabla para evitar la sobrecarga de la creación y recompilación de estadísticas (posiblemente requeriría sugerencias para arreglar el plan que desea).
6. Si la fuente de los datos insertados en la tabla proviene de una SELECTdeclaración potencialmente costosa, considere que el uso de una variable de tabla bloqueará la posibilidad de que se utilice un plan paralelo.
7. Si necesita que los datos de la tabla sobrevivan a una reversión de una transacción de usuario externo, utilice una variable de tabla. Un posible caso de uso para esto podría ser registrar el progreso de diferentes pasos en un lote SQL largo.
8. Cuando se utiliza una #temptabla dentro de un usuario, los bloqueos de transacciones se pueden mantener por más tiempo que para las variables de la tabla (potencialmente hasta el final de la transacción versus el final de la declaración dependiendo del tipo de bloqueo y el nivel de aislamiento) y también puede evitar el truncamiento del tempdbregistro de transacciones hasta el finaliza la transacción del usuario. Entonces esto podría favorecer el uso de variables de tabla.
9. Dentro de las rutinas almacenadas, se pueden almacenar en caché tanto las variables de tabla como las tablas temporales. El mantenimiento de metadatos para las variables de tablas almacenadas en caché es menor que el de #temporarylas tablas. Bob Ward señala en su tempdbpresentación que esto puede causar contención adicional en las tablas del sistema en condiciones de alta concurrencia. Además, cuando se trata de pequeñas cantidades de datos, esto puede marcar una diferencia mensurable en el rendimiento .

Efectos de compartir conjuntos de filas

DECLARE @T TABLE(id INT PRIMARY KEY, Flag BIT);

CREATE TABLE #T (id INT PRIMARY KEY, Flag BIT);

INSERT INTO @T 
output inserted.* into #T
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY @@SPID), 0
FROM master..spt_values v1, master..spt_values v2

SET STATISTICS TIME ON

/*CPU time = 7016 ms,  elapsed time = 7860 ms.*/
UPDATE @T SET Flag=1;

/*CPU time = 6234 ms,  elapsed time = 7236 ms.*/
DELETE FROM @T

/* CPU time = 828 ms,  elapsed time = 1120 ms.*/
UPDATE #T SET Flag=1;

/*CPU time = 672 ms,  elapsed time = 980 ms.*/
DELETE FROM #T

DROP TABLE #T

Dec 08 '2012 13:12 Martin Smith