SQL Server et les Identifiants : La Guerre des Clés - INT vs GUID

Introduction : Quand le choix de l'identifiant devient une décision stratégique

Vous pensiez que choisir entre INT et GUID était un choix anodin ? Détrompez-vous !
Nous allons décortiquer ce duel infernal avec des tests concrets et une conclusion tranchée (sans page split, promis).

Pré-requis de Lecture

Avant de plonger dans cet article, assurez-vous d'avoir lu :

• SQL Server et le Page Split : Quand la base de données se met à danser
• SQL Server joue à Tetris avec vos données

Ces articles vous fourniront un aperçu des problématiques de fragmentation et de gestion des pages, qui seront au cœur de notre expérimentation.

Remarque : Par simplification de lecture, le terme UNIQUEIDENTIFIER sera évoqué sous l'appellation GUID, son équivalent en SQL Server, pour des raisons de clarté.

Contexte

Le choix du type de clé primaire en SQL Server influence bien plus que la simple unicité des enregistrements. INT est compact, rapide et séquentiel, tandis que GUID garantit une unicité globale mais à un prix plus élevé en termes de stockage et de performance des jointures.

Là où le bât blesse, c’est que la structure des index cluster peut provoquer une fragmentation et ralentir les performances. Nous allons explorer les erreurs courantes, puis proposer une stratégie optimisée pour concilier sécurité, intégrité et performance.

Cet article répond également à un sujet traité en C# par Christophe Mommer sur le GUID(Ulid) triable, présenté comme:

• le type d'identifiant ULTIME pour vos objets (ce n'est pas le GUID) sur YouTube
• Son article de blog trouvable ici.

Protocole de Test (Benchmark)

Pour analyser les performances de manière objective, nous avons mis en place un benchmark en trois étapes :

1. Création de Tables de Test

CREATE TABLE dbo.bench_int (
    id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
)

CREATE TABLE dbo.bench_newid (
    id UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY CLUSTERED
)

CREATE TABLE dbo.bench_newseqid (
    id UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID() PRIMARY KEY CLUSTERED
)

Note : NEWSEQUENTIALID() génère des valeurs de type GUID séquentielles (mais pas consécutives) et ne fonctionne qu'avec une valeur de colonne par défaut.

2. Scénarios de Benchmark

• Insertion de données : 100 milliers de lignes avec des valeurs générées automatiquement.

DECLARE @i INT = 0

WHILE @i < 100000
BEGIN
    INSERT INTO dbo.bench_int DEFAULT VALUES
    INSERT INTO dbo.bench_newid DEFAULT VALUES
    INSERT INTO dbo.bench_newseqid DEFAULT VALUES
    SET @i = @i + 1
END

3. Mesures Collectées

Pour collecter les statistiques, nous utilisons la vue système sys.dm_db_index_physical_stats avec la requête suivante:

SELECT
	OBJECT_NAME(ips.object_id) AS 'Nom de l''objet',
	si.name AS 'Nom de l'' index',
    ips.page_count AS 'Pages',
    ROUND(ips.avg_page_space_used_in_percent, 2) AS 'Densité',
    ROUND(ips.avg_fragmentation_in_percent, 2) AS 'Fragmentation'
FROM
    sys.dm_db_index_physical_stats(DB_ID('NomDeVotreDb'), NULL, NULL, NULL, 'DETAILED') AS ips
CROSS APPLY
    sys.indexes AS si
WHERE
    si.object_id = ips.object_id
    AND si.index_id = ips.index_id
    AND ips.index_level = 0
    AND si.name IS NOT NULL
    AND OBJECT_NAME(ips.object_id) IN ('bench_int','bench_newid','bench_newseqid')
ORDER
    BY ips.page_count DESC

Astuce : Remplacez NomDeVotreDb par le nom de votre base de données.

Typologie des Identifiants en SQL Server

Bien que cet article se concentre sur INT et GUID, voici un tableau récapitulatif des types d'entiers disponibles :

Pour plus de détails, consultez :

• Types de données entiers (Transact-SQL)
• Types de données uniqueidentifier (Transact-SQL)

Résultats du Benchmark et Analyse

1. Déchiffrage des Mesures

• Pages - Nombre de pages allouées pour stocker les données. Intimement liée à la taille de la table, de façon physique (sur le disque).
  • Rappel : Une page pèse 8 Ko. Pour calculer la taille de stockage physique, multipliez le nombre de pages par 8 et divisez par 1024 pour obtenir la taille en Mo.
  • Note : Une valeur de nombre de pages basse est meilleure.
• Densité - Pourcentage de remplissage des pages.
  • Rappel : Lié à la notion du Fill Factor. Autrement dit, 100% équivaut à une page pleine de 8 Ko ce qui est optimal.
  • Note : Une valeur proche de 100% est meilleure.
• Fragmentation des index. - Pourcentage de fragmentation de l'index.
  • Rappel : La fragmentation peut ralentir les performances des requêtes. Augmenter le temps du processus de lecture/écriture et donc augmenter le temps de réponse.
  • Note : Une valeur proche de 0% est meilleure.

2. Insertion de Données

• INT avec IDENTITY(1,1) : Insertion rapide, faible fragmentation, allocation séquentielle des pages.
  

• GUID avec NEWID() : Insertion lente, forte fragmentation due à la nature aléatoire des valeurs, surcoût en écriture de pages.
  

• GUID avec NEWSEQUENTIALID() : Performances proches de INT, faible fragmentation, allocation séquentielle des pages.
  

Vulgarisation du NEWSEQUENTIALID() :
Dans la visualisation des données du NEWSEQUENTIALID, vous pouvez observer d'une légère séquence dans les valeurs générées.
Ceci est dû à la nature séquentielle de la fonction NEWSEQUENTIALID() qui génère des valeurs GUID par la notion du timestamp et de l'usage d'une seed.
Chaque usage de la fonction NEWSEQUENTIALID() en tant que valeur par défaut pour une colonne d'une table différente, y est associé une nouvelle seed.
Si vous doutez encore de l'efficacité de cette fonction, je vous invite à insérer de nouvelles données par itération de 10 à 30 minutes, voire une heure pour observer le changement des séquences.
Vous conviendrez que ça n'en reste pas moins difficile à prédire.

3. Jointures Complexes

• Démonstration pratique :
  Nous simulerons une jointure entre deux tables avec une clé primaire NEWID().

CREATE TABLE dbo.table_a (
    id UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY
)

CREATE TABLE dbo.table_b (
    id_a UNIQUEIDENTIFIER PRIMARY KEY FOREIGN KEY REFERENCES table_a(id)
)

Pourquoi est-ce une extrême mauvaise idée ?

Constatation après nos découvertes

Comme nous l'avons vu, l'utilisation de NEWID() entraîne une fragmentation élevée et un "coût" en termes de stockage.

En comprendre le sens

Lorsque vous effectuez une jointure entre ces tables :

1. Vous traitez un coût lié au type, ici exprimé par un GUID, qui est de 16 octets par clé, soit 2 * 16 = 32 octets, pour une relation.
2. Vous faites suivre le problème de fragmentation de la table table_a à la table table_b, doublant ainsi aisément la volumétrie de stockage.
3. Vous augmentez le temps de réponse des requêtes, car le moteur de base de données doit traiter des données plus volumineuses et plus fragmentées, ce qui impacte la profondeur de l'arbre B-Tree, et donc dégrade les performances en recherche.

L'Approche Idéale : Le Meilleur des Deux Mondes

Comment concilier performances et unicité globale ?

Peut-être qu'en parcourant l'article, vous avez déjà une idée de la solution à envisager. Et pour ceux qui ne l'ont pas encore, pas de panique, la voici !

La Stratégie Optimale

1. Utiliser une colonne de type INT (IDENTITY(1,1)) comme Primary Key et Foreign Key.
2. Ajouter une colonne de type GUID (UNIQUEIDENTIFIER) avec NEWSEQUENTIALID() pour des besoins métiers. On se souvient, on doit l'utiliser comme valeur par défaut.
3. Index Clustered sur INT et Index Non-Clustered sur GUID si nécessaire.
   • En déposant l'instruction de Primary Key sur la colonne INT, vous obtiendrez automatiquement un index Clustered.
   • Pour la colonne GUID, vous pouvez ajouter un index Non-Clustered pour des besoins de recherche.

Avantages de cette Approche

• Performances : Les jointures et les recherches seront rapides grâce à l'INT.
• Unicité : La colonne GUID garantit l'unicité globale pour des besoins spécifiques.
• Sécurité : L'INT reste compact et séquentiel, limitant la fragmentation.

Exemple de Mise en Pratique

CREATE TABLE dbo.table_optim
(
    id   INT IDENTITY CONSTRAINT pk_table_optim PRIMARY KEY,
    guid UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID() NOT NULL ROWGUIDCOL
)
GO

CREATE UNIQUE NONCLUSTERED INDEX uncix_table_optim_guid ON dbo.table_optim (guid)
GO

Conclusion

L’expérimentation montre que :

• INT IDENTITY(1,1) est le meilleur choix pour la PK clustered.
• Les GUID NEWID() ne doivent pas être utilisés en PK clustered !
• NEWSEQUENTIALID() offre un compromis, mais reste plus volumineux qu'un INT, bien que l'index non-clustered sur ce GUID permette une identité unique sans fragmentation. Ce qui pour nous est une solution viable pour des identifiants publics.
• Même si certains services, y compris chez Microsoft, favorisent l'usage des GUID pour les relations entre tables, ou à même des services/applications, cela ne signifie pas que c'est une vérité absolue. Ces choix sont souvent guidés par une mécompréhension générale des développeurs qui popularisent ces pratiques sans en mesurer pleinement les impacts sur la performance et la gestion des index, dans un environnement de base de données. *(POV par l'auteur de l'article.)

Choisir entre INT et GUID, c’est choisir entre performances et unicité globale. Faites le bon choix pour éviter un enfer de page splits et de fragmentation.