Auto incrément IDENTITY avec SQL Server

Le mécanisme d’auto incrément de SQL Server via la propriété IDENTITY fait désormais partie de la norme SQL:2003.
Cependant il prend quelques distance avec cette dernière et permet de faire plus de choses que la norme n’a prévue…
Voyons en détail quels sont les possibilités de cet outil.

1) créer une colonne auto incrémentée

La création d’une colonne auto incrémentée se fait avec la propriété IDENTITY dont la syntaxe est la suivante :
IDENTITY [ ( #graine , #pas ) ]
ou #graine est la valeur de départ et #pas le pas d’incrément.
En l’absence des valeurs #graine et #pas le défaut est de 1 pour les deux (commence à 1 avec un pas de 1).
Ainsi :
IDENTITY (8192, 7)
Signifie que l’autoincrément commençera à 8192 et augmentera de 7 à chaque nouvelle ligne insérée.

Attention : il ne peut y avoir qu’une seule colonne auto incrémentée par table.

Exemple :


CREATE TABLE T_CLIENT_CLI 

(CLI_ID            INT NOT NULL IDENTITY(12485, 1) PRIMARY KEY, 

 CLI_NOM           CHAR(32) NOT NULL, 

 CLI_PRENOM        VARCHAR(25))

Contrairement à une idée reçue, ce n’est pas parce que vous dotez une colonne de la propriété IDENTITY, que cette dernière devient une clef primaire.
Deuxième idée reçue, ce n’est pas parce que vous dotez une colonne de la propriété IDENTITY, que les valeurs de cette colonne sont automatiquement unique.

2) insérer des lignes dans une table avec une colonne ayant une propriété IDENTITY

Lorsque vous voulez insérer des lignes dans une table pourvues d’un auto incrément, il convient de ne pas préciser cette colonne ou bien d’invoquer le mot clef DEFAULT.

Exemple :
— syntaxe omettant la liste des colonnes
INSERT INTO T_CLIENT_CLI VALUES ('DUPONT', 'Paul');

— syntaxe avec la liste des colonnes non IDENTITY
INSERT INTO T_CLIENT_CLI (CLI_NOM, CLI_PRENOM) VALUES ('DUPONT', 'Paul');

Voici le résultat :


SELECT * FROM T_CLIENT_CLI 

 

CLI_ID      CLI_NOM        CLI_PRENOM 

----------- -------------- ------------ 

12486       DUPONT         Paul 

12487       DUPONT         Paul

Si votre table est constituée d’une unique colonne avec auto incrément, il faut utiliser la syntaxe suivante :

CREATE TABLE T_IDENTITY_IDT (IDT_ID INT IDENTITY);
INSERT INTO T_IDENTITY_IDT DEFAULT VALUES;

3) forcer des valeurs dans la colonne ayant la propriété IDENTITY

Il est possible de forcer une valeur dans la colonne pourvue de la propriété IDENTITY. Pour ce faire il faut utiliser le flag :
SET IDENTITY_INSERT #MaTable { ON | OFF }
Ce flag ne peut porter que sur une seule table à la fois.
Dans ce cas, les INSERT devront mentionner impérativement la liste des colonnes et la colonne pourvue de la propriété IDENTITY devra y figurer.
Exemple :

SET IDENTITY_INSERT T_CLIENT_CLI ON;
INSERT INTO T_CLIENT_CLI (CLI_ID, CLI_NOM, CLI_PRENOM) VALUES (-1, 'DUPONT', 'Paul');
SET IDENTITY_INSERT T_CLIENT_CLI OFF;

Résultat :


SELECT * FROM T_CLIENT_CLI 

 

CLI_ID      CLI_NOM        CLI_PRENOM 

----------- -------------- ------------ 

-1          DUPONT         Paul 

12486       DUPONT         Paul 

12487       DUPONT         Paul

Notez que si vous utilisez une valeur supérieur au dernier « jeton » consommé, alors la valeur courante du générateur sera repoussée.

Exemple :

SET IDENTITY_INSERT T_CLIENT_CLI ON;
INSERT INTO T_CLIENT_CLI (CLI_ID, CLI_NOM, CLI_PRENOM) VALUES (1000000, 'DURAND', 'Marc');
SET IDENTITY_INSERT T_CLIENT_CLI OFF;

INSERT INTO T_CLIENT_CLI (CLI_NOM, CLI_PRENOM) VALUES ('DURAND', 'Marc');

Comme on le constate, le compteur d’auto incrément a été réinitialisé à la valeur suivant 1000000 :


SELECT * FROM T_CLIENT_CLI WHERE CLI_NOM = 'DURAND' 

 

CLI_ID      CLI_NOM        CLI_PRENOM 

----------- -------------- ------------ 

1000000     DURAND         Marc 

1000001     DURAND         Marc

4) récupérer la valeur du dernier auto incrément

SQL Server dispose de 3 outils pour récupérer la valeur du dernier auto incrément.
La variable @@IDENTITY fournit la valeur du dernier auto incrément de la session de l’utilisateur quelque soit la table qui a été auto incrémentée, avec une visibilité limitée à la session propre à l’utilisateur
La fonction SCOPE_IDENTITY() (pas d’argument) fournit la valeur du dernier auto incrément de la session de l’utilisateur dans l’étendue de code quelque soit la table qui a été auto incrémentée.
Enfin, IDENT_CURRENT(‘nom_table‘) permet de connaître la valeur du dernier incrément généré sur une table quelque soit l’utilisateur qui l’a fait générer.

Exemple :


INSERT INTO T_CLIENT_CLI (CLI_NOM, CLI_PRENOM) VALUES ('DUVAL', 'Luc'); 

 

SELECT @@IDENTITY AS IDENT,  

       SCOPE_IDENTITY() AS SCOP_IDENT,  

       IDENT_CURRENT('dbo.T_CLIENT_CLI') AS CURR_IDENT 

 

IDENT     SCOP_IDENT   CURR_IDENT 

--------- ------------ ------------ 

1000002   1000002      1000002

La différence entre @@IDENTITY et SCOPE_IDENTITY() est subtile. En effet, si l’insertion dans votre table déclenche un trigger qui insère des données dans une autre table (trigger AFTER INSERT) alors la variable @@IDENTITY contient l’incrément de cet autre table et la fonction SCOPE_IDENTITY() renvoie le dernier incrément visible dans l’unité de code (fonction, trigger, procédure…).

Démonstration :


INSERT INTO T_CLIENT_CLI (CLI_NOM, CLI_PRENOM) VALUES ('DUBOIS', 'Marie'); 

 

SELECT @@IDENTITY AS IDENT,  

       SCOPE_IDENTITY() AS SCOP_IDENT,  

       IDENT_CURRENT('master.sys.databases') AS CURR_IDENT 

 

IDENT     SCOP_IDENT   CURR_IDENT 

--------- ------------ ------------ 

2         1000003      NULL

5) réinitialiser les paramètres du compteur IDENTITY

Il est possible de réinitialiser les paramètres du compteur à l’aide d’une commande du DBCC (DataBase Consol Command) de nom CHECKIDENT :

DBCC CHECKIDENT ( 'nomTable' [ , { NORESEED | { RESEED [ , nouvelle_graine ] } } ] ) [ WITH NO_INFOMSGS ]

Exemple :


DBCC CHECKIDENT('T_CLIENT_CLI', RESEED, 123 ); 

 

INSERT INTO T_CLIENT_CLI (CLI_NOM, CLI_PRENOM) VALUES ('WELLES', 'Orson'); 

 

SELECT * FROM T_CLIENT_CLI WHERE CLI_NOM = 'WELLES'; 

 

CLI_ID      CLI_NOM       CLI_PRENOM 

----------- ------------- ---------------- 

124         WELLES        Orson

6) IDENTITY et ROLLBACK

Lorsque des transactions sont annulées (ROLLBACK) les valeurs des id « consommées » par les auto incrément ne sont pas récupérées. En effet le ROLLBACK ne concerne pas le mécanisme d’auto incrément (et heureusement, sinon, gare au paradoxe temporel…)

Démonstration :


-- Création des objets 

Code : 

 

CREATE DATABASE DB_TEST; 

GO 

 

USE DB_TEST; 

GO 

 

CREATE TABLE T (C INT IDENTITY); 

GO 

 

-- primo insertion 

Code : 

 

INSERT INTO T DEFAULT VALUES; 

GO 

 

SELECT * FROM T; 

GO 

 

C 

----------- 

1 

 

-- insertion annulée dans une transaction 

Code : 

 

BEGIN TRANSACTION 

INSERT INTO T DEFAULT VALUES; 

ROLLBACK TRANSACTION; 

GO 

 

SELECT * FROM T; 

GO 

 

C 

----------- 

1 

 

-- troisième insertion : 

Code : 

 

INSERT INTO T DEFAULT VALUES; 

GO 

 

SELECT * FROM T; 

GO 

 

C 

----------- 

1 

3

On voit bien que le « jeton » n°2 a été consommé par la transaction annulée !

7) accéder à la colonne autoincrémentée sans connaître son nom :

Dans une requête il est possible d’accéder à la colonne autoincrémentée (via IDENTITY) sans pour autant avoir besoin de connaître son nom.
Il suffit d’utiliser la pseudo colonne $IDENTITY.

Exemple :

Partant de la table :


CREATE TABLE T_CLIENT_CLI 

(CLI_ID            INT NOT NULL IDENTITY(12485, 1) PRIMARY KEY, 

 CLI_NOM           CHAR(32) NOT NULL, 

 CLI_PRENOM        VARCHAR(25))

Dans laquelle aucune insertion n’a encore eut lieu, faisons :


SELECT MIN($IDENTITY) AS MIN_ID, MAX($IDENTITY) AS MAX_ID 

FROM   T_CLIENT_CLI

Résultat :


MIN_ID      MAX_ID 

----------- ----------- 

NULL        NULL

Insérons quelques résultats :

INSERT INTO T_CLIENT_CLI
VALUES ('DUPONT', 'Paul'),
('DUVAL', 'Marc'),
('DUFOUR', 'Jean');

Recommençons la requête SELECT :


SELECT MIN($IDENTITY) AS MIN_ID, MAX($IDENTITY) AS MAX_ID 

FROM   T_CLIENT_CLI

Résultat :
MIN_ID MAX_ID
———– ———–
1 3

8) Résumés des outils de manipulation de l’auto incrément :

Génération d’auto incrément :


IDENTITY [ ( graine [, pas ] ) ]      Propriété autoincrément de colonne avec graine et pas de type entier (INT, BIGINT) - Utilisée avec CREATE et ALTER TABLE. 

IDENTITY [ ( graine [, pas ] ) ]      Fonction d'autoincrémentation pour une colonne de table créée à la volée - Utilisée uniquement avec SELECT INTO 

SET IDENTITY INSERT { ON | OFF }      Débranchement (ON) ou branchement (OFF) de l'autoincrément pour forçage de valeurs

Lecture de la valeur de l’auto incrément :


@@IDENTITY                            Variable globale de session contenant la valeur du dernier autoincrément inséré 

SCOPE_IDENTITY()                      Fonction de session contenant la valeur du dernier autoincrément inséré dans l'unité de code 

IDENT_CURRENT('<nom_table>')          Fonction retournant la valeur de l'autoincrément courant de la table passée en argument 

$IDENTITY                             pseudo colonne permettant la lecture directe de la valeur de l'autoincrément dans une requête SELECT appliquée à la table concernée

Pilotage de l’auto incrémentation :


DBCC CHECKIDENT ('<nom_table>'        Vérifie la valeur e l'autoincrément courant pour la table spécifiée et, si nécessaire, modifie cette valeur 

[ , { NORESEED | { RESEED [ , nouvelle_graine ] } } ] ) [ WITH NO_INFOMSGS ] 

IDENT_INCR('<nom_table>')             Renvoie la valeur d'incrément (pas) de l'autoincrément de la table considérée 

IDENT_SEED('<nom_table>')             Renvoie la valeur de la graine de l'autoincrément de la table considérée

Méta données :


sys.identity_columns                  Vue système donnant la liste des colonnes autoincrémentées par objet 

COLUMNPROPERTY                        Fonction retournant 1 si la colonne (column_name) de la table (object_id) est autoincrémentée 

(object_id , column_name , 'IsIdentity')  

sys.columns / is_identity             Vue renseignant dans la colonne is_identity quelle colonne de la table est autoincrémentée


-------- 

Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/ 

Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server 

www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation 

* * * * *  Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence  * * * * *

MVP Microsoft SQL Server

3 réflexions au sujet de « Auto incrément IDENTITY avec SQL Server »

CinePhil 4 octobre 2017 à 12 h 36 min

Quelques questions générales sur IDENTITY / Séquence / AUTO_INCREMENT…

1) Si je comprends bien, IDENTITY est une séquence interne à la table qui n’est pas visible en tant qu’objet indépendant, contrairement à la séquence (Oracle ou Postgresql).
L’AUTO_INCREMENT de MySQL serait donc du même genre que l’IDENTITY de SQL Server ?
(Oui, je sais que l’AUTO_INCREMENT de MySQMerde est pourri, j’en ai fait l’expérience récemment lors d’insertions en masse dans une table)

2) Il m’est arrivé plusieurs fois dans une base Oracle que des données aient été insérées dans une table sans utiliser la séquence associée. Le LAST_NUMBER de la séquence était ainsi inférieur au MAX(colonne_cle_primaire) de la table.
Conséquence : lors de l’insertion d’une ligne en utilisant la séquence, cette dernière délivrait un numéro déjà présent dans la clé primaire ! Aïe !
Obligé de passer une série de requêtes SELECT sequence.next() FROM DUAL pour monter artificiellement le LAST_NUMBER de la séquence au MAX(colonne_cle_primaire) de la table.
Cela peut-il arriver aussi avec IDENTITY ou bien le SGBDR tient compte de ce qui inséré dans la colonne IDENTITY pour déterminer le prochain numéro à délivrer ?

Connectez-vous pour répondre ↓
SQLpro Auteur de l’article29 juillet 2015 à 23 h 16 min

Il faut passer par une recréation de la table :
1) la recréer sous nom_table_bis avec la colonne IDENTITY
2) migrer les données de l’ancienne à la nouvelle table
3) la renommer avec sp_rename

A +

Connectez-vous pour répondre ↓
win_ubuntu 8 mars 2015 à 21 h 25 min

comment ajouter la contrainte identity à une colonne existante?:
alter table nom_table
add identity …

Connectez-vous pour répondre ↓