Come e perché creare un filtered index su SQL Server

/ Performance dei database e temi più avanzati

Con filtered index intendiamo su SQL Server gli indici che contengono una condizione Where nella loro definizione. In questo articolo vedremo alcuni casi d’uso legati alla distribuzione dei dati nelle tabelle e il relativo impatto sulle performance.

 

Casi d’uso e sintassi dei filtered index

La creazione di indici filtrati può rivelarsi molto efficace in presenza di  colonna sparse caratterizzate dalla presenza di null nella maggior parte delle righe. Prendiamo ad esempio la colonna DataChiusura  di un’ipotetica tabella dei Conti Corrente dove nel 95% dei casi è presente un null per indicare che i conti non sono stati chiusi.

Ma la presenza del null non è obbligatoria. In generale può essere utile creare filtered index su colonne che contengono quasi sempre lo stesso valore, ma per le quali siamo interessati a effettuare calcoli e statistiche sulle poche righe restanti. Supponiamo ad esempio di avere nella tabella Prodotti il 99% di righe con una determinata categoria, ma vogliamo tenere sempre sotto controllo i record associati ai valori restanti.

Vediamo come al solito il codice d’esempio sul nostro database di test. Creiamo la tabella dei Prodotti con un progressivo come chiave primaria e la colonna Categoria

CREATE TABLE Prodotti (
 IdProdotto INT IDENTITY(1,1) PRIMARY KEY,
 Categoria VARCHAR(50))

Aggiungiamo circa diecimila righe con il valore ‘A’ in categoria e quattro righe con i valori ‘B’ e ‘C’:

INSERT INTO Prodotti(Categoria)
SELECT 'a'
FROM sys.objects AS a 
CROSS JOIN sys.objects AS b;
INSERT INTO Prodotti(Categoria)
VALUES ('b'), ('c'), ('b');

Supponiamo di voler contare il numero di Prodotti associati alle categorie anomale. Con questa distribuzione di dati e un’analisi del genere da effettuare, può essere utile creare un indice filtrato:

CREATE NONCLUSTERED INDEX IX_categorie_rare 
ON Prodotti(Categoria) 
WHERE Categoria <> 'A';

Attenzione: non è obbligatorio creare l’indice sulla stessa colonna utilizzata nella Where.

T-SQL avanzato e tuning delle query

 

Analisi delle performance con Filtered Index

Eseguiamo la query

SELECT Categoria, 
  COUNT(*)
FROM Prodotti
WHERE Categoria <> 'A'
GROUP BY Categoria;

e analizziamone il piano d’esecuzione:

SQL Server ha utilizzato l’indice filtered evitando qualsiasi tipo di ricerca. Inoltre la query è resa particolarmente performante dalle dimensioni estremamente ridotte dell’indice.

Un altro caso d’uso degli indici filtrati è imporre una condizione di univocità sui dati in una colonna sparsi che non sono null: ci basterà aggiungere UNIQUE alla sintassi di creazione dell’indice.

 

Continua a imparare

Scroll to Top
Torna su