¿Cuál es la diferencia entre una tabla temporal y una variable de tabla en SQL Server?

En SQL Server 2005, podemos crear tablas temporales de una de dos maneras:

declare @tmp table (Col1 int, Col2 int); 

o

 create table #tmp (Col1 int, Col2 int); 

¿Cuáles son las diferencias entre estos dos? He leído opiniones contradictorias sobre si @tmp todavía usa tempdb, o si todo sucede en la memoria.

¿En qué escenarios uno supera al otro?

Existen algunas diferencias entre Tablas temporales (#tmp) y Variables de tabla (@tmp), aunque el uso de tempdb no es una de ellas, como se detalla en el enlace de MSDN a continuación.

Como regla general, para volúmenes de datos pequeños y medianos y escenarios de uso simples, debe usar variables de tabla. (Esta es una directriz demasiado amplia con, por supuesto, muchas excepciones; consulte los artículos siguientes y siguientes).

Algunos puntos a considerar al elegir entre ellos:

  • Las tablas temporales son tablas reales, por lo que puede hacer cosas como CREAR INDEXes, etc. Si tiene grandes cantidades de datos para los que el acceso por índice será más rápido, las tablas temporales son una buena opción.

  • Las variables de tabla pueden tener índices mediante el uso de restricciones PRIMARY KEY o UNIQUE. (Si desea un índice no exclusivo, simplemente incluya la columna de la clave principal como la última columna en la restricción única. Si no tiene una columna única, puede usar una columna de identidad.) SQL 2014 también tiene índices no únicos. .

  • Las variables de tabla no participan en transacciones y SELECT s están implícitamente con NOLOCK . El comportamiento de la transacción puede ser muy útil, por ejemplo, si desea ROLLBACK a mitad de un procedimiento, las variables de tabla pobladas durante esa transacción aún se completarán.

  • Las tablas temporales pueden dar como resultado la recomstackción de procedimientos almacenados, tal vez con frecuencia. Las variables de tabla no.

  • Puede crear una tabla temporal usando SELECT INTO, que puede ser más rápido de escribir (bueno para consultas ad-hoc) y puede permitirle tratar con el cambio de tipos de datos a lo largo del tiempo, ya que no necesita definir la estructura de la tabla temporal por adelantado.

  • Puede volver a pasar variables de tabla desde funciones, lo que le permite encapsular y reutilizar la lógica mucho más fácilmente (por ejemplo, crear una función para dividir una cadena en una tabla de valores en algún delimitador arbitrario).

  • El uso de Variables de tabla dentro de las funciones definidas por el usuario permite que esas funciones se utilicen más ampliamente (vea la documentación CREAR FUNCIÓN para más detalles). Si está escribiendo una función, debe usar variables de tabla sobre tablas temporales, a menos que haya una necesidad imperiosa de lo contrario.

  • Ambas variables de tabla y tablas temporales se almacenan en tempdb. Pero las variables de la tabla (desde 2005) se basan de forma predeterminada en la intercalación de la base de datos actual en comparación con las tablas temporales que toman la intercalación predeterminada de tempdb ( ref ). Esto significa que debe tener en cuenta los problemas de intercalación si usa tablas temporales y su intercalación de bases de datos es diferente a la de tempdb, causando problemas si desea comparar datos en la tabla temporal con datos en su base de datos.

  • Global Temp Tables (## tmp) es otro tipo de tabla temporal disponible para todas las sesiones y usuarios.

Algunas lecturas adicionales:

Solo mirando el reclamo en la respuesta aceptada, las variables de tabla no participan en el registro.

Por lo general, no es cierto que exista una diferencia en la cantidad de registros (al menos para las operaciones de insert / update / delete , aunque desde entonces he descubierto que existe una pequeña diferencia en este sentido para los objetos temporales almacenados en memoria caché en procedimientos almacenados debido a actualizaciones adicionales de la tabla del sistema).

Miré el comportamiento de registro tanto en una @table_variable como en una tabla #temp para las siguientes operaciones.

  1. Inserción exitosa
  2. Insertar fila múltiple donde la instrucción se retrotrae debido a una violación de restricción.
  3. Actualizar
  4. Borrar
  5. Desasignar

Los registros del registro de transacciones fueron casi idénticos para todas las operaciones.

La versión variable de tabla realmente tiene algunas entradas de registro adicionales porque obtiene una entrada agregada (y luego eliminada de) la tabla base sys.syssingleobjrefs , pero en general tuvo unos pocos bytes menos registrados simplemente porque el nombre interno de las variables de tabla consume 236 bytes menos que para #temp tablas #temp (118 caracteres nvarchar menos).

Script completo para reproducir (se ejecuta mejor en una instancia iniciada en modo de usuario único y usando el modo sqlcmd )

 :setvar tablename "@T" :setvar tablescript "DECLARE @T TABLE" /* --Uncomment this section to test a #temp table :setvar tablename "#T" :setvar tablescript "CREATE TABLE #T" */ USE tempdb GO CHECKPOINT DECLARE @LSN NVARCHAR(25) SELECT @LSN = MAX([Current LSN]) FROM fn_dblog(null, null) EXEC(N'BEGIN TRAN StartBatch SAVE TRAN StartBatch COMMIT $(tablescript) ( [4CA996AC-C7E1-48B5-B48A-E721E7A435F0] INT PRIMARY KEY DEFAULT 0, InRowFiller char(7000) DEFAULT ''A'', OffRowFiller varchar(8000) DEFAULT REPLICATE(''B'',8000), LOBFiller varchar(max) DEFAULT REPLICATE(cast(''C'' as varchar(max)),10000) ) BEGIN TRAN InsertFirstRow SAVE TRAN InsertFirstRow COMMIT INSERT INTO $(tablename) DEFAULT VALUES BEGIN TRAN Insert9Rows SAVE TRAN Insert9Rows COMMIT INSERT INTO $(tablename) ([4CA996AC-C7E1-48B5-B48A-E721E7A435F0]) SELECT TOP 9 ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM sys.all_columns BEGIN TRAN InsertFailure SAVE TRAN InsertFailure COMMIT /*Try and Insert 10 rows, the 10th one will cause a constraint violation*/ BEGIN TRY INSERT INTO $(tablename) ([4CA996AC-C7E1-48B5-B48A-E721E7A435F0]) SELECT TOP (10) (10 + ROW_NUMBER() OVER (ORDER BY (SELECT 0))) % 20 FROM sys.all_columns END TRY BEGIN CATCH PRINT ERROR_MESSAGE() END CATCH BEGIN TRAN Update10Rows SAVE TRAN Update10Rows COMMIT UPDATE $(tablename) SET InRowFiller = LOWER(InRowFiller), OffRowFiller =LOWER(OffRowFiller), LOBFiller =LOWER(LOBFiller) BEGIN TRAN Delete10Rows SAVE TRAN Delete10Rows COMMIT DELETE FROM $(tablename) BEGIN TRAN AfterDelete SAVE TRAN AfterDelete COMMIT BEGIN TRAN EndBatch SAVE TRAN EndBatch COMMIT') DECLARE @LSN_HEX NVARCHAR(25) = CAST(CAST(CONVERT(varbinary,SUBSTRING(@LSN, 1, 8),2) AS INT) AS VARCHAR) + ':' + CAST(CAST(CONVERT(varbinary,SUBSTRING(@LSN, 10, 8),2) AS INT) AS VARCHAR) + ':' + CAST(CAST(CONVERT(varbinary,SUBSTRING(@LSN, 19, 4),2) AS INT) AS VARCHAR) SELECT [Operation], [Context], [AllocUnitName], [Transaction Name], [Description] FROM fn_dblog(@LSN_HEX, null) AS D WHERE [Current LSN] > @LSN SELECT CASE WHEN GROUPING(Operation) = 1 THEN 'Total' ELSE Operation END AS Operation, Context, AllocUnitName, COALESCE(SUM([Log Record Length]), 0) AS [Size in Bytes], COUNT(*) AS Cnt FROM fn_dblog(@LSN_HEX, null) AS D WHERE [Current LSN] > @LSN GROUP BY GROUPING SETS((Operation, Context, AllocUnitName),()) 

Resultados

 +-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+ | | | | @TV | #TV | | +-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+ | Operation | Context | AllocUnitName | Size in Bytes | Cnt | Size in Bytes | Cnt | Difference Bytes | +-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+ | LOP_ABORT_XACT | LCX_NULL | | 52 | 1 | 52 | 1 | | | LOP_BEGIN_XACT | LCX_NULL | | 6056 | 50 | 6056 | 50 | | | LOP_COMMIT_XACT | LCX_NULL | | 2548 | 49 | 2548 | 49 | | | LOP_COUNT_DELTA | LCX_CLUSTERED | sys.sysallocunits.clust | 624 | 3 | 624 | 3 | | | LOP_COUNT_DELTA | LCX_CLUSTERED | sys.sysrowsets.clust | 208 | 1 | 208 | 1 | | | LOP_COUNT_DELTA | LCX_CLUSTERED | sys.sysrscols.clst | 832 | 4 | 832 | 4 | | | LOP_CREATE_ALLOCCHAIN | LCX_NULL | | 120 | 3 | 120 | 3 | | | LOP_DELETE_ROWS | LCX_INDEX_INTERIOR | Unknown Alloc Unit | 720 | 9 | 720 | 9 | | | LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysallocunits.clust | 444 | 3 | 444 | 3 | | | LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysallocunits.nc | 276 | 3 | 276 | 3 | | | LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.syscolpars.clst | 628 | 4 | 628 | 4 | | | LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.syscolpars.nc | 484 | 4 | 484 | 4 | | | LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysidxstats.clst | 176 | 1 | 176 | 1 | | | LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysidxstats.nc | 144 | 1 | 144 | 1 | | | LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysiscols.clst | 100 | 1 | 100 | 1 | | | LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysiscols.nc1 | 88 | 1 | 88 | 1 | | | LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysobjvalues.clst | 596 | 5 | 596 | 5 | | | LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysrowsets.clust | 132 | 1 | 132 | 1 | | | LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysrscols.clst | 528 | 4 | 528 | 4 | | | LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysschobjs.clst | 1040 | 6 | 1276 | 6 | 236 | | LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysschobjs.nc1 | 820 | 6 | 1060 | 6 | 240 | | LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysschobjs.nc2 | 820 | 6 | 1060 | 6 | 240 | | LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysschobjs.nc3 | 480 | 6 | 480 | 6 | | | LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.syssingleobjrefs.clst | 96 | 1 | | | -96 | | LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.syssingleobjrefs.nc1 | 88 | 1 | | | -88 | | LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | Unknown Alloc Unit | 72092 | 19 | 72092 | 19 | | | LOP_DELETE_ROWS | LCX_TEXT_MIX | Unknown Alloc Unit | 16348 | 37 | 16348 | 37 | | | LOP_FORMAT_PAGE | LCX_HEAP | Unknown Alloc Unit | 1596 | 19 | 1596 | 19 | | | LOP_FORMAT_PAGE | LCX_IAM | Unknown Alloc Unit | 252 | 3 | 252 | 3 | | | LOP_FORMAT_PAGE | LCX_INDEX_INTERIOR | Unknown Alloc Unit | 84 | 1 | 84 | 1 | | | LOP_FORMAT_PAGE | LCX_TEXT_MIX | Unknown Alloc Unit | 4788 | 57 | 4788 | 57 | | | LOP_HOBT_DDL | LCX_NULL | | 108 | 3 | 108 | 3 | | | LOP_HOBT_DELTA | LCX_NULL | | 9600 | 150 | 9600 | 150 | | | LOP_INSERT_ROWS | LCX_CLUSTERED | sys.sysallocunits.clust | 456 | 3 | 456 | 3 | | | LOP_INSERT_ROWS | LCX_CLUSTERED | sys.syscolpars.clst | 644 | 4 | 644 | 4 | | | LOP_INSERT_ROWS | LCX_CLUSTERED | sys.sysidxstats.clst | 180 | 1 | 180 | 1 | | | LOP_INSERT_ROWS | LCX_CLUSTERED | sys.sysiscols.clst | 104 | 1 | 104 | 1 | | | LOP_INSERT_ROWS | LCX_CLUSTERED | sys.sysobjvalues.clst | 616 | 5 | 616 | 5 | | | LOP_INSERT_ROWS | LCX_CLUSTERED | sys.sysrowsets.clust | 136 | 1 | 136 | 1 | | | LOP_INSERT_ROWS | LCX_CLUSTERED | sys.sysrscols.clst | 544 | 4 | 544 | 4 | | | LOP_INSERT_ROWS | LCX_CLUSTERED | sys.sysschobjs.clst | 1064 | 6 | 1300 | 6 | 236 | | LOP_INSERT_ROWS | LCX_CLUSTERED | sys.syssingleobjrefs.clst | 100 | 1 | | | -100 | | LOP_INSERT_ROWS | LCX_CLUSTERED | Unknown Alloc Unit | 135888 | 19 | 135888 | 19 | | | LOP_INSERT_ROWS | LCX_INDEX_INTERIOR | Unknown Alloc Unit | 1596 | 19 | 1596 | 19 | | | LOP_INSERT_ROWS | LCX_INDEX_LEAF | sys.sysallocunits.nc | 288 | 3 | 288 | 3 | | | LOP_INSERT_ROWS | LCX_INDEX_LEAF | sys.syscolpars.nc | 500 | 4 | 500 | 4 | | | LOP_INSERT_ROWS | LCX_INDEX_LEAF | sys.sysidxstats.nc | 148 | 1 | 148 | 1 | | | LOP_INSERT_ROWS | LCX_INDEX_LEAF | sys.sysiscols.nc1 | 92 | 1 | 92 | 1 | | | LOP_INSERT_ROWS | LCX_INDEX_LEAF | sys.sysschobjs.nc1 | 844 | 6 | 1084 | 6 | 240 | | LOP_INSERT_ROWS | LCX_INDEX_LEAF | sys.sysschobjs.nc2 | 844 | 6 | 1084 | 6 | 240 | | LOP_INSERT_ROWS | LCX_INDEX_LEAF | sys.sysschobjs.nc3 | 504 | 6 | 504 | 6 | | | LOP_INSERT_ROWS | LCX_INDEX_LEAF | sys.syssingleobjrefs.nc1 | 92 | 1 | | | -92 | | LOP_INSERT_ROWS | LCX_TEXT_MIX | Unknown Alloc Unit | 5112 | 71 | 5112 | 71 | | | LOP_MARK_SAVEPOINT | LCX_NULL | | 508 | 8 | 508 | 8 | | | LOP_MODIFY_COLUMNS | LCX_CLUSTERED | Unknown Alloc Unit | 1560 | 10 | 1560 | 10 | | | LOP_MODIFY_HEADER | LCX_HEAP | Unknown Alloc Unit | 3780 | 45 | 3780 | 45 | | | LOP_MODIFY_ROW | LCX_CLUSTERED | sys.syscolpars.clst | 384 | 4 | 384 | 4 | | | LOP_MODIFY_ROW | LCX_CLUSTERED | sys.sysidxstats.clst | 100 | 1 | 100 | 1 | | | LOP_MODIFY_ROW | LCX_CLUSTERED | sys.sysrowsets.clust | 92 | 1 | 92 | 1 | | | LOP_MODIFY_ROW | LCX_CLUSTERED | sys.sysschobjs.clst | 1144 | 13 | 1144 | 13 | | | LOP_MODIFY_ROW | LCX_IAM | Unknown Alloc Unit | 4224 | 48 | 4224 | 48 | | | LOP_MODIFY_ROW | LCX_PFS | Unknown Alloc Unit | 13632 | 169 | 13632 | 169 | | | LOP_MODIFY_ROW | LCX_TEXT_MIX | Unknown Alloc Unit | 108640 | 120 | 108640 | 120 | | | LOP_ROOT_CHANGE | LCX_CLUSTERED | sys.sysallocunits.clust | 960 | 10 | 960 | 10 | | | LOP_SET_BITS | LCX_GAM | Unknown Alloc Unit | 1200 | 20 | 1200 | 20 | | | LOP_SET_BITS | LCX_IAM | Unknown Alloc Unit | 1080 | 18 | 1080 | 18 | | | LOP_SET_BITS | LCX_SGAM | Unknown Alloc Unit | 120 | 2 | 120 | 2 | | | LOP_SHRINK_NOOP | LCX_NULL | | | | 32 | 1 | 32 | +-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+ | Total | | | 410144 | 1095 | 411232 | 1092 | 1088 | +-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+ 

¿En qué escenarios uno supera al otro?

Para tablas más pequeñas (menos de 1000 filas) use una variable temp; de lo contrario, use una tabla temporal.

@wcm: en realidad, para elegir la variable de tabla no es solo RAM, se puede almacenar parcialmente en el disco.

Una tabla temporal puede tener índices, mientras que una variable de tabla solo puede tener un índice primario. Si la velocidad es un problema Las variables de tabla pueden ser más rápidas, pero obviamente si hay muchos registros o la necesidad de buscar en la tabla temporal de un índice agrupado, entonces una tabla de temperatura sería mejor.

Buen artículo de fondo

  1. Tabla de temperatura: una tabla Temp es fácil de crear y hacer una copia de seguridad de los datos.

    Variable de tabla: pero la variable de tabla implica el esfuerzo cuando generalmente creamos las tablas normales.

  2. Tabla de temperatura: el resultado de la tabla de temperatura puede ser utilizado por múltiples usuarios.

    Variable de tabla: pero la variable de tabla solo puede ser utilizada por el usuario actual.

  3. Tabla de temperatura: la tabla de temperatura se almacenará en tempdb. Hará tráfico de red. Cuando tenemos datos grandes en la tabla temporal, entonces tiene que funcionar en toda la base de datos. Un problema de rendimiento existirá.

    Variable de tabla: pero una variable de tabla almacenará en la memoria física algunos de los datos, luego, cuando el tamaño aumente, se moverá a tempdb.

  4. Tabla de temperatura: la tabla de temperatura puede hacer todas las operaciones de DDL. Permite crear los índices, soltar, alterar, etc.,

    Variable de tabla: mientras que la variable de tabla no permitirá hacer las operaciones DDL. Pero la variable de la tabla nos permite crear solo el índice agrupado.

  5. Tabla de temperatura: la tabla de temperatura se puede utilizar para la sesión actual o global. Para que una sesión de usuario múltiple pueda utilizar los resultados en la tabla.

    Variable de tabla: pero la variable de tabla puede usarse hasta ese progtwig. (Procedimiento almacenado)

  6. Tabla de temperatura: la variable de temperatura no puede usar las transacciones. Cuando hacemos las operaciones DML con la tabla temporal, puede deshacerse o comprometer las transacciones.

    Variable de tabla: pero no podemos hacerlo para la variable de tabla.

  7. Tabla de temperatura: las funciones no pueden usar la variable de temperatura. Además, no podemos hacer la operación DML en las funciones.

    Variable de tabla: pero la función nos permite usar la variable de tabla. Pero usando la variable de tabla podemos hacer eso.

  8. Tabla de temperatura: el procedimiento almacenado hará la recomstackción (no se puede usar el mismo plan de ejecución) cuando usemos la variable de temperatura para cada llamada subsiguiente.

    Variable de tabla: mientras que la variable de tabla no hará eso.

Para todos los que creen en el mito de que las variables temporales solo están en la memoria

Primero, la variable de tabla NO es necesariamente residente de memoria. Bajo la presión de la memoria, las páginas que pertenecen a una variable de tabla pueden enviarse a tempdb.

Lea el artículo aquí: TempDB :: Tabla variable vs tabla temporal local

La otra diferencia principal es que las variables de la tabla no tienen estadísticas de columnas, como lo hacen las tablas temporales. Esto significa que el optimizador de consultas no sabe cuántas filas hay en la variable de la tabla (conjetura 1), lo que puede generar que se generen planes altamente no óptimos si la variable de la tabla realmente tiene una gran cantidad de filas.

Cita tomada de; Professional SQL Server 2012 Internals y resolución de problemas

Estadísticas La principal diferencia entre las tablas temporales y las variables de tablas es que las estadísticas no se crean en las variables de la tabla. Esto tiene dos consecuencias principales, la primera de las cuales es que Query Optimizer utiliza una estimación fija para el número de filas en una variable de tabla, independientemente de los datos que contiene. Además, agregar o eliminar datos no cambia la estimación.

Índices No puede crear índices en las variables de la tabla, aunque puede crear restricciones. Esto significa que al crear claves primarias o restricciones únicas, puede tener índices (ya que se crean para soportar restricciones) en variables de tabla. Incluso si tiene restricciones y, por lo tanto, índices que tendrán estadísticas, los índices no se usarán cuando se compile la consulta, ya que no existirán en tiempo de comstackción ni generarán recomstackciones.

Modificaciones de esquema Las modificaciones de esquema son posibles en tablas temporales pero no en variables de tabla. Aunque las modificaciones de esquema son posibles en tablas temporales, evite utilizarlas porque provocan recomstackciones de sentencias que usan las tablas.

Tablas temporales versus variables de tabla

LAS VARIABLES DE LA TABLA NO SE CREAN EN LA MEMORIA

Existe una idea errónea de que las variables de tabla son estructuras en memoria y, como tales, se ejecutarán más rápidamente que las tablas temporales . Gracias a un DMV llamado sys. dm _ db _ session _ space _ usage, que muestra el uso de tempdb por sesión, puede probar que ese no es el caso . Después de reiniciar SQL Server para borrar el DMV, ejecute el siguiente script para confirmar que su sesión _id devuelve 0 para el usuario _ objects _ alloc _ page _ count:

 SELECT session_id, database_id, user_objects_alloc_page_count FROM sys.dm_db_session_space_usage WHERE session_id > 50 ; 

Ahora puede verificar cuánto espacio utiliza una tabla temporal ejecutando la siguiente secuencia de comandos para crear una tabla temporal con una columna y llenarla con una fila:

 CREATE TABLE #TempTable ( ID INT ) ; INSERT INTO #TempTable ( ID ) VALUES ( 1 ) ; GO SELECT session_id, database_id, user_objects_alloc_page_count FROM sys.dm_db_session_space_usage WHERE session_id > 50 ; 

Los resultados en mi servidor indican que a la tabla se le asignó una página en tempdb. Ahora ejecute el mismo script pero use una variable de tabla esta vez:

 DECLARE @TempTable TABLE ( ID INT ) ; INSERT INTO @TempTable ( ID ) VALUES ( 1 ) ; GO SELECT session_id, database_id, user_objects_alloc_page_count FROM sys.dm_db_session_space_usage WHERE session_id > 50 ; 

¿Cuál usar?

Si se usan o no tablas temporales o variables de tablas se debe decidir mediante pruebas exhaustivas, pero lo mejor es inclinarse hacia las tablas temporales como las predeterminadas porque hay muchas menos cosas que pueden salir mal .

He visto a clientes desarrollar código utilizando variables de tabla porque trataban con una pequeña cantidad de filas, y era más rápido que una tabla temporal, pero unos años después había cientos de miles de filas en la variable de tabla y el rendimiento era terrible , ¡intente y permita un poco de planificación de capacidad cuando tome su decisión!

Otra diferencia:

Solo se puede acceder a una tabla var desde las instrucciones dentro del procedimiento que la crea, no desde otros procedimientos llamados por ese procedimiento o SQL dynamic nested (a través de exec o sp_executesql).

El scope de una tabla temporal, por otro lado, incluye código en procedimientos llamados y SQL dynamic nested.

Si la tabla creada por su procedimiento debe ser accesible desde otros procedimientos llamados o SQL dynamic, debe usar una tabla temporal. Esto puede ser muy útil en situaciones complejas.

Considere también que a menudo puede reemplazar ambos con tablas derivadas que también pueden ser más rápidas. Sin embargo, al igual que con todos los ajustes de rendimiento, solo las pruebas reales con sus datos reales pueden indicarle el mejor enfoque para su consulta en particular.

Tabla temporal

La tabla temporal se comporta como una tabla real, pero se crea en tiempo de ejecución. Su trabajo es similar a la mesa real. Podemos hacer casi todas las operaciones posibles en tablas reales. Podemos usar sentencias DDL como ALTER, CREATE, DROP en tablas temporales.

Cualquier cambio en la estructura de la tabla temporal es posible después de la creación. Tabla temporal almacenada en la base de datos “tempdb” de las bases de datos del sistema.

Tabla temporal participar en transacciones, registro o locking. Por esa razón, es más lento que la variable de tabla.

Variable de tabla

Es variable pero funciona como una mesa. También se crea en la base de datos Tempdb no en la memoria. Variable de tabla solo disponible en el lote o en el scope del Procedimiento almacenado. No es necesario que suelte la variable de tabla, se descarta automáticamente cuando se completa el proceso de ejecución de proceso y lote

Table variable support primary Key, identidad en el tiempo de creación. Pero no es compatible con el índice no agrupado. Después de declarar la clave principal, la identidad no puede modificarlos.

Las variables de tabla no participan en transacciones, registro o locking. Las transacciones, el registro y el locking no afectan a las variables de la tabla.

Lea este artículo para más información – http://goo.gl/GXtXqz