Sql server : compteur et identity

Dernière mise à jour le par noctambule28 .

Définition de l'identity

Un champ identity est un champ numérique de type compteur, qui n'est pas nécessairement un « int » (c'est le type le plus couramment utilisé car permettant un panel assez large de valeur). C'est une propriété du champ qui s'applique à des nombres entiers

Les types possibles pour l'identity sont :

  • decimal(p, 0) : nombre à virgule précis, où p représente le nombre de chiffres
  • int : entier signé 32 bits
  • numeric(p, 0) : identique à decimal
  • smallint : entier signé 16 bits
  • bigint : entier signé 64 bits
  • tinyint : entier non signé 8 bits

La définition de ce type se fait grâce à la commande suivante :

IDENTITY [ (seed,increment) * [NOT FOR REPLICATION]

Où :

  • seed est la valeur initiale du compteur
  • increment est la valeur ajoutée à la valeur actuelle, peut être positive ou négative
  • NOT FOR REPLICATION indique que l'incrément ne sera pas fait si la source de l'insertion dans la table est provient d'une réplication

Le champ portant l'attribut IDENTITY n'est pas nécessairement clef primaire, il n'est pas obligatoire de l'indexer, il ne porte pas de contrainte unique. Ce champ ne doit pas permettre les valeurs nulles et ne doit pas avoir de valeur par défaut.

Il est par contre unique dans la table, en aucun cas vous ne pourrez en ajouter un deuxième dans la même table. Vous ne pouvez pas par défaut insérer de valeur directement dans un champ de ce type, sauf exceptions (voir plus loin).

Les valeurs de ce champ ne sont pas continues, et elles ne dépendent pas la transaction courante (et ne pas bas être affectées par un ROLLBACK).

Création

L'identity est lié à un champ dans une table, voici comment il est défini dans la déclaration d'une table

CREATE TABLE MaTable 
(
   Clef int IDENTITY(1,1) NOT NULL,
   MonChamp varchar(100)
)

==> Je reprends la définition de cette table tout au long de mes exemples de codes T-SQL

Pour connaître les propriétés de ce champ vous avez à votre disposition plusieurs fonctionnalités.

  • COLUMNPROPERTY ( OBJECT_ID('MaTable'), 'Clef' , 'IsIdentity' )
    • Renvoie 1 (TRUE) si le champ est identity, 0 (FALSE) si ce n'est pas le cas et NULL si les paramètres sont incorrects
  • IDENT_INCR('MaTable')
    • Renvoie la valeur de l'incrément du champ identity de la table spécifiée
  • IDENT_SEED('MaTable')
    • Renvoie la valeur initiale du champ identity de la table spécifiée
  • $IDENTITY (nouveauté SQL Server 2005)
    • Remplace le nom du champ qui possède l'attribue identity, il est possible du coup de l'utiliser quel que soit son nom sur la table manipulée.

Connaître la/les valeur(s) insérée(s) dans ce champ

Tout d'abord ne jamais utiliser deSELECT MAX(Clef) !!!!! Cette commande ne vous
provoquera que des problèmes. Il existe 1 seul et unique cas où elle fonctionnera convenablement, c'est si vous utiliser le niveau d'isolation SERIALIZABLE qui interdira toute insertion pendant le SELECT, cependant cela engendrera un verrouillage important de la

Il est possible de connaître les valeurs insérées dans ce type de champ grâce à :

  • @@IDENTITY
    • Renvoie la dernière valeur Identity insérée par la dernière commande exécutée dans la session en cours, si aucune valeur n'a été générée il renvoie NULL. Utilisez cette commande si vous souhaitez récupérer l'Id généré dans une procédure stockée par
  • SCOPE_IDENTITY
    • Renvoie la dernière valeur Identity insérée par la dernière commande exécutée dans la portée en cours (par exemple dans la procédure stockée dans laquelle il se trouve). Privilégiez cette commande, particulièrement si vous utilisez MARS (Multiple Active ResultSet) sous SQL Server 2005.
  • IDENT_CURRENT('MaTable')
    • Renvoie la dernière valeur Identity insérée par n'importe quelle commande sur cette table, quel que doit la session qui l'a exécutée. Attention les mêmes problèmes qu'avec SELECT MAX(Clef) risquent de se produire avec cette commande.

Dans tous les cas seule la dernière valeur est renvoyée. Si vous voulez obtenir la liste des valeurs insérées il vous faudra (et sous SQL Server 2005 uniquement) utiliser la clause OUTPUT :

DECLARE @liste TABLE 
(ChampID int)
INSERT INTO MaTable (MonChamp)

OUTPUT inserted.Clef INTO @liste
SELECT ChampA
FROM UneAutreTable

SELECT * FROM @liste

Output s'appuie obligatoirement sur une variable de type table dans la clause INTO. Comme il s'agit de SQL Server 2005 vous pouvez remplacer inserted.Clef, par inserted.$IDENTITY pour obtenir un code plus générique.

Modifier la valeur courante d'un identity

Il y a 3 possibilités pour manipuler la valeur courante d'un champ identity

Changer la valeur courante par une autre

  • Si la valeur actuelle de l'identity est inférieur à la valeur max du champ
  • L'identity prend alors cette valeur
DBCC CHECKIDENT('MaTable', RESEED)

Force la valeur courante par une nouvelle, ici 234

DBCC CHECKIDENT('MaTable', RESEED, 234)

La valeur suivante attribuée sera celle affectée (234) plus la valeur de l'incrément.

Forcer temporairement l'insertion de valeur dans le champ en question

Désactive l'autoincrément

SET IDENTITY_INSERT MaTable ON

Insère une valeur particulière sur le champ

INSERT MaTable (Clef, MonChamp) VALUES(-10,'AAAA')

Réactive l'autoincrément

SETIDENTITY_INSERT MaTable OFF

La valeur courante n'est pas modifiée dans ce
cas, attention tout de même à ne pas provoquer de télescopage en insérant des
valeurs de compteur que l'identity risque d'atteindre. C'est pour cette raison
que dans l'exemple j'ai inseré la valeur -10, mon compteur étant positif, je
n'ai aucune chance de tomber sur cette valeur.

Remettre la valeur courante à sa valeur d'origine en supprimant tous les enregistrements de la table

TRUNCATE TABLE MaTable

Attention au TRUNCATE TABLE qui est une opération accessible uniquement à des utilisateurs ayant des privilèges important sur le serveur. De plus cette commande ne peut
être exécutée s'il existe des clefs étrangères sur la table. A noter qu'un
DELETE simple n'affecte pas la valeur du champ identity.

Copier une table possedant un IDENTITY

Je pense que vous serez d'accord avec le fait que la méthode la plus rapide pour la copie d'une table dans SQL Server est la syntaxe SELECT INTO, qui en une seule commande duplique la structure de la table et ses données.

==> Dans les exemples qui suivent j'utilise volontairement une table temporaire locale (marqué par un #). celà fonctionne de la même manière avec une table classique.

Mais que se passe t'il si un champ de type IDENTITY se trouve dans la table source ?

SELECT * INTO #TableTemp 
FROM MaTable

Pour réinitialiser le champ identity avec un
nouveau compteur par exemple, on pourra utiliser :

SELECT IDENTITY(int, 1, 1) AS Clef, MonChamp INTO #MaTableTemp FROM MaTable

Ici c'est la fonction IDENTITY qui a été utilisée, elle permet la création dans la table cible d'un champ identity avec les caractéristiques renseignés, par exemple ici : Valeur initiale 1, incrément 1 et type du champ int. Vous pouvez de cette façon ajouter un champ identity à
une table n'en ayant pas lors de la copie.

A l'opposé comment supprimer l'attribut identity du champ lors d'une copie de la table ?

SELECT CAST(Clef AS int) AS Clef, MonChamp INTO #MaTableTemp FROM MaTable

On a tout simplement effectué un CAST (convertion).

Les alternatives

Certains d'entre vous je suis sûr trouve
l'identity limité pour un certains nombres de raisons. Et bien nous allons voir
d'autres solutions possibles pour palier aux quelques besoins que vous pourriez

Pour avoir un identifiant unique au sein de la base de données il existe le type timestamp (ou rowversion dans la norme ISO dont la valeur est unique au sein de la base de données. Cependant sa valeur change dès qu'il se produit une modification sur l'un des champs de l'enregistrement. De ce fait il sert essentiellement de « traceur » de modification dans la base de données plutôt que d'identifiant unique, celà le rend très utilile dans des batch incrémentaux.

Autre alternative intéressante qui fera l'objet d'un prochain article, c'est l'utilisation du GUID qui a le double avantage d'être unique quel que soit la machine (utilisation indispensable d'une carte réseau dans ce cas) et d'être aléatoire (ne peut être deviné ou déduit de ses
valeurs précédentes).

Dernières solutions qui ressemblent un peu au principe des séquences de certains moteurs concurrents. L'idée étant de générer ses propres compteurs au sein d'une base de données ou même d'un ensemble de base de données ou de serveurs.

On va distinguer 3 catégories de compteurs

  • Séquence lâche
    • Comme l'identity, on ne réserve pas la valeur, elle peut être perdue (non utilisée et on passe à la valeur
  • Séquence non lâche
    • On réserve la valeur, en cas de ROLLBACK elle est conservée.
  • Séquence dense
    • On prend la première valeur non utilisée dans les valeurs possible (on réutilise les valeurs des enregistrements supprimés)

==> Les compteurs sont présentés dans l'ordre du moins contraignant (donc plus performant) au plus contraingant (donc moins performant).

La séquence lâche, que vous ne pourrez pas gérer dans le cadre d'une transaction (valeurs perdues possibles). Equivalente à l'identity pour plusieurs tables. Nécessite autant de tables que de compteurs. Le cas ici est très simple, on s'appuie sur une table contenant un identity, mais qui va être utilisé par plusieurs tables. Cette table de compteur sera en
permanence étant donné que seul nous intéresse la valeur courante de notre

Table des compteurs

IF OBJECT_ID('Sequence_Lache','U') IS NOT NULL

 DROP TABLE Sequence_Lache

GO


CREATE TABLE Sequence_Lache

(

 Num int Identity(1,1) PRIMARY KEY,

 Seq int NOT NULL DEFAULT(1)

)

GO

Procédure stockée d'utilisation

IF OBJECT_ID('GetSeq_Lache','P') IS NOT NULL

 DROP PROCEDURE GetSeq_Lache

GO


CREATE PROCEDURE 
GetSeq_Lache @value int OUTPUT

AS

DECLARE @acttran int


SET @acttran = @@TRANCOUNT


IF @@TRANCOUNT= 0

 BEGIN TRAN


SAVE TRAN GetSeq

INSERT INTO Sequence_Lache (Seq)

VALUES(0)


SET @value = @@IDENTITY


ROLLBACK TRAN GetSeq


IF @acttran != @@TRANCOUNT

 ROLLBACK TRAN

GO

Test de la génération

DECLARE @val int

EXEC GetSeq_Lache @val OUTPUT

SELECT @val

La séquence non lâche, gérable dans la cadre d'une transaction. Nécessite une seule table pour un ensemble de compteurs.

Table des compteurs

IF OBJECT_ID('Sequence_NonLache','U') IS NOT NULL

 DROP TABLE Sequence_NonLache

GO


CREATE TABLE 
Sequence_NonLache

(

 Num int Identity(1,1) PRIMARY KEY,

 Seq int NOT NULL DEFAULT(1),

 Inc int NOT NULL DEFAULT(1)

)

GO

Ajoute un compteur

INSERT INTO Sequence_NonLache (Seq, Inc)

VALUES(1,1)

GO

Procédure stockée d'utilisation

IF OBJECT_ID('GetSeq_NonLache','P') IS NOT NULL

 DROP PROCEDURE GetSeq_NonLache

GO

Procédure stockée d'utilisation

CREATE PROCEDURE GetSeq_NonLache @num int, @value int OUTPUT
AS

DECLARE @acttran int
SET @acttran = @@TRANCOUNT
IF @@TRANCOUNT= 0

 BEGIN TRAN


UPDATE Sequence_NonLache
SET Seq = Seq + Inc
WHERE Num = @num


SELECT @value = Seq
FROM Sequence_NonLache
WHERE Num = @num


IF @acttran != @@TRANCOUNT
 COMMIT TRAN
GO

Test de la génération

DECLARE @val int
EXEC GetSeq_NonLache 1, @val OUTPUT
SELECT @val

Dernière solution le compteur dense, qui va récupérer les « trous » dans la liste des identifiants. On pourra s'appuyer dans le cas de cette solution sur table contenant la liste de valeur de compteur qui servira de référence et pour déterminer la première valeur de libre faire une jointure entre les table pour déterminer les valeurs manquantes, récupérer
la valeur minimale, verrouiller l'enregistrement correspondant et le tour est
joué.

Aller plus loin...

Où se trouvent les informations concernant l'identity dans les tables systèmes ?

Comme c'est un attribut de champ, vous les trouverez sans surprise dans la table système syscolumns (ou la vue système syscolumns sur SQL Server 2005).

SELECT autoval, colstat, status
FROM sys.syscolumns

Ces 3 champs contiennent toutes les informations sur les identity. autoval contient la valeur courante, initiale et celle de l'incrément de l'identity ainsi que d'autres options, c'est un champ binaire (Je vous déconseille de toucher à autoval directement). Le champ colstat
peut se révéler intéressant à modifier, car c'est lui qui porte le status de l'identity (avec le champ status) avec entre autre l'attribut NOT FOR REPLICATION. Le champ status permet de savoir si ce champ est identity.

Liste des champs identity

SELECT id, colid, autoval, status, colstat
FROM sys.syscolumns
WHERE status & 0x80 = 0x80

Plus simplement sous SQL Server 2005 on utilisera les vues systèmes pour identifier ces champs.

Liste des champs identity

SELECT column_id, name, is_identity
FROM sys.columns
WHERE is_identity = 1

Sous SQL Server 2000 il est possible de modifier sous certaines conditions les tables systèmes, même si cela n'est pas conseillé. S'il vous arrivait de toucher aux champs définissant l'identity, utilisez impérativement DBCC CHECKIDENT après la modification pour vous assurez que les valeurs des champs définissant l'identity sont dans un état cohérant.
Sous SQL Server 2005 vous n'avez plus cette possibilité.

EXEC sp_configure 'allow 
updates', 1 
RECONFIGURE WITH OVERRIDE
GO

BEGIN TRAN
UPDATE syscolumns SET -- A vous de compléter ;o)
DBCC CHECKIDENT('matable')
COMMIT TRAN
GO


EXEC 
sp_configure 'allow updates', 
0 
RECONFIGURE WITH OVERRIDE
GO

Dernier point, sachez que le changement de la valeur du champ de type identity est une opération journalisée (qui par contre ne peut être annulée via un ROLLBACK), c'est-à-dire qu'elle nécessite l'inscription d'informations dans le journal de transaction. Ceci s'explique par le fait que la valeur courante du compteur est préservée dans la table système
évoquée ci-dessus.

Voilà c'est tout sur l'identity... En commençant avec çà je ne pensais pas en faire 8 pages sous Word... Comme quoi le sujet est vaste et souvent malheureusement mal connu.

Bon développements avec SQL Server !!!

A voir également
Ce document intitulé « Sql server : compteur et identity » 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.
Team system for database professionals
Les bases de données relationnelles
Rejoignez-nous