Générer des valeurs automatiques

  • 3 minutes

Vous devrez peut-être générer automatiquement des valeurs séquentielles pour une colonne d’une table spécifique. Transact-SQL fournit deux façons de procéder : utiliser la propriété IDENTITY avec une colonne spécifique dans une table, ou définir un objet SEQUENCE et utiliser des valeurs générées par cet objet.

La propriété IDENTITY

Pour utiliser la propriété IDENTITY, définissez une colonne à l’aide d’un type de données numérique avec une échelle de 0 (c’est-à-dire des nombres entiers uniquement) et incluez le mot clé IDENTITY. Les types autorisés incluent tous les types d’entiers et les types décimaux où vous donnez explicitement une échelle de 0.

Vous pouvez également spécifier une valeur initiale facultative (valeur de départ) et un incrément (valeur de pas). Si vous ne spécifiez pas de valeur initiale et d’incrément, les deux sont définis sur 1.

Notes

La propriété IDENTITY est spécifiée au lieu de spécifier NULL ou NOT NULL dans la définition de colonne. Toute colonne avec la propriété IDENTITY est configurée automatiquement pour ne pas accepter la valeur NULL. Vous pouvez spécifier NOT NULL uniquement pour référence, mais si vous spécifiez la colonne comme NULL (pouvant accepter la valeur NULL), l’instruction de création de table génère une erreur.

Une seule colonne d’une table peut avoir la propriété IDENTITY définie. Elle est fréquemment utilisée comme clé primaire ou secondaire.

Le code suivant illustre la création de la table Sales.Promotion utilisée dans les exemples de la section précédente, mais cette fois avec une colonne d’identité nommée PromotionID comme clé primaire :

CREATE TABLE Sales.Promotion
(
PromotionID int IDENTITY PRIMARY KEY,
PromotionName varchar(20),
StartDate datetime NOT NULL DEFAULT GETDATE(),
ProductModelID int NOT NULL REFERENCES Production.ProductModel(ProductModelID),
Discount decimal(4,2) NOT NULL,
Notes nvarchar(max) NULL
);

Notes

Les détails complets de l’instruction CREATE TABLE n’entrent pas dans le cadre de ce module.

Insertion de données dans une colonne d’identité

Lorsque la propriété IDENTITY est définie pour une colonne, les instructions INSERT dans la table ne spécifient généralement pas de valeur pour la colonne IDENTITY. Le moteur de base de données génère une valeur à l’aide de la valeur disponible suivante pour la colonne.

Par exemple, vous pouvez insérer une ligne dans la table Sales.Promotion sans spécifier de valeur pour la colonne PromotionID :

INSERT INTO Sales.Promotion
VALUES
('Clearance Sale', '01/01/2021', 23, 0.10, '10% discount')

Notez que même si la clause VALUES n’inclut pas de valeur pour la colonne PromotionID, vous n’avez pas besoin de spécifier une liste de colonnes dans la clause INSERT : les colonnes d’identité sont exemptes de cette exigence.

Si cette ligne est la première insérée dans la table, le résultat est une nouvelle ligne comme suit :

PromotionID

PromotionName

StartDate

IDModèleProduit

Remise

Notes

1

Ventes en liquidation

2021-01-01T00:00:00

23

0.1

10 % de remise

Lorsque la table a été créée, aucune valeur de départ ou d’incrément n’a été définie pour la colonne d’identité, donc la première ligne est insérée avec la valeur 1. La ligne suivante insérée reçoit une valeur PromotionID de 2, et ainsi de suite.

Récupération d’une valeur d’identité

Pour retourner la dernière valeur IDENTITY attribuée dans les mêmes session et étendue, utilisez la fonction SCOPE_IDENTITY, comme ceci :

SELECT SCOPE_IDENTITY();

La fonction SCOPE_IDENTITY retourne la valeur d’identité la plus récente générée dans l’étendue actuelle pour une table. Si vous avez besoin de la dernière valeur d’identité dans une table spécifique, vous pouvez utiliser la fonction IDENT_CURRENT, comme suit :

SELECT IDENT_CURRENT('Sales.Promotion');

Remplacement des valeurs d’identité

Si vous souhaitez remplacer la valeur générée automatiquement et affecter une valeur spécifique à la colonne IDENTITY, vous devez d’abord activer les insertions d’identité à l’aide de l’instruction SET IDENTITY INSERT nom_table ON. Lorsque cette option est activée, vous pouvez insérer une valeur explicite pour la colonne d’identité, comme pour n’importe quelle autre colonne. Lorsque vous avez terminé, vous pouvez utiliser l’instruction SET IDENTITY INSERT nom_table OFF pour reprendre l’utilisation de valeurs d’identité automatiques, en utilisant la dernière valeur que vous avez entrée explicitement comme valeur initiale.

SET IDENTITY_INSERT SalesLT.Promotion ON;

INSERT INTO SalesLT.Promotion (PromotionID, PromotionName, ProductModelID, Discount)
VALUES
(20, 'Another short sale',37, 0.3);

SET IDENTITY_INSERT SalesLT.Promotion OFF;

Comme vous l’avez appris, la propriété IDENTITY est utilisée pour générer une séquence de valeurs pour une colonne dans une table. Toutefois, la propriété IDENTITY ne convient pas pour coordonner des valeurs entre plusieurs tables dans une base de données. Supposons, par exemple, que votre organisation différencie les ventes directes et les ventes aux revendeurs, et qu’elle souhaite stocker des données pour ces ventes dans des tables distinctes. Les deux types de vente peuvent avoir besoin d’un numéro de facture unique, et vous pouvez souhaiter éviter de dupliquer la même valeur pour deux types de vente différents. Une solution pour cette exigence est de maintenir un pool de valeurs séquentielles uniques entre les deux tables.

Réamorçage d’une colonne d’identité

Parfois, vous devrez réinitialiser ou ignorer les valeurs d’identité de la colonne. Pour ce faire, vous allez « réamorcer » la colonne à l’aide de la fonction DBCC CHECKIDENT. Vous pouvez l’utiliser pour ignorer de nombreuses valeurs ou pour réinitialiser la valeur d’identité suivante sur 1 après avoir supprimé toutes les lignes de la table. Pour obtenir tous les détails sur l’utilisation de DBCC CHECKIDENT, consultez la documentation de référence de Transact-SQL.

SEQUENCE

Dans Transact-SQL, vous pouvez utiliser un objet de séquence pour définir de nouvelles valeurs séquentielles indépendamment d’une table spécifique. Un objet de séquence est créé à l’aide de l’instruction CREATE SEQUENCE, en fournissant éventuellement le type de données (doit être un type entier, décimal ou numérique avec une échelle de 0), la valeur de départ, une valeur d’incrément, une valeur maximale et d’autres options liées aux performances.

CREATE SEQUENCE Sales.InvoiceNumber AS INT
START WITH 1000 INCREMENT BY 1;

Pour récupérer la valeur suivante disponible dans une séquence, utilisez la construction NEXT VALUE FOR, comme suit :

INSERT INTO Sales.ResellerInvoice
VALUES
(NEXT VALUE FOR Sales.InvoiceNumber, 2, GETDATE(), 'PO12345', 107.99);

IDENTITY ou SEQUENCE

Lorsque vous choisissez entre des colonnes IDENTITY ou un objet SEQUENCE pour remplir les valeurs automatiquement, gardez les points suivants à l’esprit :

  • Utilisez SEQUENCE si votre application requiert le partage d’une série de nombres unique entre plusieurs tables ou plusieurs colonnes dans une table.

  • SEQUENCE vous permet de trier les valeurs par une autre colonne. La construction NEXT VALUE FOR peut utiliser la clause OVER pour spécifier la colonne de tri. La clause OVER garantit que les valeurs retournées sont générées dans l'ordre de la clause ORDER BY de la clause OVER. Cette fonctionnalité vous permet également de générer des numéros de ligne pour les lignes à mesure qu’elles sont retournées dans une sélection. Dans l’exemple suivant, la table Production.Product est triée par la colonne Name, et la première colonne retournée est un nombre séquentiel.

    SELECT NEXT VALUE FOR dbo.Sequence OVER (ORDER BY Name) AS NextID,
    ProductID,
    Name
FROM Production.Product;

    Même si l’instruction précédente sélectionne simplement des valeurs SEQUENCE à afficher, les valeurs sont toujours consommées et les valeurs SEQUENCE affichées ne sont plus disponibles. Si vous exécutez l’instruction SELECT ci-dessus plusieurs fois, vous obtiendrez des valeurs SEQUENCE différentes à chaque fois.

  • Utilisez SEQUENCE si votre application nécessite que plusieurs nombres soient affectés en même temps. Par exemple, une application doit réserver cinq numéros séquentiels. La demande de valeurs d'identité peut provoquer des intervalles dans la série si des nombres ont été émis pour d'autres processus simultanément. Vous pouvez utiliser la procédure système sp_sequence_get_range pour récupérer simultanément plusieurs nombres de la séquence.

  • SEQUENCE vous permet de modifier la spécification de la séquence, telle que la valeur de l’incrément.

  • Les valeurs IDENTITY sont protégées contre les mises à jour. Si vous essayez de mettre à jour une colonne avec la propriété IDENTITY, vous obtenez une erreur.