IDENTITY (Propriété) (Transact-SQL)

Article
10/31/2013

Crée une colonne d'identité dans une table. Cette propriété est utilisée avec les instructions Transact-SQL CREATE TABLE et ALTER TABLE.

[!REMARQUE]

La propriété IDENTITY diffère de la propriété SQL-DMO Identity qui représente la propriété d'identité de lignes d'une colonne.

Icône Lien de rubrique Conventions de la syntaxe Transact-SQL

Syntaxe

IDENTITY [ (seed , increment) ]

Arguments

seed
Valeur utilisée pour la toute première ligne chargée dans la table.
increment
Valeur d'incrément ajoutée à la valeur d'identité de la ligne précédemment chargée.

Vous devez spécifier à la fois la valeur initiale et l'incrément, ou aucun des deux. Si vous ne spécifiez aucun des deux, la valeur par défaut est (1,1).

Notes

Les colonnes d'identité peut être utilisées pour générer des valeurs de clé. La propriété d'identité sur une colonne garantit ce qui suit :

Chaque nouvelle valeur est générée en fonction de la valeur et de l'incrément actuels.
Chaque nouvelle valeur pour une transaction spécifique est différente des autres transactions simultanées sur la table.

La propriété d'identité sur une colonne ne garantit pas ce qui suit :

Unicité de la valeur – L'unicité doit être appliquée à l'aide d'une contrainte PRIMARY KEY ou UNIQUE, ou d'un index UNIQUE.
Valeurs consécutives dans une transaction – Il n'est pas garantit qu'une transaction qui insère plusieurs lignes obtienne des valeurs consécutives pour les lignes car d'autres insertions simultanées peuvent se produire sur la table. Si les valeurs doivent être consécutives, alors la transaction doit utiliser un verrou sur la table ou le niveau d'isolation SERIALIZABLE.
Valeurs consécutives après le redémarrage du serveur ou d'autres échecs – SQL Server peut mettre en cache les valeurs d'identité pour garantir les performances, et certaines valeurs affectées peuvent être perdues lors d'un échec de base de données ou le redémarrage du serveur. Cela peut entraîner des intervalles de valeur d'identité à l'insertion. Si les intervalles ne sont pas acceptables, alors l'application doit utiliser un générateur de séquence avec l'option NOCACHE ou utiliser son propre mécanisme pour générer des valeurs clés.
Réutilisation des valeurs – Pour une propriété d'identité donnée, avec une valeur/un incrément spécifique, les valeurs d'identité ne sont pas réutilisées par le moteur. Si une instruction d'insertion donnée échoue, ou si l'instruction d'insertion est restaurée, alors les valeurs d'identité consommées sont perdues et ne peuvent plus être générées. Cela peut entraîner des intervalles lorsque les valeurs d'identité ultérieures sont générées.

Ces restrictions sont dues à la conception et visent à améliorer les performances, car elles sont acceptables dans la plupart des situations. Si vous ne pouvez pas utiliser les valeurs d'identité en raison de ces restrictions, créez une table distincte contenant une valeur actuelle et gérez l'accès à la table et l'affectation de numéro dans votre application.

Si une table possédant une colonne d'identité est publiée pour la réplication, la colonne doit être gérée de manière à ce qu'elle soit compatible avec le type de réplication utilisée. Pour plus d'informations, consultez Répliquer des colonnes d'identité.

Une seule colonne d'identité peut être créée par table.

Exemples

A.Utilisation de la propriété IDENTITY avec CREATE TABLE

L'exemple suivant crée une nouvelle table avec la propriété IDENTITY pour incrémenter automatiquement un numéro d'identification.

USE AdventureWorks2012
IF OBJECT_ID ('dbo.new_employees', 'U') IS NOT NULL
   DROP TABLE new_employees;
GO
CREATE TABLE new_employees
(
 id_num int IDENTITY(1,1),
 fname varchar (20),
 minit char(1),
 lname varchar(30)
);

INSERT new_employees
   (fname, minit, lname)
VALUES
   ('Karin', 'F', 'Josephs');

INSERT new_employees
   (fname, minit, lname)
VALUES
   ('Pirkko', 'O', 'Koskitalo');

B.Utilisation d'une syntaxe générique afin de trouver des écarts entre les valeurs d'identité

L'exemple suivant illustre une syntaxe générique qui permet de trouver des écarts entre les valeurs d'identité lorsque des données sont supprimées.

[!REMARQUE]

La première partie du script Transact-SQL suivant sert uniquement d'illustration. Vous pouvez exécuter le script Transact-SQL qui commence par le commentaire : -- Create the img table.

-- Here is the generic syntax for finding identity value gaps in data.
-- The illustrative example starts here.
SET IDENTITY_INSERT tablename ON
DECLARE @minidentval column_type
DECLARE @maxidentval column_type
DECLARE @nextidentval column_type
SELECT @minidentval = MIN($IDENTITY), @maxidentval = MAX($IDENTITY)
    FROM tablename
IF @minidentval = IDENT_SEED('tablename')
   SELECT @nextidentval = MIN($IDENTITY) + IDENT_INCR('tablename')
   FROM tablename t1
   WHERE $IDENTITY BETWEEN IDENT_SEED('tablename') AND 
      @maxidentval AND
      NOT EXISTS (SELECT * FROM tablename t2
         WHERE t2.$IDENTITY = t1.$IDENTITY + 
            IDENT_INCR('tablename'))
ELSE
   SELECT @nextidentval = IDENT_SEED('tablename')
SET IDENTITY_INSERT tablename OFF
-- Here is an example to find gaps in the actual data.
-- The table is called img and has two columns: the first column 
-- called id_num, which is an increasing identification number, and the 
-- second column called company_name.
-- This is the end of the illustration example.

-- Create the img table.
-- If the img table already exists, drop it.
-- Create the img table.
IF OBJECT_ID ('dbo.img', 'U') IS NOT NULL
   DROP TABLE img
GO
CREATE TABLE img (id_num int IDENTITY(1,1), company_name sysname)
INSERT img(company_name) VALUES ('New Moon Books')
INSERT img(company_name) VALUES ('Lucerne Publishing')
-- SET IDENTITY_INSERT ON and use in img table.
SET IDENTITY_INSERT img ON

DECLARE @minidentval smallint
DECLARE @nextidentval smallint
SELECT @minidentval = MIN($IDENTITY) FROM img
 IF @minidentval = IDENT_SEED('img')
    SELECT @nextidentval = MIN($IDENTITY) + IDENT_INCR('img')
    FROM img t1
    WHERE $IDENTITY BETWEEN IDENT_SEED('img') AND 32766 AND
      NOT    EXISTS (SELECT * FROM img t2
          WHERE t2.$IDENTITY = t1.$IDENTITY + IDENT_INCR('img'))
 ELSE
    SELECT @nextidentval = IDENT_SEED('img')
SET IDENTITY_INSERT img OFF