Início / dba / Perguntas / 144
Accepted
bernd_k
Asked: 2011-01-05 02:32:27 +0800 CST

Quando devo usar uma restrição exclusiva em vez de um índice exclusivo?

  • 772

Quando quero que uma coluna tenha valores distintos, posso usar uma restrição

create table t1(
id int primary key,
code varchar(10) unique NULL
);
go

ou posso usar um índice exclusivo

create table t2(
id int primary key,
code varchar(10) NULL
);
go

create unique index I_t2 on t2(code);

Colunas com restrições exclusivas parecem ser boas candidatas a índices exclusivos.

Existem razões conhecidas para usar restrições exclusivas e não usar índices exclusivos?

sql-server index

7 respostas

  1. Best Answer

    Sob o capô, uma restrição exclusiva é implementada da mesma forma que um índice exclusivo - um índice é necessário para cumprir com eficiência o requisito para impor a restrição. Mesmo que o índice seja criado como resultado de uma restrição UNIQUE, o planejador de consulta pode usá-lo como qualquer outro índice se o considerar a melhor maneira de abordar uma determinada consulta.

    Portanto, para um banco de dados que suporta ambos os recursos, a escolha de qual usar geralmente se resume ao estilo e consistência preferidos.

    Se você planeja usar o índice como um índice (ou seja, seu código pode depender da pesquisa/classificação/filtragem nesse campo para ser rápido), eu usaria explicitamente um índice exclusivo (e comentaria a fonte) em vez de uma restrição para fazer isso clear - desta forma, se o requisito de exclusividade for alterado em uma revisão posterior do aplicativo, você (ou algum outro codificador) saberá para garantir que um índice não exclusivo seja colocado no lugar do exclusivo (apenas remover uma restrição exclusiva removeria o índice completamente). Além disso, um índice específico pode ser nomeado em uma dica de índice (ou seja, WITH(INDEX(ix_index_name)), o que não acho que seja o caso do índice criado nos bastidores para gerenciar a exclusividade, pois é improvável que você saiba seu nome.

    Da mesma forma, se você estiver precisando apenas impor a exclusividade como uma regra de negócios, em vez de o campo precisar ser pesquisado ou usado para classificação, eu usaria a restrição, novamente para tornar o uso pretendido mais óbvio quando outra pessoa olhar para sua definição de tabela.

    Observe que, se você usar uma restrição exclusiva e um índice exclusivo no mesmo campo, o banco de dados não será brilhante o suficiente para ver a duplicação, então você acabará com dois índices que consumirão espaço extra e diminuirão as inserções/atualizações de linha.

    • 207

  2. Além dos pontos em outras respostas, aqui estão algumas diferenças importantes entre os dois.

    Observação: as mensagens de erro são do SQL Server 2012.

    Erros

    A violação de uma restrição exclusiva retorna o erro 2627.

    Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'P1U_pk'. Cannot insert duplicate key in object 'dbo.P1U'. The duplicate key value is (1).
The statement has been terminated.

    A violação de um índice exclusivo retorna o erro 2601.

    Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.P1' with unique index 'P1_u'. The duplicate key value is (1).
The statement has been terminated.

    Desativando

    Uma restrição exclusiva não pode ser desabilitada.

    Msg 11415, Level 16, State 1, Line 1
Object 'P1U_pk' cannot be disabled or enabled. This action applies only to foreign key and check constraints.
Msg 4916, Level 16, State 0, Line 1
Could not enable or disable the constraint. See previous errors.

    Mas o índice exclusivo por trás de uma restrição de chave primária ou uma restrição exclusiva pode ser desabilitado, assim como qualquer índice exclusivo. Hat-tip Brain2000.

    ALTER INDEX P1_u ON dbo.P1 DISABLE ;

    Observe o aviso usual de que desabilitar um índice clusterizado torna os dados inacessíveis.

    Opções

    Restrições exclusivas dão suporte a opções de indexação como FILLFACTORe IGNORE_DUP_KEY, embora esse não tenha sido o caso de todas as versões do SQL Server.

    Colunas incluídas

    Índices não clusterizados podem incluir colunas não indexadas (denominado índice de cobertura, este é um grande aprimoramento de desempenho). Os índices por trás das restrições PRIMARY KEY e UNIQUE não podem incluir colunas. Dica de chapéu @ypercube.

    Filtrando

    Uma restrição exclusiva não pode ser filtrada.

    Um índice exclusivo pode ser filtrado.

    CREATE UNIQUE NONCLUSTERED INDEX Students6_DrivesLicence_u 
ON dbo.Students6( DriversLicenceNo ) WHERE DriversLicenceNo is not null ;

    Restrições de chave estrangeira

    Uma restrição de chave estrangeira não pode fazer referência a um índice exclusivo filtrado, embora possa fazer referência a um índice exclusivo não filtrado (acho que isso foi adicionado no SQL Server 2005).

    Nomeação

    Ao criar uma restrição, especificar um nome de restrição é opcional (para todos os cinco tipos de restrições). Se você não especificar um nome, o MSSQL gerará um para você.

    CREATE TABLE dbo.T1 (
    TID int not null PRIMARY KEY
) ;
GO
CREATE TABLE dbo.T2 (
    TID int not null CONSTRAINT T2_pk PRIMARY KEY
) ;

    Ao criar índices, você deve especificar um nome.

    Dica de chapéu @i-one.

    Links

    http://technet.microsoft.com/en-us/library/aa224827(v=SQL.80).aspx

    http://technet.microsoft.com/en-us/library/ms177456.aspx

    • 129

  3. Para citar o MSDN como fonte autorizada:

    Não há diferenças significativas entre criar uma restrição UNIQUE e criar um índice exclusivo independente de uma restrição . A validação de dados ocorre da mesma maneira e o otimizador de consulta não diferencia entre um índice exclusivo criado por uma restrição ou criado manualmente. No entanto, criar uma restrição UNIQUE na coluna torna claro o objetivo do índice... mais informações aqui

    E...

    O Mecanismo de Banco de Dados cria automaticamente um índice UNIQUE para impor o requisito de exclusividade da restrição UNIQUE. Portanto, se for feita uma tentativa de inserir uma linha duplicada, o Mecanismo de Banco de Dados retornará uma mensagem de erro informando que a restrição UNIQUE foi violada e não adiciona a linha à tabela. A menos que um índice clusterizado seja especificado explicitamente, um índice exclusivo e não clusterizado é criado por padrão para impor a restrição UNIQUE... mais informações aqui

    Outros em: https://technet.microsoft.com/en-us/library/aa224827%28v=sql.80%29.aspx

    • 13

  4. Uma das principais diferenças entre uma restrição exclusiva e um índice exclusivo é que uma restrição de chave estrangeira em outra tabela pode fazer referência a colunas que compõem uma restrição exclusiva. Isso não é verdade para índices exclusivos. Além disso, restrições exclusivas são definidas como parte do padrão ANSI, enquanto os índices não. Por fim, considera-se que a restrição exclusiva vive no domínio do design lógico do banco de dados (que pode ser implementado de maneira diferente por diferentes mecanismos de banco de dados), enquanto o índice é um aspecto físico. Portanto, a restrição exclusiva é mais declarativa. Eu preferiria restrição única em quase todos os casos.

    • 7

  5. No Oracle, uma grande diferença é que você pode criar um índice único de função, o que não é possível com restrições exclusivas:

    Por exemplo

    create unique index ux_test on my_table (case when amount != 0 then fk_xyz end);

    Portanto, fk_xyzé exclusivo apenas para registro que possui amount != 0.

    • 0

  6. No Oracle você pode definir uma restrição para DEFERRED, ou seja, você pode definir se a exclusividade deve ser aplicada antes ou depoisCOMMIT

    • 0

  7. A restrição UNIQUE é preferível ao índice UNIQUE. Quando a restrição não é exclusiva, você precisa usar um índice regular ou não exclusivo. A restrição também é outro tipo de índice. O índice é usado para acesso mais rápido.

    Índices exclusivos podem ter cláusulas where. Por exemplo, você pode criar índices para cada ano com base na coluna de data

    WHERE Sale_Date BETWEEN '2012-01-01' AND '2012-12-31'
    • -4

relate perguntas