Este caso aconteceu na avaliação de um script SQL onde um simples caractere adicional no nome de um índice acabou causando muita discussão. O fato é que a documentação é um tanto vaga no quesito nomenclatura de índices, dando margem a entendimentos diferentes. Este artigo apresenta o estudo que foi feito para esclarecer a situação.

Sobre Índices Temporários

Primeiramente, é importante discutir caso a caso a real necessidade de criação de índices em tabelas temporárias. Eu mesmo já publiquei aqui um artigo sobre este tema anos atrás.

Índices em tabelas temporárias precisam ser muito bem avaliados para se certificar que o tempo de criação do índice vai compensar o ganho de performance nas consultas que usarão este índice. 

Sim, consultas, no plural. Entendo que os casos em que o índice seja usado em uma única consulta antes de ser destruído são ainda mais difíceis de se justificar.

O Caso

Toda discussão no caso em questão começou porque o desenvolvedor criou uma tabela normal e definiu um índice com o nome começando com o caractere “#”. O quadro a seguir mostra um exemplo. Observe a definição do índice na linha 17.

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

CREATE TABLE tab ( coluna1 int NOT NULL, coluna2 int NOT NULL, coluna3 varchar(50) , coluna4 int , coluna5 int , coluna6 varchar(50) , coluna7 varchar(50) , coluna8 int , coluna9 varchar(50) , coluna10 int  , coluna11 varchar(50) , coluna12 int  )  GO CREATE NONCLUSTERED INDEX [#ix_tab] ON tab ( coluna1 ASC, coluna2 ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TempDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  GO

Alguém me questionou se este índice era ou não um índice temporário. Confesso que fiquei em dúvida, mas meu palpite inicial foi que este seria sim um objeto temporário visto que usava a mesma nomenclatura de objetos temporários.

Esse detalhe pode parecer bobagem, mas a questão é importante: objetos temporários são válidos apenas na sessão em que eles foram criados. Mesmo que sejam objetos temporários globais (reconhecidos em todas as sessões) serão destruídos quando a instância for reiniciada, visto que todo TempDB será reconstruído neste caso. E o “desaparecimento” de um índice em ambiente de produção poderia causar problemas sérios.

Consultei vários colegas e percebi que cada um tinha uma opinião distinta, baseada mais no entendimento pessoal do que em qualquer documentação oficial.

Então decidi estudar o assunto.

Teste 1: Onde Está o Índice

A primeira constatação foi que o índice “#ix_tab” de fato era listado entre os índices do meu banco de dados.

Outra informação interessante é que o índice é listado no relatório padrão de Estatísticas Físicas do Índice no meu banco de dados. Naturalmente, era de se esperar que um objeto temporário não fosse gravado no meu banco e sim no TempDB.

Portanto a tabela “tab” e o índice “#ix_tab” pertencem ao banco de dados myPreciousNew, usando, portanto, seus filegroups e datafiles.

Teste 2: O Que Acontece quando a Instância É Reiniciada

Como se sabe, quando a instância SQL é reiniciada, o TempDB é recriado, perdendo todos os objetos que estivessem gravados ali.

Então, por segurança, reiniciei minha instância e repeti o Teste 1, obtendo exatamente os mesmos resultados. Assim confirmei que o índice “#ix_tab” era sem nenhuma dúvida um índice permanente, ao contrário da minha suspeita inicial.

Teste 3: Como se Comportam Índices de Tabelas Temporárias

Como se sabe, quando se cria uma tabela temporária, ela é armazenada no TempDB. E quando esta tabela é eliminada, qualquer objeto associado a ela, inclusive índices, também serão excluídos.

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

CREATE TABLE #tmpTab ( coluna1 int NOT NULL, coluna2 int NOT NULL, coluna3 varchar(50) , coluna4 int , coluna5 int , coluna6 varchar(50) , coluna7 varchar(50) , coluna8 int , coluna9 varchar(50) , coluna10 int  , coluna11 varchar(50) , coluna12 int  )  GO CREATE NONCLUSTERED INDEX [ix_tmpTab] ON #tmpTab ( coluna1 ASC, coluna2 ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TempDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  GO

Então verifiquei as estatísticas de armazenamento deste novo índice, como mostra o quadro a seguir.

O relatório confirma que tanto a tabela temporária como seu índice são armazenados no TempDB. Na realidade, o índice “ix_tmpTab” não é tecnicamente temporário, mas associado a um objeto temporário, reproduzindo o comportamento do objeto pai.

Teste 4: Criação de Procedimentos Temporários

Procedimentos armazenados criados com o prefixo “#”, assim como as tabelas, são objetos temporários armazenados no TempDB.

Observe no quadro a seguir que o novo procedimento não está catalogado na tabela “sys.objects” do meu banco de dados e sim no TempDB.

Teste 5: Criação de Visões Temporárias

Não existe a ideia de visões temporárias no SQL Server. Aliás, não vejo por que haveria de existir, visto que temos outros recursos, como tabelas temporárias e CTEs, que tem o mesmo comportamento de uma teórica “visão temporária”.

Porém a nomenclatura das visões segue o padrão das tabelas: nomes com prefixo “#” se referem a objetos temporários. Por isso recebemos uma mensagem de erro ao tentar criar uma visão com este prefixo. Veja o quadro a seguir.

Comentários Finais

Entendo que este estudo nos permite chegar a algumas conclusões importantes. Entre elas:

1. As regras de nomenclatura dos índices não são iguais às tabelas, procedimentos e visões.
2. Não existe o conceito de índice temporário no SQL Server. Índices são vinculados a tabelas e são automaticamente excluídos quando estas tabelas são eliminadas.