[SQL 2008] Filtered Index

Una delle molte novità della nuova versione di SQL Server riguarda la possibilità di poter applicare, agli indici non clustered, un predicato direttamente sul filtro.

Pensate ai benefici che si possono così ottenere semplicemente indicizzando solo la parte dei nostri dati che realmente ci interessa!

Proviamo a fare un esempio ed a vedere, in pratica, il guadagno che si può ottenere:

set nocount on
use tempdb
go

create table test
(
idRecord int identity(1,1),
col1 varchar(35),
col2 varchar(35),
col3 varchar(35),
recordDate datetime default GETDATE()
)
go

alter table test add constraint PK_idRecord
primary key clustered (idRecord )
go

declare @i int
set @i = 0

insert test (col1, col2, col3)
values
(replicate('x', 35), replicate('x', 35), replicate('x', 35))

while @i < 5
begin
    insert test (col1, col2, col3)
    select T1.col1, T1.col2, T1.col3 from test T1 cross join test t2
    set @i = @i + 1
end

select COUNT(*) from test

A questo punto abbiamo, sulla nostra tabella, 3.263.442 righe.

Vado ad impostare la colonna recordDate a NULL solo per alcuni valori con istruzione simile a:

update test
set recordDate = null
where idRecord % 3 = 0

A questo punto, ipotizziamo per le nostre necessità applicative, di dover indicizzare proprio questa colonna datetime.

Ipotizziamo anche che non ci interessino, realmente, le righe che hanno questa colonna non valorizzata.

Fino a SQL Server 2005 non potevo fare altro che:

create index idx_dateNotNull on test(recordDate)
go

Oggi posso applicare una where direttamente sull'indice, ovvero:

create index idx_dateNotNullFiltered on test(recordDate)
where recordDate is not null
go

Proviamo, quindi, a vedere lo spazio occupato dalle due strutture appena create per vedere, da subito, i reali benefici:

SELECT
OBJECT_NAME(id) TableName,
name IndexName, reserved, used
FROM sys.sysindexes
where OBJECT_NAME(id) = 'test'

Questo il risultato:

Direi molto interessante, no?