Índices no SQL Server e no meu livro
Você acabou de comprar meu livro, e de imediato precisa achar o capítulo sobre modelagem de dados. Você vai olhar no índice e ver que este assunto está na página 20. E em seguida abrirá o livro exatamente nesta página.
Sem o índice você precisaria percorrer todas as páginas do livro até encontrar esta informação… E acharia! Mas ia demorar muito mais.
Em um livro ou em uma tabela o índice existe para acelerar suas consultas. Simples assim!
Você deve estar se perguntando… Então eu devo criar índices para acelerar todas as consultas que eu faço no SQL Server? A resposta é NÃO (leia meu grito desesperado…)! Os índices aceleram as consultas e mas diminuem o desempenho das inclusões.
Para você entender isso, vamos definir o que é um índice usando a documentação da Microsoft , que traz uma definição linda: “Um índice é uma estrutura em disco associada a uma tabela ou view, que agiliza a recuperação das linhas procuradas. Um índice contém chaves criadas de uma ou mais colunas da tabela ou view. Essas chaves são armazenadas em uma estrutura (B-Tree) que habilita o SQL Server a localizar a linha ou as linhas associadas aos valores de chave de forma rápida e eficaz.”
Então cada índice é uma estrutura separada da tabela ou view. Sendo assim a cada inclusão o SQL Server precisa atualizar a tabela e também o índice. Consegue entender o trabalho dobrado, e ver que essa etapa a mais pode deixar as inclusões mais lentas?
Sendo assim minha dica de ouro é: use os índices, mas analise qual a operação principal para o seu projeto para saber se ele realmente será útil. Na dúvida, é hora de conversar com a sua equipe e incluir o DBA na conversa.
Índice Cluster
Uma tabela pode ter somente um índice cluster. Essa limitação existe porque este tipo de índice classifica e armazena as linhas da tabela com base nos valores da coluna escolhida para fazer parte do índice, e as linhas só podem ser classificadas em uma única ordem. Se você escolheu o código de usuário para ser o índice cluster da sua tabela de usuário, então todos os registros da sua tabela estarão ordenados pelo código do usuário,
É importante você saber que um índice cluster pode ter uma ou várias colunas, e que uma tabela sem índice cluster é chamada de HEAP.
A sintaxe para criar um índice é muito simples… Você precisa “dizer” para o SQL Server o seguinte: Crie um índice cluster chamado IDX_USUARIO na tabela USUARIO e que contenha a coluna Código do Usuário.
Traduzindo para o T-SQL ficaria assim:
CREATE CLUSTERED INDEX IDX_USUARIO ON USUARIO (COD_USUARIO); GO
Simples assim!
Índice Não-Cluster
Um índice não-cluster é uma estrutura de índice separada dos dados armazenados em uma tabela. Eles costumam ser uma forma mais rápida de localizar dados do que a busca na tabela sem índice. Geralmente, os índices não-cluster são criados para aprimorar o desempenho de consultas realizadas com frequência, e que contém colunas diferentes daquelas que fazem parte do índice cluster, ou para localizar linhas em uma heap. Você pode criar vários índices não cluster emuma tabela.
Para criar um índice cluster temos que dizer para o SQL Server: Crie um índice não-cluster chamado IDX01_USUARIO na tabela USUARIO e que contenha a coluna Data de Nascimento.
Traduzindo para o T-SQL ficaria assim:
CREATE NONCLUSTERED INDEX IDX01_USUARIO ON USUARIO (DT_NASCIMENTO); GO
Este tipo de índice pode ter duas particularidades bacanas:
- Índice filtrado – Determina que só os valores que obedecem a uma determinada condição devem ser indexados. Por exemplo, na nossa tabela de usuários, queremos filtrar somente os registros da nossa filial (SP).
CREATE NONCLUSTERED INDEX IDX03_USUARIO ON USUARIO (SIGLA_FILIAL) WHERE SIGLA_FILIAL = ‘SP’; GO
- Índices com colunas incluídas- Vamos voltar para o exemplo do meu livro. Imagine que você está procurando o capítulo sobre modelagem de dados para bancos de dados orientado a documentos. No índice temos a informação principal que você vai usar para achar o capítulo que te interessa, mas na linha abaixo existe um curto resumo falando que aquele capitulo fala sobre a modelagem de banco de dados para BD orientado a documentos. Ou seja, o índice é feito pelo nome do capítulo, mas junto com a coluna indexada existe também um adendo, importante para a localização da informação procurada. Assim são os índices com colunas incluídas, a tabela é indexada por uma coluna (ou um conjunto de colunas), mas junto com o índice estão também outras colunas. Este tipo de índice normalmente é usado para consultas frequentes, onde a coluna indexada é usada no WHERE e as colunas incluídas são usadas no SELECT 😊
Voltando para o nosso exemplo da tabela de USUÁRIOS, suponha que uma das suas principais consultas é aquela que trás o nome do usuário, gênero, e RG daqueles que tem uma determinada nota.
CREATE NONCLUSTERED INDEX IDX04_USUARIO ON USUARIO (NUM_NOTA) INCLUDE (NOME_USUARIO, SIGL_GENERO, COD_RG); GO
Índices Exclusivos
Determina que os valores de uma determinada coluna devem ser únicos.
Por exemplo, na nossa tabela de Usuários o índice cluster é o código do usuário. Mas precisamos garantir que o CPF não se repita, e para isso podemos criar um índice não-cluster único com a coluna CPF
CREATE UNIQUE INDEX IDX02_USUARIO ON USUARIO (COD_CPF); GO
Dando um passo além
Eu não ia abordar esse assunto neste post…. mas não resisti!
Uma PK é um índice cluster?
Você já se fez esta pergunta? Ou este post despertou esta curiosidade?
Vamos conversar um pouquinho…
Primeiro ponto… Você lembra o que é a primary key (PK)? Se você não lembra, é uma coluna (ou conjunto de colunas) que identifica unicamente uma linha da sua tabela.
Por padrão, o SQL Server cria a sua PK como índice cluster exclusivo. Mas pode ser que este comportamento não faça sentido para a sua aplicação, e você pode alterar este comportamento, criando a sua PK como índice noncluster.
Veja no nosso exemplo da tabela de usuários, onde a PK é a coluna COD_LEGADO.
CREATE TABLE [USUARIO]( [COD_LEGADO] [bigint] NOT NULL PRIMARY KEY NONCLUSTERED, [COD_USUARIO] [int] NOT NULL, [NOME_USUARIO] [varchar](100) NOT NULL, [DT_NASCIMENTO] [date] NOT NULL, [COD_CPF] [varchar](13) NOT NULL, [SIGLA_FILIAL] [char](2) NULL, [NUM_NOTA] [int] NULL, [SIGL_GENERO] [char](1) NULL, [COD_RG] [int] NULL );
Conclusão
Índice bem criado é vida!
Você que é desenvolvedor pode sugerir/criar índices e com isso ter excelentes ganhos de desempenho na sua aplicação. Mas como eu já disse, crie com cuidado para que eles não sejam um problema quando deveriam ser a solução.
Referências
Saiba mais sobre SQL Server 2017
7 itens que você precisa saber para escrever um comando SELECT
Bem vindo SQL Server 2017! Termos importantes e Restore
Scripts
Os scripts estão disponíveis no github e aqui também 😊
--CRIAR A TABELA DE USUÁRIOS CREATE TABLE [USUARIO]( [COD_LEGADO] [bigint] NOT NULL PRIMARY KEY NONCLUSTERED, [COD_USUARIO] [int] NOT NULL, [NOME_USUARIO] [varchar](100) NOT NULL, [DT_NASCIMENTO] [date] NOT NULL, [COD_CPF] [varchar](13) NOT NULL, [SIGLA_FILIAL] [char](2) NULL, [NUM_NOTA] [int] NULL, [SIGL_GENERO] [char](1) NULL, [COD_RG] [int] NULL ); --CRIAR UM ÍNDICE CLUSTER CREATE CLUSTERED INDEX IDX_USUARIO ON USUARIO (COD_USUARIO); GO --CRIAR ÍNDICE NÃO-CLUSTER CREATE NONCLUSTERED INDEX IDX01_USUARIO ON USUARIO (DT_NASCIMENTO); GO --CRIAR ÍNDICE ÚNICO CREATE UNIQUE INDEX IDX02_USUARIO ON USUARIO (COD_CPF); GO --CRIAR ÍNDICE FILTRADO CREATE NONCLUSTERED INDEX IDX03_USUARIO ON USUARIO (SIGLA_FILIAL) WHERE SIGLA_FILIAL = 'SP'; GO --CRIAR ÍNDICE COM COLUNAS INCLUÍDAS CREATE NONCLUSTERED INDEX IDX04_USUARIO ON USUARIO (NUM_NOTA) INCLUDE (NOME_USUARIO, SIGL_GENERO, COD_RG); GO