Sql server - global unique identifier

Dernière mise à jour le par cs_skweeky .

GUID - UniqueIdentifier ?
GUID signifie Global Unique Identifier, c’est un type qui a fait son apparition dans le monde Microsoft entre autre pour identifier de manière unique un objet COM.

Le GUID est une valeur binaire de 16 octets, elle est générée par rapport à l’adresse MAC (Adresse physique de la carte) de la carte réseau présente sur la machine (Ethernet ou Token Ring). Cette adresse MAC est nécessairement unique (unicité garantie pour les cartes Ethernet, pas forcément pour d’autres adaptateurs) ce qui fait que le GUID lui aussi doit être unique quel que soit la machine où il se trouve généré. L’autres facteur de la création d’un GUID est tout simplement le temps, le reste c’est de l’aléatoire (à base de générateur pseudo-aléatoire).

Il y a plusieurs manière de générer le GUID, il y a même une composante de version incluse dans le GUID lui-même pour bien indiqué la manière dont il a été généré. Depuis Windows 2000 on utilise les fonctions de cryptographie de Windows pour générer cette valeur, l’adresse MAC n’étant plus qu’une des bases de la génération.

La forme de représentation du GUID est la suivante :

{XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX}

Les X représentent le nombre hexadécimal représentant le GUID.

Pour plus d’informations sur le sujet :
http://fr.wikipedia.org/wiki/GUID

Dans SQL Server

Sous SQL Server, le GUID est supporté depuis SQL Server 2000, le type de champ associé à un champ de type GUID est uniqueidentifier. Il est nécessaire de générer une valeur par défaut pour ce champ si vous souhaitez qu’il se remplisse seul.

Voici le code de création d’une table avec un tel champ :
CREATE TABLE MaTable<?xml:namespace prefix o ns "urn:schemas-microsoft-com:office:office" /?>

(

      Clef uniqueidentifierDEFAULTNEWID(),

      MonChamp varchar(100)

)

Ou depuis SQL Server 2005 une variante est possible

CREATE TABLE MaTable

(

      Clef uniqueidentifierDEFAULTNEWSEQUENTIALID(),

      MonChamp varchar(100)

)

La 2ème méthode génère des GUID triés ou séquentiel.

Les utilisations

SQL Server se sert des GUID lors des réplications bidirectionnelles (fusion ou transactionnelle), pour justement avoir un identifiant unique quel que soit le serveur où il est généré.

A cet effet la propriété ROWGUIDCOL permet d’indiquer quel est le champ qui est utilisé pour identifier de manière unique un enregistrement, c'est-à-dire celui qui sera utilisé dans le cadre de la réplication bidirectionnelle.

$ROWGUID fait référence à ce même champ mais sans le nommer, cela permet de rendre le script plus générique en cas de changement du nom du champ par exemple.

NEWID

Cette fonction peut être utilisée dans n’importe quel endroit de votre code Transact-SQL.

Les particularités des GUID générés par cette fonction sont :

·         L’unicité quel que soit la machine, si celle-ci possède une carte TokenRing ou Ethernet

·         L’impossibilité de définir la valeur du prochain GUID à partir d’un autre, les valeurs sont donc aléatoires

Il est aussi possible de créer ce type de valeur directement depuis un environnement de développement, via .Net avec ici un exemple en C# :

            Guid myId = System.Guid.NewGuid();

Pour plus d’informations sur l’API utilisée pour la génération de ce type de valeur :
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rpc/rpc/uuidcreate.asp

Comment récupérer la valeur générée ?

Plusieurs possibilités, on peut générer le GUID au sein de l’application et dans ce cas passer sa valeur comme paramètre. De la même façon dans du code T-SQL on peut effectuer la même chose :

DECLARE @mavar uniqueidentifier

SET @mavar =NEWID()

 

-- On utilisera @mavar ici

Avec SQL Server 2005 il est aussi possible de faire cela :

-- Attention cet exemple est simple on ne récupère que 1 enregistrement

-- Mais il est possible de récuperer la listes des GUID inserés

DECLARE @mesguid table(guid uniqueidentifier)

 

INSERT INTO matable (champ1, champ2)

OUTPUT inserted.champguid INTO @mesguid

VALUES ( 'A' , 'B' )

 

SELECT @mesguid

Sinon sous SQL Server 2000 il n’y a pas de méthode type @@IDENTITY pour récupérer la dernière valeur de GUID générée dans la session.

Quelques usages dans SQL Server

Tirage aléatoire de données :

-- Renvoie 1 enregistrement de la table
SELECT TOP 1 *

FROM MaTable

ORDER BY NEWID ()

Génération de données aléatoires :

-- Renvoie un entier 32 bit signé
SELECT CHECKSUM ( NEWID ())

NEWSEQUENTIALID

Cette fonction ne peut être utilisée que dans la définition d’une valeur par défaut, elle est d’ailleurs non reconnue par l’interface graphique (SQL Server Management Studio), mais cela devrait être corrigé.

Les particularités des GUID générés par cette fonction sont :

·         L’unicité quel que soit la machine, si celle-ci possède une carte TokenRing ou Ethernet

·         La valeur n’est pas aléatoire, elle est obtenue grâce à un numéro de séquence  et l’adresse de carte réseau de la machine (adresse MAC).

·         Il est possible très facilement de déterminer la machine qui a généré le GUID

Pour plus d’informations sur l’API utilisée pour la génération de ce type de valeur :
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rpc/rpc/uuidcreatesequential.asp

Comme on le voit au niveau API, la génération d’un GUID séquentiel s’appuie toujours sur une valeur de séquence, il est donc très simple de trouver la séquence de GUID générée quand on a une de ces valeurs. On n’utilisera donc pas ce type de valeur dans un environnement où il est impératif pour des questions de confidentialité de ne pas avoir de relations entre les valeurs.

Comment récupérer la valeur générée pour ce champ

Du fait que la fonction ne peut être placé que dans une valeur par défaut il n’y a moins de possibilités de connaître celle-ci.

On pourra utiliser la clause OUTPUT comme pour la valeur générée par NEWID :

-- Attention cet exemple est simple on ne récupère que 1 enregistrement

-- Mais il est possible de récuperer la listes des GUID inserés

DECLARE @mesguid table(guid uniqueidentifier)

 

INSERT INTO matable (champ1, champ2)

OUTPUT inserted.champguid INTO @mesguid

VALUES ( 'A' , 'B' )

 

SELECT @mesguid

Il est aussi possible de se baser sur un MAX ou TOP, mais attention il faut verrouiller la table pour que cela fonctionne, et cela peut nuire aux accès aux données dans la base de données (je ne recommande pas cette solution).

-- On interdit toute insertion par une session tierce

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

 

-- On démarre une nouvelle transaction

BEGIN TRANSACTION

 

-- On insère une enregistrement

INSERT INTO matable (champ1, champ2)

VALUES ( 'A' , 'B' )

 

-- Les GUID étant séquentiels il est plus facile de récupérer leur valeur

SELECT TOP 1 champguid

FROM matable

ORDER BY champguid

 

-- On valide la transaction

COMMIT TRANSACTION

 

-- On repasse le mode d'isolation par défaut

SET TRANSACTION READ COMMITTED

Si maintenant vous souhaitez toujours générez la valeur depuis l’application ou depuis du code T-SQL avant de l’utiliser voici le code (en C#) qu’il va vous falloir.

using System.Runtime.InteropServices;

        [DllImport("rpcrt4.dll", SetLastError = true) *

        static extern int UuidCreateSequential(outGuid guid);

        static Guid UuidCreateSequential()

        {

            const int RET_OK = 0;

            Guid uuid;

 

            int ret = UuidCreateSequential(out uuid);

           

            return uuid;

        }

Vous pourrez bien entendu incorporer cette fonction dans une fonction ou procédure stockée SQLCLR ce qui pourra vous permettre facilement d’utiliser les GUID séquentiel avec plus de facilité.

Faisons maintenant un petit test avec IPCONFIG pour connaître l’adresse MAC de notre carte réseau :

Media State . . . . . . . . . . . : Media disconnected

specific DNS Suffix  . :

Description . . . . . . . . . . . : Bluetooth Device (Personal Area Network)
Physical Address. . . . . . . . . : 00-16-41-5C-67-8E
DHCP Enabled. . . . . . . . . . . : Yes
Autoconfiguration Enabled . . . . : Yes

Et jetons un coup d’œil aux données générées par cette fonction :

0feca252-7a4a-11db-94bc-0016415c678e

7a4a-11db-94bc-0016415c678e

7a4a-11db-94bc-0016415c678e

7a4a-11db-94bc-0016415c678e

7a4a-11db-94bc-0016415c678e

7a4a-11db-94bc-0016415c678e

On voit bien ici notre numéro de séquence et … l’adresse MAC de la carte BlueTooth de mon ordinateur.

Comparaisons et tri

Attention à la manière dont sont comparé et trié ces types dans SQL Server, vous trouverz ici toutes les informations ç ce sujet :
http://blogs.msdn.com/sqlprogrammability/archive/2006/11/06/how-are-guids-compared-in-sql-server-2005.aspx

Performances

Premier points sur les performances, la suite viendra dans le prochain article entre les GUID et les IDENTITY. Ici il s’agit plutôt de comparer rapidement NEWSEQUENTIALID et NEWID au niveau du type de GUID.

Le GUID généré de manière séquentielle est généré de manière plus rapide étant donné qu’il n’y a pas de logique de cryptographie à utiliser. Autre avantage il est séquentiel et dans le cas de données indexé c’est un très gros avantage dans SQL Server.

En effet l’utilisation du NEWSEQUENTIALID va se comporter comme la génération d’un champ identity (et même mieux dans certains cas) et ne va pas fragmenter les index. Il garde cependant le désavantage de tous les GUID, sa taille, en effet 16 octets c’est beaucoup en terme de taille de stockage.

L’autre GUID, généré quant à lui par NEWID s’il n’est pas indexé peut être utilisé sans problème, il présente le même désavantage que son collègue, c'est-à-dire sa taille. Si par contre le champ est indexé ce type de valeur est à proscrire totalement, une valeur de type aléatoire comme celle-ci provoquant nécessairement de la fragmentation et donc un ralentissement des insertions, des mises à jour et de certaines lectures (lecture de plages d’enregistrements).

Et enfin

Dans la dernière partie de cette suite d’article je vous parlerais des 3 types d’identifiants GUID (séquentiel ou non) et identity en vous présentant les problèmes de performance ou gain apportés au cas par cas.

Adresse d'origine

A voir également
Ce document intitulé « Sql server - global unique identifier » issu de CodeS SourceS (codes-sources.commentcamarche.net) est mis à disposition sous les termes de la licence Creative Commons. Vous pouvez copier, modifier des copies de cette page, dans les conditions fixées par la licence, tant que cette note apparaît clairement.
Les bases de données relationnelles
Connexion distante à sql server 2005
Rejoignez-nous