o SQL Server tabelas temporárias são um tipo especial de tabelas que são escritos no banco de dados TempDB e agir como mesas regulares, proporcionando um adequado local de trabalho, por intermédio de processamento de dados antes de guardar o resultado em uma tabela periódica, como ele pode viver apenas para a era da conexão de banco de dados.,
SQL temp tabelas podem ser usadas para melhorar o desempenho dos procedimentos armazenados, reduzindo o tempo de transação, permitindo que você prepare registros que você irá modificar na tabela temp do servidor SQL, em seguida, abrir uma transação e realizar as alterações.
Existem quatro tipos principais para o SQL temp tabelas:
-
um SQL Server Local tabela temporária, qual é o nome começando com um símbolo # (ex: #TempShipments), que pode ser referenciada apenas pelo actual sessão de banco de dados e descartado por sua desconexão,
-
um SQL Global tabela temporária, qual é o nome começando com ## (e.g., ##TempShipments), que pode ser referenciada por qualquer processo no banco de dados atual e descartadas quando o banco de dados original de sessão que criaram essa tabela temporária desconectado ou até que a última instrução que foi referência a tabela temporária parou de usar ele, como qualquer um que tenha acesso ao sistema de banco de dados TempDB quando o SQL global tabela temporária é criada será capaz de usar essa tabela,
-
um Servidor SQL Persistente tabela temporária que é chamado começando com um TempDB prefixo, tais como o TempDB.DBO.Templationments
-
and a Table Variable that starts with an @ prefix (e.g., @TempShipments)
o motor de banco de Dados SQL Server pode distinguir entre as mesmas tabelas temporárias SQL criadas ao executar o mesmo procedimento armazenado muitas vezes simultaneamente, adicionando um sufixo numérico gerado pelo sistema ao nome da tabela temp do servidor SQL. É por isso que o nome local da tabela temp SQL não pode exceder 116 caracteres.,
embora ambas as tabelas de temperatura do servidor SQL e as variáveis de tabela sejam armazenadas na base de dados do TempDB, existem muitas diferenças entre elas, tais como:
-
as tabelas de temperatura SQL são criadas usando a instrução Criar tabela T-SQL, mas as variáveis de tabela são criadas usando declaração @nome tabela T-SQL.
-
Você pode alterar as tabelas temp do servidor SQL depois de criá-lo, mas as variáveis de tabela não suportam nenhuma declaração DDL como a instrução ALTER.
-
as tabelas de temperatura SQL não podem ser usadas em funções definidas pelo Utilizador, mas as variáveis de tabela podem ser.,
-
SQL Server temporary tables honor explit transactions defined by the user, but table variables can’t participate in such transactions.
-
SQL temp tabelas de apoio a adição de cluster e não-índices de cluster após o SQL Server de criação de tabela temporária e, implicitamente, pela definição de restrição de chave Primária ou restrição Unique Key durante as tabelas de criação, mas as variáveis de tabela suporte apenas a adição de tais índices, implicitamente, pela definição de restrição de chave Primária ou restrição Unique key durante tabelas de criação.,
-
SQL temp tabelas podem ser descartadas explicitamente, mas as variáveis de tabela não pode ser descartada, explicitamente, levando-se em consideração que ambos os tipos são descartados automaticamente quando a sessão em que eles são criados é desligado.
-
o SQL Server tabelas temporárias pode ser local, temporal, tabelas em nível de lote ou procedimento armazenado no qual a tabela declarado ou global temporal tabelas onde ele pode ser chamado de fora do lote ou procedimento armazenado escopo, mas variáveis de tabela só pode ser chamado dentro do lote ou procedimento armazenado no qual ela é declarada.,
além disso, o SQL Server de nível de coluna as estatísticas são geradas automaticamente contra SQL temp tabelas, ajudando o Otimizador do SQL Server para gerar o melhor plano de execução, obter o melhor desempenho quando consultar o SQL Server tabela temporária. Mas você deve levar em consideração que modificar tabelas SQL temp muitas vezes em seu código pode levar a Estatísticas ficando fora de data. Isso exigiria atualização manual para essas estatísticas, ou habilitando Trace Flag 2371., Neste artigo, veremos como podemos nos beneficiar da capacidade de adicionar índices agrupados e não agrupados nas tabelas temp do servidor SQL.
definir a chave primária e restrições de chave únicas durante a criação da tabela temp SQL irá permitir que o Otimizador de Consulta do servidor SQL seja sempre capaz de usar esses índices. Mesmo assim, estes índices impedem a inserção de valores não-únicos nestas colunas, o que não é o melhor caso em todos os cenários, que podem exigir valores não-únicos. Neste caso, é melhor definir explicitamente um índice agrupado ou não agrupado que poderia ser configurado como um índice Não-único., Adicionar índices às tabelas de temperatura SQL irá melhorar o seu desempenho se o índice for escolhido corretamente, caso contrário, ele pode causar degradação do desempenho. Além disso, nem toda tabela temp do servidor SQL precisa adicionar índices, pois depende de muitas coisas, como a forma como esta tabela temp SQL será chamada, associada com outras tabelas enormes ou se fará parte de um procedimento complexo armazenado.,
vamos iniciar a nossa demonstração, na qual vamos testar o desempenho de preenchimento e recuperação de dados de tabelas temp do servidor SQL que contêm registros 100k, sem qualquer índice, com um índice não agrupado, e com um índice agrupado. Vamos concentrar-nos em verificar o tempo consumido por cada caso e o plano de execução gerado. Para avaliar o tempo consumido, vamos declarar a variável @StartTime antes de cada execução, definir o seu valor para GETDATE () e no final de cada execução, vamos imprimir a diferença de data (em ms) entre a hora atual e a hora de início.,
O script abaixo irá criar o anteriormente mencionado SQL três tabelas temp; temp tabela sem um índice, tabela temporária com um índice não agrupado em cluster e a tabela temporária com o índice de cluster e preenchê-lo com 100 mil registros de CountryInfo tabela de teste, em seguida, recuperar esses registros das tabelas:
Executar o script anterior, o resultado vai nos mostrar que no nosso caso, a adição de um índice não agrupado é pior do que ter a tabela sem o índice de 1.,e tempo no nosso caso, como abaixo temporização de comparação em ms:
Verificar o plano de execução gerado usando o ApexSQL Plano de aplicação após a execução, veremos que, como não temos junta-se com enormes tabelas ou consultas complexas, a recuperação de dados a partir de três tabelas consumir os mesmos recursos (1%) e diferem em que o operador é utilizado para obter os dados; Verificação de tabela, no caso de a tabela temporária sem índice, Índice de busca, no caso da tabela temporária com o índice não agrupado em cluster e pesquisa de Índice de Cluster no caso da tabela temporária com o índice de cluster.,
você Também pode derivar da execução do plano, que a tabela com o índice não agrupado levou o maior tempo (1063 ms) e recursos (47% do total da execução) durante o processo de inserção de tabela em frente à mesa, com um índice de cluster de inserção que demorou menos tempo (827 ms) e recursos (32 % do total de execução):
No script anterior, nós criamos um índice não agrupado em cluster após o preenchimento da tabela temporária e o índice de cluster antes de preencher a tabela temporária., Mas é diferente quando criamos o índice antes ou depois de encher a mesa temporária?, Para responder a esta pergunta, vamos executar o seguinte teste no qual iremos verificar o tempo consumido em todos os casos; a adição de um índice não agrupado em cluster antes de preencher a tabela temporária, a adição de um índice não agrupado em cluster após o preenchimento da tabela temporária, a adição de um índice de cluster antes de preencher a tabela temporária e a adição de um índice de cluster após o preenchimento da tabela temporária:
é claro que o resultado gerado a partir de executar o script anterior que é melhor para criar o índice não agrupado em cluster após o preenchimento da tabela, que é de 1,2% mais rápido, e cria o índice de cluster antes de preencher a tabela, que é de 2.,5% mais rápido, devido ao mecanismo que é usado para preencher as tabelas e criar índices:
Verificar o plano de execução, o resultado nos mostra que a criação de um índice agrupado antes da inserção consome 15,7% da execução global, onde a criação é após a inserção consomem 22% do total da execução., Por outro lado, a criação de índices não clusterizados após a inserção consome 23% dos recursos, em comparação com os 25% por cento que é consumido pela criação antes de o processo de inserção:
Conclusão
No SQL Server, o SQL tabelas temporárias, que são armazenados no banco de dados TempDB são amplamente usados para fornecer um local adequado para o intermediário de processamento de dados antes de guardar o resultado em uma tabela base., Ele também usou para encurtar a duração das transações de longa duração com o bloqueio mínimo da tabela de base, tomando os dados, processando-os e, finalmente, abrindo uma transação para realizar a mudança na tabela de base. Esta abordagem é aplicável para adicionar índices Não agrupados e agrupados para as tabelas temp do servidor SQL, que ambos podem melhorar o desempenho de recuperar dados dessas tabelas, se os índices são escolhidos corretamente.,
- 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.,
Também, ele está contribuindo com suas dicas de SQL em muitos blogs.
Ver todos os posts por Ahmad Yaseen
- Como monitor Azure Dados de Fábrica – em 15 de janeiro de 2021
- Usando o Controle de Origem no Azure Dados de Fábrica de 12 de janeiro, 2021
- Usando Modelos no Azure Dados de Fábrica, 8 de janeiro de 2021