Quando um índice nonclustered encontra-se em uma tabela sem índice clustered, ele fará uso do que chamamos de RowID, rowid é um código que define o posicionamento físico das linhas dentro do arquivo de dados. Porém, quando existe índice clustered em uma tabela, o índice nonclustered utiliza a chave do índice clustered para localizar as linhas na tabela e não o rowID. O uso da chave do índice clustered é bem mais eficiente do que o uso do rowID. Por isso, fica aqui a recomendação de que sempre que possível crie um índice clustered em suas tabelas.
Agora imagine uma situação em que você deseja eliminar e recriar o índice clustered de uma tabela. Se a tabela for pequena o problema não será notado, mas em tabelas com grandes volumes o consumo de CPU para a realização desta tarefa é gigantesco.
Ao eliminar o índice clustered de uma tabela, o SQL Server precisa eliminar também todos os índices nonclustered e posteriormente recriá-los, pois como eles estavam usando a chave do índice clustered, precisarão agora passar a usar o rowID no índice nonclustered.
Terminada essa longa tarefa, você irá então criar novamente o índice clustered com as suas alterações. Este processo de nova criação do índice clustered fará com que todos os índices nonclustered mais uma vez sejam eliminados e recriados, com o objetivo de mais uma vez, trocar o rowID que está sendo usando, pela chave do novo índice clustered.
Embora o processo de eliminação e recriação de um índice clustered pareça simples, podemos perceber que quando realizado em tabelas muito grandes e principalmente em tabelas que possuem um grande número de índices nonclustered, pode ser bem dispendioso em custo de CPU.
Uma alternativa para minimizar este impacto no servidor SQL Server, é fazer a criação do novo índice clustered usando a cláusula DROP_EXISTING do comando CREATE INDEX. Com essa cláusula, você não precisará eliminar o índice clustered existente utilizando o comando DROP INDEX para depois recriá-lo com o comando CREATE INDEX. Ao utilizá-la você estará dizendo ao SQL Server que está substituindo o índice existente por um novo com novas configurações. Isso permite ao SQL Server fazer uma transição automática dos índices nonclustered, diretamente do índice clustered antigo para o índice clustered novo, evitando assim o consumo de tempo de CPU com a passagem dos índices nonclustered para rowid e depois, de volta para a nova chave do índice clustered.
Para utilizar a cláusula drop_existing faça como o exemplo abaixo, onde criamos um índice na tabela authors2 do banco de dados Pubs e depois o recriamos usando a cláusula drop_existing de forma a adicionar uma nova coluna ao índice:
USE pubs
GO
CREATE CLUSTERED INDEX au_id_ind
ON authors2 (au_id)
WITH FILLFACTOR = 80
GO
sp_helpindex authors2
index_name index_description index_keys
————— ——————————– —————–
au_id_ind clustered located on PRIMARY au_lname
aunmind nonclustered located on PRIMARY au_lname, au_fname
— Recria o índice CLUSTERED utilizando a cláusula DROP_EXISTING
CREATE CLUSTERED INDEX au_id_ind
ON authors2 (au_id,au_fname)
WITH DROP_EXISTING
sp_helpindex authors2
index_name index_description index_keys
————– ——————————– —————–
au_id_ind clustered located on PRIMARY au_id, au_fname
aunmind nonclustered located on PRIMARY au_lname, au_fname
Nota: Para mais informações sobre a cláusula DROP_EXISTING consulte o BOL do SQL Server 2000