Singularidade e Desempenho

Argumentos PARA declarar unicidade

Normalmente, é melhor fornecer mais informações ao otimizador do que menos. Se você sabe que um índice é único, é melhor declará-lo dessa forma. Isso pode ajudar o otimizador, bem como fornecer clareza sobre seus dados para pessoas reais que estão olhando para as definições de esquema.

Isso pode ter um impacto direto no desempenho. Veja o artigo de Paul White Enforcing Uniqueness for Performance para um exemplo.

Argumentos CONTRA a declaração de unicidade

Quanto às especificidades desta questão, tentei adaptar seu cenário de índice não clusterizado à demonstração no artigo de Paul, e parece que buscas singleton são usadas, independentemente de o índice não clusterizado ser declarado exclusivo ou não. Incluí o código de demonstração modificado no final desta resposta.

Com base nisso, parece que o otimizador é capaz de inferir exclusividade pela inclusão da chave UNIQUE do índice clusterizado. Esse conceito é mais completamente confirmado e desenvolvido neste Q&A: Devo marcar um índice composto como exclusivo se ele contiver a chave primária?

Há também certos cenários em que um índice UNIQUE pode causar sobrecarga de desempenho, então não há uma resposta mágica aqui, infelizmente. Veja estes artigos para leitura relacionada:

• Cuidado com leituras furtivas com índices exclusivos
• SQL Server, buscas e pesquisa binária

Ordem das Chaves de Índice

Em relação à ordenação das colunas, isso realmente depende dos tipos de consultas que serão executadas contra essa tabela. E essa é mais ou menos a orientação "geral" sobre índices que entra em jogo aqui. Você geralmente quer a coluna "mais seletiva" primeiro.

Se as consultas normalmente incluem um valor para "Primário" como um filtro (ou condição de junção), então esse é um candidato ideal - você pode buscar diretamente na linha certa. Se as consultas normalmente fornecem apenas um valor para "Secundário", então Primário não é uma boa chave de índice líder - você teria que escanear cada linha na tabela. Nesse caso, Secundário é uma escolha melhor para liderar o índice.

Código de demonstração

Veja abaixo o código que usei. Novamente, ele foi adaptado (possivelmente incorretamente, já que sou apenas um humilde desenvolvedor web) do artigo de Paul:

SET STATISTICS TIME, IO ON;

CREATE TABLE [dbo].[OnlyTwoGoodColumnsButManyBad]
(
    [Primary] INT PRIMARY KEY CLUSTERED,
    [Secondary] NVARCHAR(500) NOT NULL,
    [Evil1] NVARCHAR(MAX),
    [Evil2] NVARCHAR(MAX),
    [Evil3] NVARCHAR(MAX),
    [Evil4] NVARCHAR(MAX),
    [Evil5] NVARCHAR(MAX)
);

INSERT dbo.OnlyTwoGoodColumnsButManyBad
    WITH (TABLOCKX)
    ([Primary], Secondary)
SELECT TOP (5000000)
    [Primary] = 
        ROW_NUMBER() OVER (
            ORDER BY @@SPID),
  Secondary = 'Garbage'
FROM 
    master.sys.columns AS C
    CROSS JOIN master.sys.columns AS c2
    CROSS JOIN master.sys.columns AS c3
ORDER BY
    [Primary];

CREATE NONCLUSTERED INDEX [IX_Primary_Secondary]
ON [dbo].[OnlyTwoGoodColumnsButManyBad]
(
    [Primary],
    [Secondary]
);

CREATE UNIQUE NONCLUSTERED INDEX [UIX_Primary_Secondary]
ON [dbo].[OnlyTwoGoodColumnsButManyBad]
(
    [Primary],
    [Secondary]
);

SELECT 
    COUNT_BIG(*)
FROM dbo.[OnlyTwoGoodColumnsButManyBad] AS ST 
    WITH (TABLOCK, INDEX ([IX_Primary_Secondary]))
JOIN dbo.[OnlyTwoGoodColumnsButManyBad] AS ST2
    WITH (TABLOCK, INDEX ([IX_Primary_Secondary]))
    ON ST2.[Primary] = ST.[Primary] AND ST2.[Secondary] = ST.[Secondary]
OPTION (MAXDOP 1, LOOP JOIN, FORCE ORDER);

/*
Table 'OnlyTwoGoodColumnsButManyBad'. 
Scan count 1, logical reads 15329888
CPU time = 5000 ms,  elapsed time = 5312 ms.
*/

SELECT 
    COUNT_BIG(*)
FROM dbo.[OnlyTwoGoodColumnsButManyBad] AS ST 
    WITH (TABLOCK, INDEX ([UIX_Primary_Secondary]))
JOIN dbo.[OnlyTwoGoodColumnsButManyBad] AS ST2
    WITH (TABLOCK, INDEX ([UIX_Primary_Secondary]))
    ON ST2.[Primary] = ST.[Primary] AND ST2.[Secondary] = ST.[Secondary]
OPTION (MAXDOP 1, LOOP JOIN, FORCE ORDER);
/*
Table 'OnlyTwoGoodColumnsButManyBad'. 
Scan count 1, logical reads 15329888
CPU time = 4984 ms,  elapsed time = 5283 ms.
*/

Há outro benefício em declarar o índice como único.

Cada um non unique indexé sempre maior que o similar unique index. Isso ocorre porque no caso de índice não clusterizado não exclusivo, o rowID(RID ou chave de índice clusterizado) também é armazenado em níveis de índice não folha .

O índice não clusterizado não exclusivo (seu caso) sempre tem o clustered index keypresente em todos os níveis de índice, enquanto um índice exclusivo similar não precisa dele em seus níveis não-folha, então quanto maior for sua chave de índice clusterizado, maior será o tamanho do seu non uniqueíndice não clusterizado.

Não consegui encontrar a documentação oficial, mas aqui está uma explicação sobre isso: Por que os níveis raiz e intermediário de índices NÃO exclusivos e NÃO agrupados armazenam adicionalmente o ID da linha?

Ok, aqui está minha reprodução:

select n, replicate('0', 200) + cast(n as varchar(10)) as n1
into dbo.nums
from fleetxs.dbo.nums

alter table nums add constraint PK_nums primary key clustered(n)
create index ix_n1 on dbo.nums(n1) 

DBCC IND (test, nums, 2)

--187341
--187342

DBCC TRACEON(3604)
DBCC PAGE(test, 1, 187341, 3) 

Acabei de criar uma tabela a partir da minha tabela de trabalho dbo.Nums que contém números inteiros de 1 a 1000000, coloquei a coluna única n como ela está na coluna n da minha nova tabela e criei outra coluna replicate('0', 200) + cast(n as varchar(10)) as n1, aqui a replicação é usada apenas para o propósito de uma coluna grande o suficiente para obter não apenas raiz + folha, mas também 1 nível intermediário.

O índice nesta coluna não é único, mas o conteúdo é único por definição.

Então obtenho minhas páginas de índice com DBCC IND:

Pego 2 páginas que representam o nível intermediário e raiz e as examino:

1. nível intermediário

2. nível raiz

Ambos têm a coluna de índice clusterizado na como parte da chave. Os valores na coluna n1 são únicos, no entanto.