Qual é a diferença entre uma tabela temporária e uma variável de tabela no SQL Server?

No SQL Server 2005, podemos criar tabelas temporárias de duas maneiras:

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

ou

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

Quais são as diferenças entre esses dois? Eu li opiniões conflitantes sobre se o @tmp ainda usa o tempdb, ou se tudo acontece na memory.

Em quais cenários um supera o outro?

    Existem algumas diferenças entre Tabelas Temporárias (#tmp) e Variáveis ​​de Tabela (@tmp), embora o uso de tempdb não seja uma delas, conforme explicado no link do MSDN abaixo.

    Como regra geral, para pequenos e médios volumes de dados e cenários de uso simples, você deve usar variables ​​de tabela. (Esta é uma diretriz excessivamente ampla, obviamente com muitas exceções – veja abaixo e artigos a seguir).

    Alguns pontos a considerar ao escolher entre eles:

    • Tabelas temporárias são tabelas reais para que você possa fazer coisas como CREATE INDEXes, etc. Se você tiver grandes quantidades de dados para os quais o access por índice será mais rápido, as tabelas temporárias serão uma boa opção.

    • As variables ​​de tabela podem ter índices usando restrições PRIMARY KEY ou UNIQUE. (Se você quiser um índice não exclusivo, apenas inclua a coluna de chave primária como a última coluna na restrição exclusiva. Se você não tiver uma coluna exclusiva, poderá usar uma coluna de identidade.) O SQL 2014 também possui índices não exclusivos .

    • Variáveis ​​de tabela não participam de transactions e SELECT s são implicitamente com NOLOCK . O comportamento da transação pode ser muito útil, por exemplo, se você quiser ROLLBACK no meio de um procedimento, as variables ​​da tabela preenchidas durante essa transação ainda serão preenchidas!

    • Tabelas temporárias podem resultar em stored procedures sendo recompilados, talvez com freqüência. Variáveis ​​de tabela não.

    • Você pode criar uma tabela temporária usando SELECT INTO, que pode ser mais rápido de escrever (bom para consultas ad-hoc) e pode permitir que você lide com mudanças de tipos de dados ao longo do tempo, já que não precisa definir a estrutura da tabela temporária antecipadamente.

    • Você pode passar variables ​​de tabela de volta das funções, permitindo encapsular e reutilizar a lógica muito mais facilmente (por exemplo, fazer uma function para dividir uma string em uma tabela de valores em algum delimitador arbitrário).

    • Usar as variables ​​de tabela nas funções definidas pelo usuário permite que essas funções sejam usadas mais amplamente (consulte a documentação do comando CREATE FUNCTION para obter detalhes). Se você está escrevendo uma function, você deve usar variables ​​de tabela sobre tabelas temporárias, a menos que exista uma necessidade de outra forma.

    • Ambas as variables ​​de tabela e tabelas temporárias são armazenadas em tempdb. Mas as variables ​​de tabela (desde 2005) padrão para o agrupamento do database atual versus tabelas temporárias que levam o agrupamento padrão de tempdb ( ref ). Isso significa que você deve estar ciente dos problemas de agrupamento se estiver usando tabelas temporárias e seu agrupamento de db for diferente do do tempdb, causando problemas se você quiser comparar os dados na tabela temporária com dados em seu database.

    • Tabelas temporárias globais (## tmp) são outro tipo de tabela temporária disponível para todas as sessões e usuários.

    Alguma leitura adicional:

    Apenas olhando para a afirmação na resposta aceita, as variables ​​da tabela não participam do registro.

    Parece geralmente falso que haja alguma diferença na quantidade de logs (pelo menos para operações de insert / update / delete na própria tabela, embora eu tenha descoberto que há alguma pequena diferença a esse respeito para objects temporários armazenados em cache em stored procedures devido a atualizações adicionais da tabela do sistema).

    Eu olhei para o comportamento de log contra as @table_variable e #temp para as seguintes operações.

    1. Inserção de sucesso
    2. Multi Row Insert, onde a instrução foi revertida devido à violação de restrição.
    3. Atualizar
    4. Excluir
    5. Deallocate

    Os registros do log de transactions eram quase idênticos para todas as operações.

    A versão da variável de tabela na verdade tem algumas inputs de log extras porque obtém uma input adicionada (e mais tarde removida da) tabela base sys.syssingleobjrefs , mas teve alguns bytes a menos registrados apenas porque o nome interno das variables ​​de tabela consome 236 bytes a menos do que para tabelas #temp (menos 118 caracteres nvarchar ).

    Script completo para reprodução (melhor execução em uma instância iniciada no modo de usuário único e usando o 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 | +-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+ 

    Em quais cenários um supera o outro?

    Para tabelas menores (menos de 1000 linhas) use uma variável temporária, caso contrário, use uma tabela temporária.

    @wcm – na verdade, para escolher nit a Variável da tabela não é apenas Ram – pode ser parcialmente armazenado no disco.

    Uma tabela temporária pode ter índices, enquanto uma variável de tabela pode ter apenas um índice primário. Se a velocidade for um problema As variables ​​de tabela podem ser mais rápidas, mas, obviamente, se houver muitos registros ou a necessidade de pesquisar a tabela temporária de um índice clusterizado, uma tabela temporária seria melhor.

    Bom artigo de fundo

    1. Tabela temporária: Uma tabela temporária é fácil de criar e fazer backup de dados.

      Variável de tabela: Mas a variável de tabela envolve o esforço quando normalmente criamos as tabelas normais.

    2. Tabela temporária: o resultado da tabela temporária pode ser usado por vários usuários.

      Variável de tabela: mas a variável de tabela pode ser usada apenas pelo usuário atual.

    3. Tabela temporária: A tabela temporária será armazenada no tempdb. Isso fará com que o tráfego de rede. Quando temos dados grandes na tabela temporária, eles precisam funcionar no database. Um problema de desempenho existirá.

      Variável de tabela: mas uma variável de tabela armazenará na memory física alguns dos dados e, posteriormente, quando o tamanho aumentar, ele será movido para o tempdb.

    4. Tabela temporária: A tabela temporária pode fazer todas as operações de DDL. Permite criar os índices, soltando, alterando, etc.

      Variável de tabela: Considerando que a variável de tabela não permitirá fazer as operações de DDL. Mas a variável table nos permite criar apenas o índice clusterizado.

    5. Tabela temporária: A tabela temporária pode ser usada para a session atual ou global. Para que uma session de vários usuários possa utilizar os resultados na tabela.

      Variável de tabela: mas a variável de tabela pode ser usada até esse programa. (Procedimento armazenado)

    6. Tabela temporária: a variável Temp não pode usar as transactions. Quando fazemos as operações DML com a tabela temporária, ela pode reverter ou confirmar as transactions.

      Variável de tabela: Mas não podemos fazer isso para a variável de tabela.

    7. Tabela Temp: As funções não podem usar a variável temp. Mais ainda, não podemos fazer a operação DML nas funções.

      Variável de tabela: Mas a function nos permite usar a variável de tabela. Mas usando a variável table nós podemos fazer isso.

    8. Tabela Temp: O procedimento armazenado fará a recompilation (não pode usar o mesmo plano de execução) quando usamos a variável temp para todas as chamadas subsequentes.

      Variável de tabela: Considerando que a variável de tabela não será assim.

    Para todos vocês que acreditam no mito de que as variables ​​temporárias estão apenas na memory

    Primeiro, a variável table não é necessariamente residente na memory. Sob pressão de memory, as páginas pertencentes a uma variável de tabela podem ser enviadas para tempdb.

    Leia o artigo aqui: TempDB :: Table variable vs local temporary table

    A outra diferença principal é que as variables ​​de tabela não possuem statistics de coluna, onde as tabelas temporárias fazem. Isso significa que o otimizador de consulta não sabe quantas linhas estão na variável table (ele adivinha 1), o que pode levar a que planos altamente não ideais sejam gerados se a variável table realmente tiver um grande número de linhas.

    Cotação retirada de; Internos e solução de problemas profissionais do SQL Server 2012

    Estatísticas A principal diferença entre tabelas temporárias e variables ​​de tabela é que as statistics não são criadas em variables ​​de tabela. Isso tem duas conseqüências principais, a primeira das quais é que o otimizador de consultas usa uma estimativa fixa para o número de linhas em uma variável de tabela, independentemente dos dados contidos nela. Além disso, adicionar ou remover dados não altera a estimativa.

    Índices Você não pode criar índices em variables ​​de tabela, embora possa criar restrições. Isso significa que, ao criar chaves primárias ou restrições exclusivas, é possível ter índices (como esses são criados para suportar restrições) em variables ​​de tabela. Mesmo se você tiver restrições e, portanto, índices que terão statistics, os índices não serão usados ​​quando a consulta for compilada porque eles não existirão em tempo de compilation, nem causarão recompilações.

    Modificações de Esquema As modificações de esquema são possíveis em tabelas temporárias, mas não em variables ​​de tabela. Embora as modificações de esquema sejam possíveis em tabelas temporárias, evite usá-las porque elas causam recompilações de instruções que usam as tabelas.

    Tabelas temporárias versus variáveis ​​de tabela

    VARIÁVEIS DE TABELA NÃO SÃO CRIADAS EM MEMÓRIA

    Há um equívoco comum de que as variables ​​de tabela são estruturas na memory e, como tal, serão mais rápidas que as tabelas temporárias . Graças a um DMV chamado sys. dm _db _ session _ space _ usage, que mostra o uso de tempdb por session, você pode provar que não é o caso . Depois de reiniciar o SQL Server para limpar o DMV, execute o script a seguir para confirmar que sua session _id retorna 0 para user _ 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 ; 

    Agora você pode verificar quanto espaço uma tabela temporária usa executando o seguinte script para criar uma tabela temporária com uma coluna e preenchê-la com uma linha:

     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 ; 

    Os resultados no meu servidor indicam que a tabela foi alocada em uma página em tempdb. Agora, execute o mesmo script, mas use uma variável de tabela desta 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 ; 

    Qual deles usar?

    Se você usa ou não tabelas temporárias ou variables ​​de tabela deve ser decidido por um teste completo, mas é melhor se inclinar para tabelas temporárias como o padrão, porque há muito menos coisas que podem dar errado .

    Eu vi clientes desenvolverem código usando variables ​​de tabela porque eles estavam lidando com uma pequena quantidade de linhas, e era mais rápido que uma tabela temporária, mas alguns anos depois havia centenas de milhares de linhas na variável table e o desempenho era terrível , então tente e permita algum planejamento de capacidade quando você tomar sua decisão!

    Outra diferença

    Uma tabela var só pode ser acessada a partir de instruções dentro do procedimento que a cria, não de outros procedimentos chamados por esse procedimento ou SQL dynamic nested (via exec ou sp_executesql).

    Por outro lado, o escopo de uma tabela temporária inclui código em procedimentos chamados e SQL dynamic nested.

    Se a tabela criada pelo seu procedimento deve ser acessível a partir de outros procedimentos chamados ou SQL dynamic, você deve usar uma tabela temporária. Isso pode ser muito útil em situações complexas.

    Considere também que você pode replace os dois com tabelas derivadas, que podem ser mais rápidas também. No entanto, como em todos os ajustes de desempenho, somente os testes reais em relação aos dados reais podem indicar a melhor abordagem para sua consulta específica.

    Mesa Temporária

    A tabela temporária se comporta como uma tabela real, mas criada no tempo de execução. Seu trabalho é semelhante à mesa real. Podemos fazer quase todas as operações possíveis em tabelas reais. Podemos usar instruções DDL como ALTER, CREATE, DROP em tabelas temporárias.

    Quaisquer alterações na estrutura da tabela temporária são possíveis após a criação. Tabela Temporária armazenada em database “tempdb” de bancos de dados do sistema.

    Tabela Temporária participa de transactions, registro ou bloqueio. Por essa razão, é mais lento que a variável Table.

    Variável de tabela

    É variável, mas funciona como uma mesa. Também é criado no Banco de Dados Tempdb não na memory. Variável de tabela disponível apenas no escopo do procedimento armazenado ou em lote. Você não precisa descartar a variável de tabela, ele é automaticamente descartado quando o processo de execução do processo de lote e armazenamento é concluído

    Suporte à variável de tabela primária Chave, identidade no tempo de criação. Mas não suporta índice não agrupado. Após a chave primária de declaração, identidade você não pode modificá-las.

    As variables ​​de tabela não participam de transactions, registro ou bloqueio. Transações, registro e bloqueio não afetam as variables ​​da tabela.

    Leia este artigo para mais – http://goo.gl/GXtXqz