Índices no SQL Server 2000

Esta semana(No ano de 2008) me deparei com um problema de otimização e então li muito a respeito. Hoje falarei de índices.
Antes de falarmos sobre os índices, falaremos como os dados no SQL Server são armazenados e como os mesmo são acessados.

Armazenamento de dados
Uma pilha é uma coleção de páginas de dados:
• Cada página contém 8 quilobytes (KB) de informações.
• Quando os registros são inseridos em uma página, e ela já está cheia, as páginas de dados são divididas.
• Um grupo de 8 páginas adjacentes é chamado de extensão.

Acesso aos Dados
O SQL Server acessa os dados de dois métodos.
• Examinando todas as páginas de dados das tabelas – chamado de exame de tabela. Ao executar um exame de tabela o SQL Server:
• Começa no inicio da tabela.
• Examina todos os registros da tabela, página a página.
• Extrai os registros que satisfazem os critérios da consulta.

• Usando índices. Ao usar um índice, o SQL Server:
• Percorre a estrutura da árvore do índice para localizar os registros solicitados pela consulta.
• Extrai apenas os registros necessários que satisfazem os critérios da consulta

Como Criar Índices?

Ao criar índices, leve em conta dois fatores: a natureza dos dados e a natureza das consultas realizada nas tabelas.
Natureza dos dados – Quando me refiro a natureza de dados, sempre levo em conta o tipo da coluna em que colocarei um índice, é recomendável que índices estejam em colunas do tipo: int, char, bigint, tinyint, smallint e datetime.
Natureza das consultas – As naturezas das consultas se referem em qual campo da minha tabela devo criar um índice, exemplo: Imagine um cenário que tenho que criar uma consulta que traga os pedidos emitidos por data de emissão e que a situação seja somente os pedidos liberados. Naturalmente na minha tabela de pedidos eu teria o campo data de emissão e situação do pedido. Essas são colunas aconselháveis para a criação de índices.
Os índices são úteis, porém consomem espaço em disco e acarretam custos de manutenção e sobrecarga. Não crie um índice que não será usado com freqüência.

Índice Clustered ou Agrupamento
Esse tipo de índice é útil para as colunas pesquisadas com freqüência ou em busca de chave de valores. Ao criar um índice desse tipo, considere as seguintes diretrizes:
• Cada tabela só pode ter um índice clustered.
• A ordem física dos registros da tabela e a ordem dos registros do índice são iguais. Primeiramente é aconselhável que seja criado o indice clustered antes do índice não clusterizado.
• Quando um registro é excluído, o espaço é restaurado e torna-se disponível para outro registro.
• Quando se cria uma coluna como PRIMARY KEY, automaticamente essa coluna torna-se um índice clustered.
• O tamanho médio de um índice clustered é aproximadamente 5% do tamanho da tabela. No entanto, esse tamanho varia dependendo do tamanho da coluna indexada.

Índice No-Clustered ou Sem Agrupamento
Um índice não clusterizado é eficiente quando os usuários precisam de vários critérios de pesquisa. Por exemplo, um vendedor pode pesquisar na tabela de pedidos pelo número do pedido, data de emissão, cliente e o representante deste cliente. Ao criar um índice não clusterizado, considere os seguintes fatos:
• A ordem das páginas no nível folha se um índice não clusterizado é diferente da ordem
• Pode existir até 249 índices não clusterizado por tabela.
• O SQL Server recria automaticamente os índices não clusterizados existentes quando ocorre uma das situações a seguir:
• Um índice clustered é criado.
• Um índice clustered é descartado.
• A opção DROP_EXISTING é usada para alterar as colunas que definem o índice de agrupamento.

Colunas que devem ser indexadas.
Crie índices em colunas pesquisadas com freqüências como:
• Chaves primárias (PK).
• Chaves externas (FK) ou colunas usadas frequentemente para unir tabelas.
• Colunas pesquisadas para a localização de faixa de valores de chave. (quando usamos a clausula IN, NOT IN, BETWEEN).
• Colunas acessadas na ordem de classificação (ORDER BY)
• Colunas usadas durante a agregação. (GROUP BY)

Colunas que NÃO devem ser indexadas.
Não referencie colunas que:
• Raramente são usadas em um consulta.
• Contenham poucos valores únicos. Por exemplo, um índice em uma coluna com dois valores, Masculino e feminino, retorna uma alta porcentagem de registros.
• Sejam definidas com os tipos de dados text, ntext, varchar e image. Não é possível indexar colunas com esses tipos de dados.

Criando índices.
Ao criar índice em uma tabela considere os fatos:
• Você deve ser proprietário da tabela.
• Quando se cria uma constraint do tipo PRIMARY KEY e UNIQUE, automaticamente o SQL Server cria índices clustered para elas.
• Você pode criar índices em VIEWS
• Você não pode criar índice em colunas que já existam os mesmos.
• Você pode criar índice na hora de criação de sua tabela.

Sintaxe:
CREATE NONCLUSTERED INDEX nomeDoIndice ON Tabela(CampoIndexado)
Apagando índice
Ao apagar um índice você deve levar em conta as seguintes diretrizes:
• Você não pode apagar os índices criados pelas restrições PRIMARY KEY e UNIQUE.
• Você não pode apagar os índices das tabelas do sistema.
• Você deve está no banco que reside o índice para apagá-lo.

Sintaxe:
DROP INDEX Tabela.NomeDoIndice

Na prática:
DROP INDEX DptoEmpresa.IdxEmpresaID

Até a próxima e espero que todos tenham gostado.
Obs: Esse post retirei de um site que havia publicado a muito tempo atras quando comecei a trabalhar com SQL Server, futuramente irei adicionar a questão de indicec om as features do SQL Server 2008