O índice nonclustered tem sempre o índice clustered na sua definição.

Afinal o que compõe um índice clustered e nonclustered?

Quantos de nós já viram uma tabela com um índice clustered na ColunaX e depois um índice nonclustered na ColunaY e na ColunaX?

Quando eu vejo uma coisa destas costumo “disparar”:
Eu: “Temos uma coluna duplicada neste nonclustered”
Alguém : “Hã?”
Eu: “Sabes que um nonclustered numa tabela tem sempre o clustered lá, não sabes?”
Alguém : “Hã?”

Tudo bem, vamos a isso.

Criar uma tabela de teste

CREATE TABLE [dbo].[IndexTest](
	[col1] [bigint] IDENTITY(1,1) NOT NULL,
	[col2] [smallint] NOT NULL,
	[col3] [varchar](30) NOT NULL,
	[col4] [varchar](40) NOT NULL,
	[col5] [varchar](50) NOT NULL
) ON [PRIMARY]
GO

Vamos ver o que por lá anda.

select p.index_id, p.partition_number, 
	pc.leaf_null_bit,
	coalesce(cx.name, c.name) as column_name,
	pc.partition_column_id,
	pc.max_inrow_length,
	pc.max_length,
	pc.key_ordinal,
	pc.leaf_offset,
	pc.is_uniqueifier
from sys.system_internals_partitions p
join sys.system_internals_partition_columns pc
	on p.partition_id = pc.partition_id
left join sys.index_columns ic
	on p.object_id = ic.object_id
	and ic.index_id = p.index_id
	and ic.index_column_id = pc.partition_column_id
left join sys.columns c
	on p.object_id = c.object_id
	and ic.column_id = c.column_id	
left join sys.columns cx
	on p.object_id = cx.object_id	
	and p.index_id in (0,1)
	and pc.partition_column_id = cx.column_id
where p.object_id = object_id('IndexTest')
order by index_id, partition_number

Vamos adicionar um índice clustered?

create clustered index cidx on IndexTest (col1)

Repetindo o código para vermos o que está agora na tabela…

Algumas mudanças.

Aparece uma coluna sem nome com 4 bytes de tamanho.
A coluna is_uniqueifier responde a qualquer pergunta que entretanto possa surgir: o nosso índice não é UNIQUE, logo levamos com um uniqueifier para tornarmos o nosso índice único.

Sim, todos os índices são UNIQUE, quer queiramos quer não. Se não quisermos, o SQL Server quer, por isso… 🙂

Agora criamos um nonclustered, só porque sim.

create nonclustered index ncidx on IndexTest (col2) include (col3)

O que temos agora?

3 colunas sem nome. Uma delas sabemos qual é, porque é a que tem o is_uniqueifier a 1.

Vamos por partes.

Temos dois index_id, no 1 temos o nosso índice clustered, no 2 o nosso nonclustered.

Nesse nonclustered temos a col2 indexada, a col3 incluída e os outros dois são o nosso índice clustered.
Se virem com atenção os tamanhos são correspondentes à col1 e ao uniqueifier.

Então o nosso índice nonclustered é composto pela col2 (indexada), col1 (aparece null, também indexada), uniqueifier (aparece null, indexado) e col3 (que não faz parte da chave, o key_ordinal é 0).

Alterando o nosso clustered para unique o que acontecerá?

drop index cidx on IndexTest
create unique clustered index cidx on IndexTest (col1)
alter table [dbo].[IndexTest] rebuild

Agora o nosso índice clustered é UNIQUE, logo o SQL Server deixa de necessitar da “muleta” uniqueifier. 4 bytes poupados.

O nosso nonclustered agora apenas tem 3 colunas, a col2 (indexada), a col1 (aparece null, indexada) e a col3 (não indexada).

De uma assentada só poupamos 8 bytes por linha.

Mas a questão inicial não era esta, se bem se lembram.

O que gostava que se retivesse desta entrada é que:

“O índice nonclustered tem sempre o índice clustered na sua definição.”