Sql Server 2008 : Filter Index
Aujourd'hui, une nouveauté de la dernière CTP (6) de Sql Server 2008:
La possibilité de créer des index filtrés.
Alors "késako" des index filtrés ?? Eh bien c'est tout simplement, la possibilité de dire à un index de n'indexer qu'une partie de ses données !
Un exemple (enfin deux) valant souvent plus qu'une longue explication :
Première Exemple :
Supposons qu'une table de Client comporte un champ fixe de 5 charactères (Char(5)) contenant le code postal de la ville où il réside.
Supposons encore que l'entreprise qui utilise votre logiciel ne désire travailler que sur les clients dans le code postal est compris entre 30000 et 33000 (pourquoi pas !), bien que la base de données contiennent un ensemble de clients de la france entière :
Eh bien vous pouvez tout simplement créer un index filtré sur la colonne CodePostal, en indiquant que seul les code postaux entre les bornes 30000 et 33000 seront indexés !
La preuve en exemple et en image. Remarquez au passage le lifting de l'interface de création d'un index :
Etape 1 : Je crée mon Index :
Etape 2 : Je donne un critère de filtre à mon Index (entre 30000 et 33000)
Et voilà c'est fini :)
Comment vérifier que mon index fonctionne ? En essayant d'interroger mon index via une requète qui le fait intervenir.
Un truc du genre (simplissime)
Select * from Client Where CodePostal = '31100'
Voilà ce que donne le plan d'exécution :
On voit bien que notre nouvel index est directement attaqué (Seek)
Il est effectué un Key Lookup sur notre index pour récupérer l'ensemble des colonnes.
(Tiens, au passage : Question subsidiaire : Comment éviter le Key Lookup en minimisant l'impact sur votre base de données ? Réponse dans 1 ou 2 jours si j'ai des tentatives de réponses :))
On continue notre test en modifiant le critère du codepostal en le passant "hors index filtré" :
Un truc du genre (toujours aussi simplissime)
Select * from Client Where CodePostal = '21100'
Le résultat en image :
On voit bien que notre index n'est pas utilisé et que le moteur SQL a scaner l'index cluster (oh mon dieu .. :))
Deuxième exemple :
Un intéret majeur découle de l'utilisation de ce genre d'index filtré : La possibilité de définir l'unicité d'une colonne MAIS en autorisant AUSSI la colonne a être nulle :)
Supposons ma table de client comporte un numéro de sécurité sociale. Il est impératif que ce numéro de sécurité sociale soit unique, si et seulement si il est connu de l'entreprise.
Avant, les solutions existaient mais pouvaient paraître compliquées (Merci Jean Pierre ;))
Aujourd'hui, facile !
Créeons un index filtré. pour la forme, cette fois ci, je passe par du code T-SQL pour créer mon index filtré :
--------------------------------------------------------------------------------------
USE [Demo]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_NumSecuSociale] ON [dbo].[Client]
(
[NumSecuriteSociale] ASC
)
WHERE NumSecuriteSociale is not null
GO
--------------------------------------------------------------------------------------
Et c'est fini (oui je sais .. je sais ...)
Il ne reste plus qu'à tester tout ça :
--------------------------------------------------------------------------------------
INSERT INTO [Demo].[dbo].[Client]
([ClientId] ,[EmployeId] ,[ClientTypeId] ,[Prenom] ,[Nom]
,[Adresse] ,[Ville] ,[CodePostal] ,[NumSecuriteSociale])
VALUES
(NewId() ,Null ,1 ,'Sébastien' ,'Pertus' ,'4 av M' ,'TOULOUSE' ,'31100'
,'1234567890')
--------------------------------------------------------------------------------------
Et une deuxième fois avec un numéro de sécurité sociale identique
--------------------------------------------------------------------------------------
INSERT INTO [Demo].[dbo].[Client]
([ClientId] ,[EmployeId] ,[ClientTypeId] ,[Prenom] ,[Nom]
,[Adresse] ,[Ville] ,[CodePostal] ,[NumSecuriteSociale])
VALUES
(NewId() ,Null ,1 ,'Jean' ,'Durand' ,'Bvd Carnot' ,'TOULOUSE' ,'31100'
,
'1234567890')
-------------------------------------------------------------------------------------- Le résultat en image :
Insertion de la première ligne et refus d'insertion de la deuxième.
Par contre, je peux tjs insérer des clients sans numéros de sécurité sociale, et autant que j'en veux :)
--------------------------------------------------------------------------------------
INSERT INTO [Demo].[dbo].[Client]
([ClientId] ,[EmployeId] ,[ClientTypeId] ,[Prenom] ,[Nom]
,[Adresse] ,[Ville] ,[CodePostal] ,[NumSecuriteSociale])
VALUES
(NewId() ,Null ,1 ,'Inconnu' ,'du Bataillon' ,'P. Jeanne d'arc' ,'TOULOUSE' ,'31100'
,Null)
--------------------------------------------------------------------------------------
Une bien belle fonctionnalité que ces index filtrés !
Happy Indexing !