165
6.1 Principes de l’indexation
[ WHERE ]
[ WITH ( [ ,...n ] ) ]
[ ON { partition_schema ( colonne )
| filegroup
| default
}
]
[ FILESTREAM_ON { filestream_filegroup | partition_schema | "NULL" } ]
::=
{
PAD_INDEX = { ON | OFF }
| FILLFACTOR = fillfactor
| SORT_IN_TEMPDB = { ON | OFF }
| DROP_EXISTING = { ON | OFF }
| ONLINE = { ON | OFF }
| ALLOW_ROW_LOCKS = { ON | OFF }
| ALLOW_PAGE_LOCKS = { ON | OFF }
| MAXDOP = max_degree_of_parallelism
| DATA_COMPRESSION = { NONE | ROW | PAGE}
[ ON PARTITIONS ( { | }
[ , ...n ] ) ]
}
Voyons rapidement quelques-unes de ces options.
UNIQUE permet de créer une contrainte sur la ou les colonnes qui composent
la clé. Au niveau du moteur, elle est strictement équivalente à la contrainte d’unicité, qui crée elle-même un index unique de ce type.
Pour chaque colonne de la clé, vous pouvez indiquer l’ordre de tri : ASC ou
DESC. Si vous triez principalement en ordre descendant (par exemple les lignes les
plus récentes d’abord, à l’aide d’un index sur une date), créez l’index directement en
DESC, cela rendra le scan plus rapide.
Les options FILLFACTOR et PAD_INDEX déterminent l’espace laissé libre
dans les pages d’index. Lorsque vous créez un index sur une table très souvent modifiée, chaque modification entraîne une opération de maintenance de l’index. Nous
nous souvenons que l’index maintient chacun de ses niveaux dans l’ordre de la clé, à
l’intérieur des pages d’index comme de page en page, à l’aide d’une liste doublement
liée. Cette maintenance entraîne des réorganisations dans la page, et entre les pages,
lorsque des valeurs de clé sont insérées. Imaginons une armoire de boîtes à fiches,
contenant par exemple des dossiers individuels. Lorsque de nouveaux dossiers sont
ajoutés, ils peuvent remplir complètement une boîte à fiche. Il faut alors prendre une
boîte vide, l’insérer au bon endroit dans l’armoire, et y placer les nouveaux dossiers
(ce qu’on appelle un split de page). Lorsque des dossiers sont retirés pour suppression,
ils libèrent de la place dans une boîte. Ceci rend l’ensemble moins compact : un certain nombre de boîtes ne sont qu’en partie pleines, voire presque vides et le tout
prend plus de place dans l’armoire. Chercher un dossier parmi l’ensemble demande
plus de travail, car il faudrait ouvrir plus de boîtes. C’est le problème que rencontre
SQL Server lors d’un scan. On appelle ce phénomène la fragmentation interne