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' )
- Renvoi 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')
- Renvoi la valeur de l'incrément du
champ identity de la table spécifiée
- IDENT_SEED('MaTable')
- Renvoi 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 de
SELECT 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
table.
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
exemple.
- 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
1. Changer tout simplement 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.
2. On peut 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.
3. 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
avoir.
Pour avoir un identifiant unique au sein de la
base de données il existe le type timestamp (ou rowversion dans la norme ISO
SQL). C'est un type binaire de 8 octets équivalent à un varbinary(8) 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
suivante)
- 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
compteur.
-- 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
sys.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'identy
(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 !!!