Index non ordonnés en clusters

la première colonne (connue sous le nom d’élément de niveau supérieur) de l’index, mais il n’est pas réellement important que la première colonne soit ou non celle utilisée le plus fréquemment et cet aspect n’est pas le plus essentiel pour la sélection des index non ordonnés en clusters. Lors du choix des index non ordonnés en clusters appropriés, il est plus important de « connaître le système » et de savoir comment SQL Server utilise les index plutôt que de partir de critères généraux concernant la première colonne.

L’élément de niveau supérieur des index est important ; néanmoins, il ne doit pas forcément être hautement sélectif pour que l’index soit utile. (Notez que la sélectivité d’un index dépend du pourcentage des lignes d’une table qui ont la même valeur pour la clé indexée. La sélectivité d’un index est optimale si quelques lignes seulement ont la même valeur.) Considérons une table hypothétique qui est extrêmement large et couvre des données personnelles similaires à celles du recensement pour les Etats-Unis. Cette table assure le suivi d’un grand nombre d’éléments et sa largeur est en moyenne de 1200 octets par ligne. En faisant appel à notre connaissance des densités de pages et du format des enregistrements, nous pouvons constater qu’il est possible d’insérer en moyenne seulement six lignes par page. Si la table stocke 300 millions de lignes de la sorte, sa taille sera de 381 Go. En général, les analyses effectuées sur une telle table seront difficiles (et potentiellement lentes). Toutefois, si une requête d’agrégation courante pour la table consiste à retourner le nombre de ménages sur le critère « HeadofHouseholdGender » (sexe du chef de famille), puis « Age » et ensuite « Dependents » (personnes à charge), la requête fera appel à un petit sous-ensemble seulement des colonnes de la table.

Par ailleurs, en fonction de l’ordre des colonnes, cet index est également utile pour compter les lignes par HeadofHouseholdGender seulement ou par HeadofHouseholdGender et Age ensemble. Vous pouvez aussi employer cet index pour tout sous-ensemble restant de colonnes, autrement dit les colonnes comprenant la portion restante de l’index. L’intérêt est que l’élément de niveau supérieur (HeadofHouseholdGender) n’est pas très sélectif et, pourtant, cet index est utile pour une multitude d’agrégations. Un autre aspect qui rend cet index tellement efficace est sa taille. Avec trois colonnes susceptibles d’être stockées comme bit (Gender [sexe]), tinyint (Dependents [personnes à charge]) et encore une fois tinyint (Age), le total pour cette clé d’index, y compris un en-tête de clé d’index (4 octets) et la clé d’index ordonné en clusters (environ 8 octets), est de 15 octets par ligne. Comme cette ligne est très étroite, SQL Server peut insérer 539 lignes par page. La table des données personnelles stocke 300 millions d’enregistrements, mais la taille de l’index est de seulement 4 Go.

Une structure de cette taille est nettement plus optimale à lire que s’il fallait lire toute la table. De même, si les données à récupérer sont ordonnées de manière appropriée pour la commande Group By, SQL Server peut réaliser une agrégation de flux au lieu d’une agrégation de type hachage, d’où de meilleures performances pour la requête. Par ailleurs, si ces données sont en lecture seule, comme les données de recensement devraient l’être, vous pouvez aussi envisager d’employer des formes d’index plus intéressantes que celles créées par les vues indexées. Cela ne veut pas dire qu’il est impossible de créer une vue indexée sur des systèmes transactionnels, mais les possibilités de blocage sont plus élevées pour ce type de système.