O item índice de tabelas no servidor SQL Server é um dos itens mais importantes que podemos estudar no campo de Bancos de dados.
Toda vez que você faz uma pesquisa em um banco de dados busca uma 'resultado'. O índice é um atalho para esse resultado.
Se você faz uma pesquisa numa tabela a procura de um resultado o sql processa a informação e como ele não sabe exatamente onde a informação está ele faz um 'table-scan', ou seja, procura do começo ao fim dos dados da tabela e seleciona os que pertencem ao critério de seleção da pesquisa.
Se a tabela que estamos fazendo a pesquisa tem poucos registros a pesquisa irá demorar pouco e o índice pode não ser necessário.
Mas se sua tabela tem mais de 100.000 registros, pode ficar tranquilo que um índice irá ajudar muito a sua pesquisa.
Eu tenho uma tabela com milhares de itens e antes de fazer um 'insert' nela quero verificar se os dados que pretendo inserir já não estão cadastrados na tabela. Mesmo no caso de cada registro de dados ser único ( não se repete ) pode ser que eu rodei a app antes e carreguei um monte de dados e alguém, por exemplo, reiniciou o servidor e minha aplicação caiu. No reprocesso da aplicação eu tenho que 'dropar' as informações carregadas anteriores, por uma data, por exemplo e repetir o processo de carga novamente ou posso verificar se já existe a info cadastrada na tabela e se não houver faço a inserção.
Supondo que você escolha o segundo caso ( verificar se a info já foi cadastrada e se não foi dar o 'insert') cada pesquisa feita para verificar que o dado ainda não foi cadastrado geraria uma 'table scan' para cada pesquisa e se a tabela tem milhares de itens faria com que essa consulta demorasse algo de meio segundo...se você está inserindo 30.000 registros e cada pesquisa demora 0,5 segundos significa que só a pesquisa para não duplicar os registros demoraria 30.000 regs * 0,5 segundos cada daria um total de 15.000 segundos o que daria mais de 4 horas só nessa pesquisa, fora o tempo do insert.
Na pesquisa acima onde você verifica se o dados já foi cadastrado anteriormente selecione o item chave da pesquisa na clausula 'where'. Por exemplo, pelo CPF da pessoa. Vá no SQL Server Management Studio, na tabela da pesquisa, abra o + do lado esquerdo da tabela e selecione 'Índices'. Clique com o botão direito do mouse em 'Índices' e escolha 'novo índice'.
Você deve criar um índice 'clusterizado' quando um ou mais campos da tabela é chave de muitas pesquisas e essa coluna ou conjunto de colunas são de alta seletividade , ou seja, retornam poucos registros. Você só pode criar um índice clusterizado porque os dados armazenados na tabela serão reordenados e o índice criado será colocado junto com os dados da tabela para evitar mais 'acessos ao disco' para obter a informação final desejada.
Caso haja pesquisas feitas em outros campos da tabela você pode criar um índice não clusterizado para agilizar a pesquisa. Lembre-se sempre que um índice pode ser feito por uma única coluna da tabela ou por um conjunto de colunas da tabela .
Cuidado : Se você fizer um índice pela coluna A,B,C de uma tabela e na pesquisa fizer, na cláusula 'where', a pesquisa pelos campos A,C,B o índice NÃO será usado . O motivo é simples, a ordem das colunas determinam o agrupamento a ser feito pelos dados e o índice foi definido para um agrupamento específico de colunas que, neste exemplo (ABC versus ACB), não atende a sua pesquisa.
Eu estava carregando dados numa tabela que foi crescendo e crescendo. Do jeito que estava cada 2 DIAS eu conseguia carregar um mês de dados. Criando o índice cada 2 HORAS eu consegui carregar um mês de dados. Excelente exemplo do ganho feito ao criar um índice.
Lembre-se que a cada índice criado o sql server tem que refazer o índice a cada insert na tabela. Se você criar muitos índices pode ser que o trabalho do sql server de dar manutenção nesses índices seja maior que o ganho que eles dão aos resultados.
Como temos boa experiência no assunto sugerimos que veja o tamanho do arquivo de banco de dados da tabela contra o tamanho do índice. O índice jamais deverá ter um tamanho maior que a metade do tamanho dos dados da tabela .
A resposta é sim...ele dá um ganho na pesquisa, mas insere uma perda no insert porque exige a manutenção do índice a cada insert e se o fill-factor da tabela for baixo pode ser que os inserts causem muitos 'page split's o que aumenta mais ainda o custo da manutenção da tabela e dos índices dela.
Já tive processos com grandes massas de dados que precisavam ser inseridas o mais rápido possível. Quando digo grande é porque era grande mesmo. Neste caso simplesmente desliguei o índice, executei todos os 'inserts' e no final liguei o índice novamente.
Importante: Caso necessite você pode forçar a reconstrução dos índices de uma tabela no SQL Server use o comando DBCC DBREINDEX. Lembre-se que este comando reconstruí todos os índices de uma tabela a não ser que você selecione um índice específico pelo nome.
O que acaba miseravelmente com um índice é a inclusão de campos com valores null neles. Neste caso é melhor criar o índice com as outras colunas e deixar essa ( com null ) de fora.