APSQL

IDENTITY ou UNIQUEIDENTIFIER ?

Lorsque l'on souhaite travailler avec des clés primaires auto générées par SQL Server il y 2 possibilités. Soit avoir recours à une colonne de type int avec IDENTITY soit une colonne de type UNIQUEIDENIFIER et de faire appel aux fonctions NEWID ou NEWSEQUENTIALID pour fournir une valeur par défaut. Mais comment faire le bon choix et comment le justifier ?

Pour répondre à cette délicate question le plus simple est d'examiner de plus près les avantages et inconvénients inhérents à chaque solution. Pour cela le test effectué de chaque côté va être le même : création de table, insertion d'un jeu d'essai, observation du résultat et mesure de l'espace disque utilisé. En plus de la colonne représentant la clé primaire, chaque table possède une colonne de type char(2000). 1000 lignes sont insérées dans chaque table à l'aide d'un script Transact SQL.

IDENTITY

L'option IDENTITY permet de fournir automatiquement une valeur numérique à la colonne sur laquelle elle est définie.

Lors de la définition de l'option IDENTITY sur une colonne de type int, il est possible de préciser la valeur de départ de compteur et le pas d'incrémentation de ce compteur. Afin de prendre en compte toutes les caractéristiques au niveau de l'espace disque utilisé 2 tests vont être définis :identity(1,1) et identity (1000,10).

Les tables de tests sont défnis de la façon suivante:

create table identity_test1(
  id int identity (1,1) 
	constraint pk_identity_test1 primary key,
  c1 char(2000) default replicate('a',2000)
 );
create table identity_test2(
  id int identity (1000,10) 
	constraint pk_identity_test2 primary key,
  c1 char(2000) default replicate('a',2000)
 );  
			

Le script transact SQL permet d'insérer 1000 lignes dans chaque table

declare @i int
 begin
   set @i=0;
   while (@i<1000) begin
    insert into identity_test1 default values;
    insert into identity_test2 default values;
	set @i=@i+1;
   end;
 end;
			

Un extrait des tables peut être alors affiché :

Extrait des tables avec identity

Les valeurs fournies par le type identity peut être retrouvé au moyen de @@IDENTITY, SCOPE_IDENTITY ou IDENT_CURRENT. Ces 3 fonctions diffère en fonction de l'étendue prise en compte pour retourner la dernière valeur de type identity fournie. Les lignes d'informations sont facilement manipulable par la connaissance de l'ID. Par contre si l'on souhaite fournir une valeur particulière pour la colonne ID l'opération est plus compliquée, car elle nécessite de désactiver le type identity à l'aide du paramètre set identity_insert.

Uniqueidentifier

Du côté de la colonne de type uniqueidentifier, la même expérience est réalisée .

Cette fois ci il est créé une table qui va utiliser la valeur fournie par la méthode NEWID et une autre par la méthode NEWSEQUENTIALID. Pour rappel ces méthode diffère car NEWID génère de façon aléatoire un identifiant unique en se basant sur le numéro d'identification de la carte réseau et un numéro unique à partir de l'horloge. Dans le cas de NEWSEQUENTIALID les valeurs sont fournies de façon séquentielle.

Le script de création des tables est donc :

create table newid_test(
  id uniqueidentifier default newid() 
	constraint pk_newid_test primary key,
  c1 char(2000) default replicate('a',2000)
 );
create table newsequentialid_test(
  id uniqueidentifier default newsequentialid() 
	constraint pk_newsequentialid_test primary key,
  c1 char(2000) default replicate('a',2000)
 );  
			

Puis de nouveau un jeu d'essai de 1000 lignes est inséré dans chaque table

declare @i int
 begin
   set @i=0;
   while (@i<1000) begin
    insert into newid_test default values;
    insert into newsequentialid_test default values;
	set @i=@i+1;
   end;
 end;
			

Lors de l'affichage d'un extrait de chaque table le caractère séquential des identifiant générés par NEWSEQUENTIALID() est évidant.

Extrait des tables avec uniqueidentifier

Il est également clair que ces valeurs sont difficilement manipulables de façon manuelle. Pourtant ces clés de type uniqueidentifier sont intéressante car les programme clients peuvent générer eux même cette valeur de type uniqueidentifier et chaque poste client est ainsi sur de générer des valeurs différentes. Dans le cas où la valeur uniqueidentifier est générée sur le serveur il est possible de récupérer cette valeur par l'intermédiaire de la clause output maintenant disponible sur l'instruction INSERT .

Stockage et espace disque occupé

Pour déterminer avec certitudes les avantages et inconvénients de chaque type, il est nécessaire de regarder les informations relatives au stockage physique des informations. Pour obtenir cette information il est fait appel à la fonction sys.dm_db_index_physical_stats qui permet de connaitre, entre autre le nombre de pages utilisés et la fragmentation des données utilisés par les index.

Résultat de sys.dm_db_index_physical_stats

La lecture du résultat met en avant le grand nombre de page utilisé par l'index dans le cas de l'utilisation de la fonction NEWID ainsi que la fragmentation très importante. Ceci à pour origine la nature aléatoire des données générées et donc la construction de l'index clustered de clé primaire est moins performant. Cette aspect peut être minimisé en reconstruisant l'index avec une valeur approprié pour le critère FILLFACTOR. L'autre élément pouvant expliquer l'espace occupé viens tout simplement des types de données. En effet une valeur de type int est codé sur 4 octets tandis qu'une valeur de type uniqueidentifier en réclame 16.

Cette consommation d'espace peut être vérifiée par l'exécution de sp_spaceused

Résultat de sp_spaceused

Finalement

En conclusion, il est possible de dire que lorsque cela est possible l'utilisation d'une clé primaire auto générée de type identity permet d'optimiser le stockage car les données sont mieux réparties (moins de fragmentation) et de façon plus dense (peu de pages utilisées pour le stockage d'information). Cette bonne structure physique conduit à bénéficier de bonne performance.

Dans le cas où la clé doit être de type uniqueidentifier, il faut préférer l'utilisation de NEWSEQUENTIALID.