CREATE TABLE (Transact-SQL) IDENTITY (propriété)

  • Article

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics

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 expose la propriété d’identité de lignes d’une colonne.

Conventions de la syntaxe Transact-SQL

Syntaxe

IDENTITY [ (seed , increment) ]

Cette syntaxe n’est pas prise en charge par le pool SQL serverless dans Azure Synapse Analytics.

Remarque

Pour afficher la syntaxe Transact-SQL pour SQL Server 2014 (12.x) et versions antérieures, consultez la Documentation sur les versions antérieures.

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.

Remarque

Dans Azure Synapse Analytics, les valeurs d’identité ne sont pas incrémentielles en raison de l’architecture distribuée de l’entrepôt de données. Pour plus d’informations, consultez Utilisation d’IDENTITY pour créer des clés de substitution dans un pool Synapse SQL.

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

Notes

Les colonnes d'identité peuvent être utilisées pour générer des valeurs de clé. La propriété d’identité sur une colonne garantit les conditions suivantes :

  • Chaque nouvelle valeur est générée en fonction de la valeur initiale 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 les conditions suivantes :

  • Unicité de la valeur : l’unicité doit être appliquée avec une contrainte PRIMARY KEY ou UNIQUE, ou avec un index UNIQUE.

    Remarque

    Azure Synapse Analytics ne prend pas en charge les contraintes PRIMARY KEY et UNIQUE, ni l’index UNIQUE. Pour plus d’informations, consultez Utilisation d’IDENTITY pour créer des clés de substitution dans un pool Synapse SQL.

  • Valeurs consécutives dans une transaction : il n’est pas garanti qu’une transaction insérant 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, la transaction doit utiliser soit un verrou exclusif sur la table, soit 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 des raisons de performance, et certaines valeurs affectées peuvent être perdues lors d’un échec de base de données ou du redémarrage du serveur. Cela peut entraîner des intervalles de valeur d'identité à l'insertion. Si les intervalles ne sont pas acceptables, l’application doit utiliser son propre mécanisme pour générer des valeurs de clé. L’utilisation d’un générateur de séquence avec l’option NOCACHE peut limiter les intervalles pour les transactions qui ne sont jamais commitées.

  • Réutilisation des valeurs : pour une propriété d’identité donnée, avec une valeur ou un incrément spécifiques, 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, 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.

Pour les tables à mémoire optimisée, la valeur initiale et l’incrément doivent être définis sur 1, 1. Le fait de définir la valeur initiale ou l’incrément sur une valeur autre que 1 provoque l’erreur suivante : The use of seed and increment values other than 1 is not supported with memory optimized tables.

Une fois la propriété d’identité définie sur une colonne, elle ne peut pas être supprimée. Le type de données peut être modifié tant que le nouveau type de données est compatible avec la propriété d’identité.

Exemples

R. Utiliser la propriété IDENTITY avec CREATE TABLE

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

USE AdventureWorks2022;
GO
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. Utiliser une syntaxe générique afin de trouver des intervalles entre les valeurs d’identité

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

Notes

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;

Contenu connexe