Histoire d’index : Stockage interne des index filtrés avec SQL Server

Les index filtrés ont été introduit à partir de la version SQL Server 2008. Il existe déjà un certain nombre d’exemples qui illustrent leur intérêt et je vous invite à les regarder si cela n’est déjà fait (voir le billet d’Elsuket). Mais qu’en est il du stockage ? En effet la question peut sembler légitime car les index filtrés ne comportent que les lignes de données qui correspondant à sa définition. Nous verrons comment SQL Server gère ces index en interne.

Le script suivant crée une table nommée test_filtered_index qui comporte un index filtré nommé idx_col3_filtered. Cet index permet de gérer l’unicité des valeurs non nuls de cette colonne. Au préalable une base nommée Internals est créée.

CREATE DATABASE Internals;
GO

USE Internals;
GO

CREATE TABLE test_filtered_index
(
col1 INT IDENTITY(1,1) PRIMARY KEY,
col2 INT NOT NULL,
col3 INT NULL
);

– Création d’un index filtré sur col3
– Cet index permet de gérer l’unicité des données
– de cette colonne pour les valeurs non nuls.
CREATE UNIQUE NONCLUSTERED INDEX idx_col3_filtered
ON test_filtered_index
(
col3
)
WHERE col3 IS NOT NULL;

Remplissons ensuite la table test_filtered_index avec un jeu de données. La table comportera 10000 lignes de données. 10% de ces lignes concerneront l’index filtré (soit 1001 lignes).

DECLARE @i INT;
SET @i = 0;

WHILE @i < 10000
BEGIN
    IF @i <= 1000
    BEGIN
         INSERT INTO test_filtered_index (col2, col3) VALUES (@i,@i);
         SET @i +=1;
    END
    ELSE
    BEGIN
         INSERT INTO test_filtered_index (col2) VALUES (@i);
         SET @i +=1;
    END
END;

Nous pouvons dès à présent visualiser les métadonnées pour les index de la table. Nous avons 2 index (un index cluster et un index unique non cluster filtré).

SELECT
    o.name AS [object_name],
    i.index_id,
    i.type_desc,
    i.has_filter,
    i.filter_definition
FROM sys.indexes AS i
INNER JOIN sys.objects AS o
ON i.object_id = o.object_id
WHERE o.name = ‘test_filtered_index';