Chave Primária vs Indice Unico (PK x UQ)

Olá aos visitantes!

Gostaria de dar uma dica super importante, aos que estão tendo dificuldades com índices em tabelas com muitos registros.

Todos aprendemos no processo de Normalização dos dados, a sempre criar uma chave primária (PK) em todas as tabelas do banco.

Pois bem, o que muitos não percebem, é que este é o momento mais importante na vida de uma tabela!

Tá, exagerei um pouco no transcendentalismo, mas é sério !

No momento em que estamos definindo a chave primária (PK) de uma tabela, talvez estejamos desperdiçando o índice mais importante para ela.

É de praxe criarmos uma chave primária (PK) na 1a. coluna da tabela, de Funcionários ou Clientes, por exemplo, sem nem ao menos pensar no que estamos fazendo.

Minha hipótese é que isto ainda ocorra, talvez por causa de um velho hábito que os programadores do sistema dBase (Clipper, Paradox, etc.) tinham na criação de tabelas, onde a chave primária garantiria a ordem física dos registros.

Como as tabelas do exemplo acima, teoricamente, contém poucos registros, isto não geraria muito impacto na performance.

Mas numa tabela gigante, por exemplo, com milhões de registros, a coisa fica séria.

Considere, por exemplo, uma tabela-header de Notas Fiscais de Venda fictícia, com os seguintes atributos:

Código, Data, CFOP, Cliente, Documento, Valor Total, Transportadora, Observações, etc.

Em 99% dos casos que já tive a oportunidade de visualizar a estrutura desta tabela, nos sistemas que costumo analisar em minhas consultorias, há uma chave primária (PK) definida na coluna Código.

Considero isto um erro grave, um desperdício de índice.

No SQL Server, há uma mudança de paradigma em relação ao sistema dBase, a chave primária (PK) cria automaticamente um Clustered Index, ou seja, um índice físico.

O índice físico é o mais utilizado e o mais rápido em qualquer busca de registros em uma tabela, pelo menos na maioria dos casos.

Desta forma, um índice físico em cima da coluna Código seria totalmente inútil, porque neste caso, acredito que a busca mais utilizada nos relatórios não seja nesta coluna em específico.

Se você simplesmente precisa garantir que não haja 2 registros com o mesmo código, sugiro que crie apenas um índice único nesta coluna, conforme abaixo:

CREATE UNIQUE INDEX iCodigo ON NotaFiscal (Codigo)

go

E para garantir a melhor performance nas suas buscas, considere por exemplo, as colunas Data e Cliente como as mais utilizadas nos relatórios do seu sistema.

Para elas então, crie um índice físico:

CREATE CLUSTERED INDEX iNotaFiscal ON NotaFiscal (Data, Cliente)

go

Os índices do tipo UNIQUE também permitem a criação de Chaves Estrangeiras (FOREIGN KEYS), ou seja, não perde-se assim, a 2a. principal função da chave primária.

Em um ambiente OLTP, onde ocorre muitas inclusões por minuto, ou até por segundo, sugiro uma análise mais cuidadosa, pois o tempo gasto na reorganização dos dados a cada Insert pode comprometer a estratégia, mas a dica ainda é válida.

Nos meus testes, em um ambiente controlado, a inclusão de 100 mil registros em uma tabela teste levou:

- 22 segundos: com um índice Clustered nas colunas Data e Cliente + índice Único na coluna Código;
- 27 segundos com uma Chave Primária na coluna Código.

Após a inclusão, foi realizado uma consulta básica com as colunas Data e Cliente na cláusula WHERE, e o Plano de Execução analisado:

- utilizando a estratégia sugerida, de índice Clustered nas colunas Data e Cliente, atingiu-se o objetivo esperado, ou seja, o Plano de Execução retornou a busca como Clustered Index Seek;

- utilizando o método antigo, de Chave Primária na coluna Código, o método da busca utilizado foi Clustered Index Scan, ou seja, o SQL precisou fazer um Scan em todas as linhas para obter o resultado.

Espero desta forma, ter demonstrado o melhor aproveitamento do índice Clustered de uma tabela, em colunas que realmente serão utilizadas nas buscas.

Concorda, discorda, alguma sugestão ?

Entre em contato!

Abraços e até a próxima.