Início / dba / Perguntas / 266640
Accepted
Raidenlee
Asked: 2020-05-07 17:16:20 +0800 CST

A criação de um índice poderia quebrar as dependências da tabela?

  • 772

Executei uma consulta usando o sys.dm_db_missing_index_details, sys.dm_db_missing_index_groupse sys.dm_db_missing_index_group_statspara determinar quais são minhas opções para índices ausentes. Depois de analisar os dados, determinei que precisava criar um índice não clusterizado para a Tabela A.

Observando a Tabela A, observei que existem 55 dependências (Tabelas, SPs, Views) e 23 objetos que são dependentes dessa tabela. Minha pergunta é, se eu criar esse índice não clusterizado nesta tabela, isso afetará as dependências e dependentes dessa tabela?

Além disso, se sim, isso afeta as dependências, como faço para tentar corrigir as dependências?

sql-server index

3 respostas

  1. Ao implementar índices filtrados, certas configurações de conexão devem estar corretas :

    insira a descrição da imagem aqui

    Por outro lado

    O Mecanismo de Banco de Dados gera um erro e reverte as instruções INSERT, UPDATE, DELETE ou MERGE que alteram os dados no índice.

    Portanto, se algum dos códigos dependentes não tiver as SETopções acima configuradas corretamente, adicionar um índice filtrado as quebrará. Infelizmente, tive que aprender da maneira mais difícil a verificar isso ao trabalhar em sistemas antigos com código de procedimento armazenado questionável :)

    • 4
  2. Best Answer

    Entre os tipos de índice Disponíveis eu pude ver apenas um tipo de índice que seria capaz de causar um erro em um objeto que depende da tabela e é o índice Unique .

    De acordo com as Diretrizes de Design de Índice Único :

    Um índice exclusivo garante que a chave de índice não contenha valores duplicados e, portanto, cada linha da tabela é de alguma forma exclusiva.

    É um índice que vem com uma restrição que garante que os valores sejam exclusivos. Além dessa situação muito específica, você não precisa se preocupar em quebrar uma dependência, como diz o documento ,

    Um índice não clusterizado é uma estrutura de índice separada dos dados armazenados em uma tabela que reordena uma ou mais colunas selecionadas. (ênfase minha)

    Como você pode ver, o índice não clusterizado é separado da tabela e não altera a estrutura da tabela em si, portanto, qualquer coisa que use essa tabela não será afetada negativamente da maneira que você teme.

    Aqui está um bom ponto de partida: SQL Server Index Architecture and Design Guide

    Laboratório: causando erro na dependência da tabela criando um índice

    Primeiro, crie uma tabela que terá um objeto que depende dela:

    CREATE TABLE MyTable(
    UserID INT NOT NULL IDENTITY PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Surname VARCHAR(50) NOT NULL
)

    Agora, crie um SP que seja dependente dessa tabela:

    CREATE PROCEDURE InsertUser
@Name VARCHAR(50),
@Surname VARCHAR(50)
AS
BEGIN 
    INSERT INTO MyTable(Name, Surname)
    VALUES(@Name, @Surname);
END

    Vamos inserir o usuário Paul Smith :

    EXEC InsertUser 'Paul', 'Smith';

    O procedimento executa bem. Paul é um nome muito comum e agora você pode inserir quantos usuários chamados Paul quiser. Mas você decidiu criar um Índice Único na coluna Nome:

    CREATE UNIQUE INDEX AK_MyTable_Name ON MyTable (Name);

    Depois disso, você tenta inserir outro usuário chamado Paul Jones :

    EXEC InsertUser 'Paul', 'Jones';

    Mas agora você recebe um erro:

    Msg 2601, Level 14, State 1, Procedure InsertUser, Line 6 [Batch Start Line 17] Não é possível inserir linha de chave duplicada no objeto 'dbo.MyTable' com índice exclusivo 'AK_MyTable_Name'. O valor da chave duplicada é (Paul). A instrução foi encerrada.

    O SP que dependia da tabela agora está quebrado. Essa é a única situação que posso imaginar que você poderia quebrar um objeto que depende de uma tabela pela criação de um índice. Se você não estiver criando um índice exclusivo agora, pode relaxar e observar o desempenho de seus novos índices.

    • 2

  3. A resposta muito simples é não. No entanto, antes de criar um índice, você deve entender a carga de trabalho, quantos índices estão atualmente na tabela, se você pode preencher o novo índice, modificando um índice existente etc.

    Nunca crie um índice baseado em recomendações de índice ausentes do SQL Server https://www.brentozar.com/archive/2017/08/missing-index-recommendations-arent-perfect/

    Como mencionei no parágrafo 1, você precisa entender sua carga de trabalho e o benefício do novo índice analisando as estatísticas de uso.

    • 1

relate perguntas