Redgate Hub (Português)

As tabelas temporárias são exatamente isso. Eles são usados com mais freqüência para fornecer espaço de trabalho para os resultados intermediários ao processar dados em um lote ou procedimento. Eles também são usados para passar uma tabela de uma função com valor de tabela, para passar dados baseados em tabela entre procedimentos armazenados ou, mais recentemente na forma de parâmetros com valor de tabela, para enviar tabelas inteiras somente leitura de aplicativos para rotinas do SQL Server ou passe tabelas temporárias somente leitura como parâmetros. Depois de concluídos com seu uso, eles são descartados automaticamente.

Antes de nos aprofundarmos na tecnologia, aconselho que você use variáveis de tabela sempre que possível. Eles são fáceis e o SQL Server faz o trabalho para você. Eles também tendem a causar menos problemas a um sistema OLTP que trabalha muito. Apenas ocasionalmente, você pode precisar ajustá-los para obter um bom desempenho deles nas junções, mas explicarei isso em um momento, no entanto, se você estiver fazendo um processamento mais complexo em dados temporários ou provavelmente usar mais do que razoavelmente pequeno quantidades de dados neles, então as tabelas temporárias locais são provavelmente uma escolha melhor.

Variáveis da tabela

As variáveis da tabela são usadas dentro do escopo da rotina ou lote em que estão definidos e foram originalmente criados para possibilitar funções com valor de tabela. No entanto, eles são bons para muitos dos usos que a mesa temporária tradicional foi feita. Eles se comportam como outras variáveis em suas regras de escopo. Uma vez fora do escopo, eles são descartados. Eles são muito mais fáceis de trabalhar e muito seguros, e também disparam menos recompilações nas rotinas onde são usados do que se você usasse tabelas temporárias. Variáveis de tabela requerem menos recursos de bloqueio, pois são “privadas” para o processo que as criou. As reversões de transação não os afetam porque as variáveis de tabela têm escopo limitado e não fazem parte do banco de dados persistente, portanto, são úteis para criar ou armazenar dados que devem sobreviver a reversões, como entradas de log. A desvantagem das variáveis de tabela é que muitas vezes elas são descartadas antes que você possa investigar seu conteúdo para depuração ou usá-las para experimentar diferentes expressões SQL interativamente.

Se seu aplicativo é conservador e seus volumes de dados iluminam você nunca mais vou querer nada. No entanto, você pode encontrar problemas. Uma dificuldade é que as variáveis de tabela só podem ser referenciadas em seu escopo local, portanto, você não pode processá-las usando SQL dinâmico como faria com uma tabela temporária ou parâmetro com valor de tabela. Isso ocorre porque você não pode se referir a uma variável de tabela definida externamente dentro do SQL dinâmico que você executa por meio da instrução EXEC ou do procedimento armazenado sp_ExecuteSQL porque o SQL dinâmico é executado fora do escopo da variável da tabela. Você pode, é claro, criar e usar a variável de tabela dentro do SQL dinâmico porque a variável de tabela estaria no escopo. No entanto, uma vez que o SQL dinâmico é executado, não haveria nenhuma variável de tabela

Existem algumas anomalias a serem observadas também. Você não pode, por exemplo, alterar a definição da tabela após a instrução DECLARE inicial. No SQL Server 2000, uma variável de tabela não pode ser o destino de uma instrução SELECT INTO ou INSERT EXEC (agora corrigido); Você não pode chamar funções definidas pelo usuário a partir de restrições CHECK, valores DEFAULT e colunas calculadas na variável da tabela. As únicas restrições permitidas além das restrições CHECK são PRIMARY KEY, UNIQUE KEY e NULL / NOT NULL

Os problemas mais complicados, porém, vêm com o aumento do tamanho das tabelas, porque, antes do SQL Server 2016 , você não poderia declarar um índice explicitamente e os índices que impunham as restrições UNIQUE e PRIMARY KEY não tinham índices de distribuição mantidos neles. Agora você pode criar certos tipos de índice em linha com a definição da tabela, mas as estatísticas de distribuição ainda não são mantidas neles. O Query Optimizer assume que existe apenas uma linha na tabela. Você também não pode gerar planos de consulta paralelos para uma expressão SQL que está modificando o conteúdo da tabela. Para contornar parcialmente a restrição do índice, você pode usar restrições para fazer a mesma coisa. O mais essencial é a restrição de chave primária, que permite impor um índice agrupado, mas restrições exclusivas são úteis para o desempenho. O otimizador de consulta ficará feliz em usá-los se eles estiverem por perto.

O maior problema com variáveis de tabela é que as estatísticas não são mantidas nas colunas. Isso significa que o otimizador de consulta deve adivinhar o tamanho e a distribuição dos dados e, se errar, você verá um desempenho ruim nas junções: Se isso acontecer, há pouco que você possa fazer. do que voltar a usar tabelas temporárias locais clássicas. A partir do SQL Server 2019, a Microsoft introduziu um novo recurso chamado Table Variable Deferred Compilation que resolve esse problema. Para saber mais, leia este artigo de Greg Larsen.

Se você não estiver usando o SQL Server 2019, uma coisa que você pode tentar é adicionar OPTION (RECOMPILE) à instrução que envolve a junção da variável da tabela com outras tabelas. .Ao fazer isso, o SQL Server será capaz de detectar o número de linhas na recompilação porque as linhas já terão sido preenchidas. Isso não resolve totalmente o problema, pois o otimizador ainda não tem estatísticas de distribuição e pode, geralmente onde a distribuição é distorcida, produzir um plano ruim. Nesta demonstração, a junção foi reduzida no tempo em três quartos simplesmente adicionando a opção (RECOMPILAR)

Agora, se você pode tornar o que entra nas tabelas exclusivo, você pode usar uma restrição de chave primária nestes tabelas. Isso permitiu que o otimizador usasse uma busca de índice clusterizado em vez de uma varredura de tabela e o tempo de execução era muito rápido para medir

Comece com variáveis de tabela, mas volte a usar tabelas temporárias locais se você tiver problemas de desempenho. Algumas pessoas são ousadas o suficiente para dar conselhos em termos do número de linhas em uma tabela, e eu vi 100 ou 1000 oferecidos no máximo; mas eu vi variáveis de tabela muito maiores terem um desempenho perfeitamente satisfatório ao longo do tempo, e muito menores darem problemas. No entanto, em tabelas menores, o problema é menos detectável!

Parâmetros com valor de tabela

O parâmetro com valor de tabela (TVP) é um tipo especial de variável de tabela que estende seu uso. Quando variáveis de tabela são passadas como parâmetros, a tabela é materializada no banco de dados do sistema TempDB como uma variável de tabela e passada por referência, um ponteiro para a tabela em TempDB.

Parâmetros com valor de tabela têm sido usados desde SQL Server 2008 para enviar várias linhas de dados para uma rotina Transact-SQL ou para um lote via sp_ExecuteSQL. Seu valor particular para o programador é que eles podem ser usados dentro do código TSQL como bem como no aplicativo cliente, portanto, são bons para enviar tabelas do cliente para o servidor. No TSQL, você pode declarar variáveis com valor de tabela, inserir dados nelas e passar essas variáveis como parâmetros com valor de tabela para procedimentos e funções armazenados. Sua utilidade mais geral é limitada pelo fato de que são passadas apenas como somente leitura. Você não pode fazer UPDATE, DELETE ou INSERT instruções em uma tabela com valor parâmetro no corpo de uma rotina.

Você precisa criar um tipo de tabela definido pelo usuário e definir uma estrutura de tabela para usá-los. Aqui está um exemplo simples de seu uso em TSQL

Assim como acontece com as variáveis de tabela, o parâmetro com valor de tabela deixa de existir quando está fora do escopo, mas a definição do tipo permanece até que seja explicitamente eliminado.Como variáveis de tabela, eles não adquirem bloqueios quando os dados estão sendo preenchidos de um cliente e as estatísticas não são mantidas em colunas de parâmetros com valor de tabela. Você não pode usar um parâmetro com valor de tabela como destino de uma instrução SELECT INTO ou INSERT EXEC. Como seria de se esperar, um parâmetro com valor de tabela pode estar na FROM cláusula de SELECT INTO ou no INSERT EXEC string ou procedimento armazenado.

O TVP resolve o problema comum de querer passar uma variável local para SQL dinâmico que é então executado por um sp_ExecuteSQL. Ele é mal documentado pela Microsoft, então vou mostrar um exemplo prático para você começar

Antes de passarmos a descrever as tabelas temporárias mais tradicionais e seu uso, precisaremos nos aprofundar no local onde as mesas temporárias são mantidas. TempDB.

TempDB

Tabelas temporárias e variáveis de tabela são criadas no banco de dados TempDB, que na verdade é apenas outro banco de dados com recuperação simples: Com TempDB, apenas o registro mínimo é feito para permitir reversão e outras sutilezas do ACID. A diferença especial do TempDB é que quaisquer objetos, como tabelas, são limpos na inicialização. Como TempDB sempre usa o modelo de recuperação simples, as transações concluídas são apagadas do log de log no próximo ponto de verificação TempDB e apenas as transações ativas são mantidas. Tudo isso significa que as tabelas temporárias se comportam como qualquer outro tipo de tabela base, pois são registradas e armazenadas como elas. Na prática, as tabelas temporárias provavelmente permanecerão armazenadas em cache na memória, mas apenas se forem usadas com frequência: o mesmo que acontece com uma tabela base. TempDB opera um sistema denominado reutilização de objetos temporários, que armazenará em cache uma parte dos objetos temporários com o plano, se houver memória suficiente. Isso pode explicar a lenda de que objetos temporários existem apenas na memória. A verdade como sempre é que depende ….

Muitas outras coisas acontecem no TempDB: O mecanismo de banco de dados pode usá-lo para colocar tabelas de trabalho para verificações de DBCC, para criar ou reconstruir índices, cursores, por exemplo. Tabelas intermediárias em consultas descritas como ‘hashes’, ‘sorts’ e ‘spools’ são materializadas em TempDB, por exemplo, junto com aquelas necessárias para várias operações ‘físicas’ na execução de instruções SQL. Ele também é usado como um armazenamento de versão para isolamento de instantâneo, conjuntos de resultados ativos múltiplos (MARS), gatilhos e construção de índice online.

Como as tabelas temporárias são armazenadas como tabelas de base, há uma ou duas coisas que você precisa ter cuidado. Você deve, por exemplo, ter permissão CREATE TABLE em TempDB para criar uma tabela normal. Para evitar problemas, isso é atribuído por padrão à função DBO (db owner), mas você pode precisar fazer isso explicitamente para usuários que não foram atribuídos à função DBO. Todos os usuários têm permissão para criar tabelas temporárias locais ou globais em TempDB porque isso é atribuído a eles por meio do GUEST contexto de segurança do usuário.

A tabela temporária clássica vem em dois sabores, a tabela temporária global ou compartilhável, prefixada por ##, e a tabela temporária local, cujo nome é prefixado por #. As tabelas temporárias locais são menos semelhantes às tabelas normais do que as tabelas temporárias globais: não pode criar visualizações neles ou associar gatilhos a eles. É um pouco complicado descobrir qual processo, sessão ou procedimento os criou. Nós vamos te ajudar com isso mais tarde. Mais importante ainda, eles são mais seguros do que uma tabela temporária global, pois apenas o processo proprietário pode vê-la.

Outra estranheza da tabela temporária local (e do procedimento armazenado temporário local) é que ela tem um nome diferente nos metadados para aquele que você fornece em sua rotina ou lote. Se a mesma rotina for executada simultaneamente por vários processos, o Mecanismo de Banco de Dados precisa ser capaz de distinguir entre as tabelas temporárias locais com nomes idênticos criadas pelos diferentes processos. Ele faz isso adicionando uma string numérica a cada nome de tabela temporária local preenchido com caracteres de sublinhado à esquerda. Embora você especifique o nome abreviado como #MyTempTable, o que é realmente armazenado em TempDB é composto pelo nome da tabela especificado na instrução CREATE TABLE e o sufixo. Por causa desse sufixo, os nomes das tabelas temporárias locais devem ter 116 caracteres ou menos.

Se estiver interessado em ver o que está acontecendo, você pode ver as tabelas em TempDB da mesma forma que faria com qualquer outro tabela. Você pode até mesmo usar sp_help trabalhar em tabelas temporárias apenas se você as invocar a partir do TempDB.

ou você pode encontrá-los nas visualizações do sistema do TempDB sem alternar os bancos de dados.

Ou o esquema de informação

Melhor ainda, você pode descobrir qual processo, e usuário, está segurando enormes tabelas temporárias em TempDB e se recusando a ceder o espaço

Você não pode usar tipos de dados definidos pelo usuário em tabelas temporárias a menos que os tipos de dados existam em TempDB; isto é, a menos que os tipos de dados tenham sido criados explicitamente

Tabelas de usuário em TempDB

Em uso normal, você criará tabelas temporárias ou variáveis de tabela sem pensar muito a respeito. Porém, é interessante que TempDB esteja lá para qualquer tipo de atividade de sandbox. Você pode criar tabelas básicas comuns, visualizações ou qualquer outra coisa que desejar. Você pode criar esquemas, procedimentos armazenados e assim por diante. É improvável que você queira fazer isso, mas certamente é possível, já que TempDB é apenas outro banco de dados. Eu só tive que reiniciar meu SQL Server de desenvolvimento depois de provar isso para mim mesmo instalando AdventureWorks nele. Isso significa que é possível criar uma tabela base no TempDB, uma espécie de .. er… tabela permanente temporária. Ao contrário da mesa temporária global, você teria que fazer todas as suas próprias tarefas domésticas: você está sozinho. O mesmo se aplica às rotinas. A vantagem de fazer isso é que qualquer processamento que você fizer usa a recuperação simples do TempDB para que, se você não conseguir limpar, o SQL Server atue como mãe na próxima inicialização: embora isso possa levar muito tempo. A próxima etapa é ter o que chamo de mesa “temporária persistente”. Nesta tabela, os próprios dados são voláteis quando o servidor é reiniciado, mas a própria tabela persiste. Provavelmente, a maneira mais comum de criar uma tabela temporária persistente é recriar na inicialização uma tabela temporária global. Isso pode ser feito automaticamente quando todos os bancos de dados são recuperados e a mensagem “Recuperação concluída” é registrada. Mesmo que seja um temporário global, não é excluído quando todas as conexões que o utilizam tenham desaparecido, porque o processo que o executa nunca desaparece. Indiscutivelmente, é melhor criar este tipo de tabela de trabalho no banco de dados que a utiliza, embora, se você estiver usando a recuperação completa, o trabalho temporário permanecerá no log. Você pode, é claro, apenas criar um tabela em TempDB. Você pode criar essas tabelas persistentes na inicialização, definindo um procedimento armazenado no mestre que cria a tabela temporária global

Por que usar esse tipo de tabela híbrida? Existem, por exemplo, vários de técnicas para passar tabelas entre procedimentos por meio de tabelas “persistentes” de maneira multiprocesso segura, de modo a fazer uma série de processamento para os dados. Elas são chamadas de tabelas com chave de processo (consulte Como compartilhar dados entre procedimentos armazenados : Tabela codificada por processo por Erland Sommarskog ) Eles irão inicialmente levantar as sobrancelhas de qualquer DBA experiente, mas eles são uma solução eficaz e segura para um problema perene, quando feitos corretamente.

Assim como as tabelas temporárias, também existem vários tipos de tabelas que não são derivados diretamente de tabelas básicas, como tabelas falsas e tabelas derivadas: algumas delas são tão fugazes que é melhor considerá-las efêmeras em vez de temporárias. O CTE usa tabelas efêmeras que são ‘inline’ ou ‘derivadas’ e não são materializadas. BOL se refere a eles como “conjuntos de resultados nomeados temporários”. Eles existem apenas dentro do escopo da expressão. Em um CTE, eles têm a vantagem sobre as tabelas derivadas, pois podem ser acessados mais de uma vez.

Tabela temporária local

Com a tabela temporária local (nomes que começam com #), o que acontece nos bastidores é surpreendentemente semelhante às variáveis da tabela. Tal como acontece com as variáveis de tabela, as tabelas temporárias locais são privadas para o processo que as criou. Portanto, eles não podem ser usados em visualizações e você não pode associar gatilhos a eles.

Eles são mais úteis do que variáveis de tabela se você gosta de usar SELECT INTO para criá-los, mas estou um pouco cauteloso sobre o uso de SELECT INTO em um sistema que provavelmente exigirá modificação, prefiro criar minhas tabelas temporárias explicitamente, junto com todas as restrições necessárias.

Você não pode dizer facilmente qual sessão ou procedimento foi criado essas tabelas. Isso ocorre porque, se o mesmo procedimento armazenado for executado simultaneamente por vários processos, o Mecanismo de Banco de Dados precisa ser capaz de distinguir as mesmas tabelas criadas pelos diferentes processos. O Mecanismo de Banco de Dados faz isso anexando internamente um sufixo numérico preenchido à esquerda a cada nome de tabela temporária local. O nome completo de uma tabela temporária conforme armazenado na exibição sys.objects em TempDB é composto pelo nome da tabela especificado na instrução CREATE TABLE e o sufixo numérico gerado pelo sistema. Para permitir o sufixo, o nome da tabela especificado para um nome temporário local deve ter menos de 116 caracteres.

Você obtém manutenção com tabelas temporárias locais; eles são eliminados automaticamente quando saem do escopo, a menos que sejam eliminados explicitamente usando DROP TABLE. Seu escopo é mais generoso do que uma variável de tabela, então você não tem problemas para referenciá-los em lotes ou em SQL dinâmico. As tabelas temporárias locais são eliminadas automaticamente no final da sessão ou procedimento atual. Descartá-lo no final do procedimento que o criou pode causar desconforto: uma tabela temporária local que é criada dentro de um procedimento armazenado ou sessão é descartada quando é concluída para que não possa ser referenciada pelo processo que chamou o procedimento armazenado que criou a mesa. Ele pode, entretanto, ser referenciado por quaisquer procedimentos armazenados aninhados executados pelo procedimento armazenado que criou a tabela. Se o procedimento aninhado fizer referência a uma tabela temporária e duas tabelas temporárias com o mesmo nome existirem naquele momento, em qual tabela a consulta é resolvida?

Como curiosidade, você também pode criar Procedimentos armazenados temporários locais com o mesmo escopo e tempo de vida que uma tabela temporária local. Você não pode fazer o mesmo para outras rotinas.

Tabelas temporárias globais.

Como as tabelas temporárias locais, as tabelas temporárias globais (elas começam com ##) são automaticamente eliminadas quando a sessão que criou a tabela termina: No entanto, porque as tabelas globais não são privados para o processo que os criou, eles devem persistir a partir de então até que a última instrução Transact-SQL que estava fazendo referência ativa à tabela no momento em que a sessão de criação terminou tenha concluído a execução e os bloqueios sejam eliminados. Qualquer pessoa que tenha acesso a TempDB no momento em que essas tabelas temporárias globais existem pode consultar, modificar ou descartar diretamente esses objetos temporários.

Você pode associar regras, padrões e índices a tabelas temporárias, mas não pode criar visualizações em tabelas temporárias ou associar gatilhos a eles. Você pode usar um tipo de dados definido pelo usuário ao criar uma tabela temporária apenas se o tipo de dados existir em TempDB

Os procedimentos armazenados podem fazer referência a tabelas temporárias que são criadas durante a sessão atual. Em um procedimento armazenado, você não pode criar uma tabela temporária, descartá-la e, em seguida, criar uma nova tabela temporária com o mesmo nome.

Embora isso funcione …

… isso não funciona t

Você pode, usando tabelas temporárias locais, forçar involuntariamente a recompilação no procedimento armazenado toda vez que for usado. Isso não é bom porque o procedimento armazenado provavelmente não terá um bom desempenho. Para evitar a recompilação, evite referir-se a uma tabela temporária criada em uma chamada ou procedimento armazenado chamado: Se você não puder fazer isso, coloque a referência em uma string que será executada usando o EXECUTE instrução ou sp_ExecuteSQL procedimento armazenado. Além disso, certifique-se de que a tabela temporária seja criada no procedimento armazenado ou gatilho antes de ser referenciada e eliminada após essas referências.Não crie uma tabela temporária dentro de uma instrução de controle de fluxo, como IF... ELSE ou WHILE.

Você tem permissão para criar procedimentos armazenados temporários globais, mas ainda não encontrei um uso para eles. Funções temporárias globais não são permitidas.

Conclusões

Em qualquer playground compartilhado, tenha muito cuidado ao balançar o bastão. Você terá percebido, enquanto lê isto, que muitas atividades acontecem no TempDB, e você pode causar estragos em todo o SQL Server usando processos de longa execução que preenchem tabelas temporárias, de qualquer tipo, com quantidades desnecessárias de dados. Na verdade, eu dei a você dicas neste artigo sobre como realmente, realmente, perturbar seu DBA pelo uso descuidado desse precioso recurso compartilhado, o TempDB. (Antigamente, antes de S2005, usar SELECT INTO com uma mesa enorme era a grande arma V (Vergeltungswaffe)

Eu sempre tenho medo de fornecer mais conselho generalizado, mas eu sempre prefiro que meus bancos de dados usem variáveis de tabela e TVPs sempre que possível. Eles exigem menos recursos e é menos provável que você os mantenha quando terminar de usá-los. Eu gosto de usá-los ao máximo , com verificações e restrições de coluna e tabela. Você pode encontrar momentos em que eles perdem o fôlego, especialmente quando os tamanhos das tabelas ficam maiores. Em casos como este, ou onde não é prático usar variáveis de tabela devido ao seu escopo restrito, então Usarei tabelas temporárias locais. É preciso apertar os lábios e balançar a cabeça antes de concordar com uma tabela temporária global ou tabela temporária persistente. Eles têm alguns usos válidos e perfeitamente razoáveis, mas confiam no programador para fazer a limpeza necessária