Les colonnes IDENTITY dans Fabric Data Warehouse

S’applique à :✅ Entrepôt dans Microsoft Fabric

Dans Fabric Data Warehouse, IDENTITY les colonnes génèrent automatiquement de nouvelles valeurs numériques lorsque vous insérez de nouvelles lignes dans un tableau.

Les clés de substitution sont des identificateurs utilisés dans l’entreposage de données pour distinguer de manière unique les lignes, indépendamment de leurs clés naturelles. Cet article explique comment créer et gérer des clés de substitution en utilisant IDENTITY, y compris l’insertion de valeurs explicites et le re-seeding.

Pourquoi utiliser une colonne IDENTITY ?

IDENTITY Les colonnes éliminent l’attribution manuelle des clés, réduisant ainsi le risque d’erreurs et simplifiant l’ingestion des données. Les valeurs uniques gérées par le système sont idéales en tant que clés de substitution et clés primaires. Comparées aux approches manuelles, IDENTITY les colonnes offrent de meilleures performances car des clés uniques sont générées automatiquement sans logique de requête supplémentaire.

Le type de données bigint , requis pour les IDENTITY colonnes, peut stocker jusqu’à 9 223 372 036 854 775 807 valeurs entières positives. Cette plage garantit que chaque ligne reçoit une valeur unique dans sa IDENTITY colonne tout au long de la durée de vie du tableau.

Pour obtenir un plan de migration des données avec des clés de substitution à partir d’autres plateformes de base de données, consultez Migrer des colonnes IDENTITY vers Fabric Data Warehouse.

Syntaxe

Pour définir une IDENTITY colonne dans Fabric Data Warehouse, utilisez la IDENTITY propriété dans la définition de la colonne :

CREATE TABLE { warehouse_name.schema_name.table_name | schema_name.table_name | table_name } (
    [ column_name ] BIGINT IDENTITY ,
    [ ,...n ]
    -- Other columns here
);

La colonne identité n’a pas besoin d’être la première colonne dans la définition du tableau.

Fonctionnement des colonnes IDENTITY

Dans Fabric Data Warehouse, vous ne pouvez pas spécifier une valeur de départ personnalisée ou un incrément. Le système gère les valeurs en interne pour garantir l’unicité. IDENTITY les colonnes produisent toujours des valeurs entières positives. Chaque nouvelle ligne reçoit une nouvelle valeur et l’unicité est garantie tant que la table existe. Une fois qu’une valeur est utilisée, IDENTITY il n’utilise plus cette même valeur. Des espaces peuvent apparaître dans les valeurs produites par la IDENTITY colonne.

Allocation de valeurs

En raison de l’architecture distribuée du moteur d’entrepôt, la IDENTITY propriété ne garantit pas l’ordre dans lequel les valeurs de substitution sont attribuées. Cette propriété peut être mise à l’échelle horizontalement sur plusieurs nœuds de calcul afin de maximiser le parallélisme sans affecter les performances de chargement. En conséquence, les plages de valeurs provenant de différentes tâches d’ingestion peuvent ne pas être séquentielles.

L'exemple suivant illustre ce comportement :

-- Create a table with an IDENTITY column
CREATE TABLE dbo.Table1(
    Column1 BIGINT IDENTITY,
    Column2 VARCHAR(30) NULL
)

-- Ingestion task A
INSERT INTO dbo.Table1
VALUES (NULL), (NULL), (NULL), (NULL);

-- Ingestion task B
INSERT INTO dbo.Table1
VALUES (NULL), (NULL), (NULL), (NULL);

-- Review the data
SELECT * FROM dbo.Table1;

Exemple de résultat :

Dans cet exemple, Ingestion task A et Ingestion task B s’exécutent séquentiellement comme des tâches indépendantes. Bien que les tâches s’exécutent consécutivement, les quatre première et dernière lignes ont des plages de clés d’identité différentes dans dbo.Table1.Column1. Des intervalles entre les plages assignées à la tâche A et la tâche B peuvent également apparaître.

IDENTITY dans Fabric Data Warehouse garantit que toutes les valeurs d'une colonne IDENTITY sont uniques tant que IDENTITY_INSERT n'est pas utilisé, mais des lacunes peuvent apparaître dans les plages produites lors d'une tâche d'ingestion.

Objets métadonnées système

Les objets de métadonnées système suivants sont disponibles et utiles lors de la conception et du travail avec les valeurs d’identité dans Fabric Data Warehouse.

Listez les colonnes d’identité avec la vue système sys.identity_columns

Utilisez la vue catalogue sys.identity_columns pour lister toutes les colonnes d’identité dans un entrepôt. L’exemple suivant liste toutes les tables contenant une IDENTITY colonne, y compris les noms de schéma, de table et de colonnes d’identité :

SELECT
    s.name AS SchemaName,
    t.name AS TableName,
    c.name AS IdentityColumnName
FROM
    sys.identity_columns AS ic
INNER JOIN
    sys.columns AS c ON ic.[object_id] = c.[object_id]
    AND ic.column_id = c.column_id
INNER JOIN
    sys.tables AS t ON ic.[object_id] = t.[object_id]
INNER JOIN
    sys.schemas AS s ON t.[schema_id] = s.[schema_id]
ORDER BY
    s.name, t.name;

Dans Fabric Data Warehouse, les colonnes seed_value et increment_value de sys.identity_columns renvoient NULL et ne sont pas mises à jour une fois la colonne d’identité créée. La colonne last_value renvoie NULL par défaut, mais bascule définitivement vers -1 après la première opération d’insertion d’identité sur la table.

Insérer des valeurs avec IDENTITY_INSERT

Par défaut, vous ne pouvez pas insérer de valeurs dans une IDENTITY colonne. Cependant, il se peut que vous deviez insérer des valeurs spécifiques lors de la migration des données, de la reprise après sinistre, ou lorsque vous remplissez des valeurs sentinelles, comme -1 pour « Inconnu » dans les tables de dimensions.

Utilisez SET IDENTITY_INSERT pour autoriser temporairement des insertions explicites dans une colonne d’identité :

SET IDENTITY_INSERT dbo.DimCustomer ON;

INSERT INTO dbo.DimCustomer (CustomerKey, CustomerName, Email)
VALUES (-1, 'John Doe', 'john@contoso.com');

SET IDENTITY_INSERT dbo.DimCustomer OFF;

Quand IDENTITY_INSERT est ON:

Une liste de colonnes est requise avec la INSERT déclaration.
Une seule table par session peut avoir IDENTITY_INSERT défini sur ON à la fois.

Important

Après avoir désactivé IDENTITY_INSERT, réinitialisez les valeurs d’identité avec DBCC CHECKIDENT.

Réinitialiser les valeurs d’identité avec DBCC CHECKIDENT

Après avoir inséré des valeurs explicites avec IDENTITY_INSERT, utilisez DBCC CHECKIDENT pour réinitialiser la valeur de départ de la colonne d’identité. L’opération RESEED analyse toutes les plages d’identité utilisées et réservées entre les nœuds de calcul distribués afin de déterminer les valeurs suivantes correctes, garantissant l’unicité et évitant les collisions de clés.

DBCC CHECKIDENT('dbo.DimProduct', RESEED);

Dans Fabric Data Warehouse, DBCC CHECKIDENT prend uniquement en charge l’option RESEED. L’entrepôt de données détermine automatiquement les plages correctes pour les valeurs suivantes, et vous ne pouvez pas spécifier de valeur de réinitialisation personnalisée. Pour plus d’informations, consultez DBCC CHECKIDENT.

Limites

Pour plus d’informations, voir les colonnes IDENTITY, IDENTITY (Transact-SQL), et Créer des tables dans l’Entrepôt dans Microsoft Fabric.

Seul le type de données bigint est pris en charge pour les colonnes IDENTITY dans Fabric Data Warehouse. D’autres types de données entraînent une erreur.
La définition d’une graine et d’un incrément n’est pas prise en charge. Le système gère les valeurs en interne.
L’ajout d’une colonne IDENTITY à une table existante à l’aide de ALTER TABLE n’est pas pris en charge. Envisagez d’utiliser CREATE TABLE AS SELECT (CTAS) ou SELECT... INTO pour créer une copie d’un tableau existant et ajouter une IDENTITY colonne.
Des limitations s’appliquent à la manière IDENTITY dont les colonnes sont conservées lorsque vous créez une table en sélectionnant dans une autre table avec CTAS ou SELECT...INTO. Pour plus d’informations, voir la section Types de données de la clause SELECT - INTO (Transact-SQL).
DBCC CHECKIDENT Prend en charge uniquement cette RESEED option. La spécification d’une valeur de réinitialisation personnalisée ou l’utilisation de NORESEED ne sont pas prises en charge.
IDENTITY Les colonnes produisent des valeurs garanties uniques, mais ces valeurs ne sont pas nécessairement séquentielles ou ordonnées, et des lacunes peuvent apparaître.

Examples

R. Créer une table avec une colonne IDENTITY

CREATE TABLE Employees (
    EmployeeID BIGINT IDENTITY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

Cette instruction crée une Employees table dans laquelle chaque nouvelle ligne reçoit automatiquement un EmployeeID unique sous la forme d’une valeur de type bigint.

B. Insérer des lignes dans un tableau avec une colonne d’identité

Lorsque vous fournissez des valeurs pour chaque colonne non-identité dans leur ordre défini, vous n’avez pas besoin de spécifier une liste de colonnes :

INSERT INTO Employees VALUES ('Quarantino', 'Esposito');

Vous pouvez également fournir une liste de colonnes qui omet la colonne d’identité :

INSERT INTO Employees (FirstName, LastName)
VALUES ('Ensi', 'Vasala');

Chapitre C. Insérer des valeurs explicites avec IDENTITY_INSERT

SET IDENTITY_INSERT dbo.Employees ON;

INSERT INTO dbo.Employees (EmployeeID, FirstName, LastName)
VALUES (100, 'Sentinel', 'Row');

SET IDENTITY_INSERT dbo.Employees OFF;

D. Insérer des valeurs explicites avec COPY INTO

L’instruction COPY INTO prend en charge l’option IDENTITY_INSERT pour ingérer des valeurs explicites dans la commande. COPY INTO Les options supplantent tout paramètre de niveau session pour IDENTITY_INSERT.

COPY INTO dbo.Employees (EmployeeID 1, FirstName 2, LastName 3)
FROM 'https://myaccount.blob.core.windows.net/myblobcontainer/folder1/'
WITH (
    FILE_TYPE = 'CSV',
    IDENTITY_INSERT = 'ON'
);

E. Réinitialiser une table après des insertions explicites

DBCC CHECKIDENT('dbo.Employees', RESEED);

F. Créer une table avec CREATE TABLE AS SELECT

Utilisez CTAS pour créer une copie d’une table et conserver la propriété IDENTITY dans la table cible :

CREATE TABLE RetiredEmployees
AS SELECT * FROM Employees;

La colonne de la table cible hérite de la IDENTITY propriété de la table source. Pour les limitations, voir la section Types de données de la clause SELECT - INTO.

G. Créez une table avec SELECT...INTO

Utilisez SELECT...INTO pour créer une copie d’une table et persévérer la IDENTITY propriété dans la table cible :

SELECT *
INTO dbo.RetiredEmployees
FROM dbo.Employees
WHERE LastName = 'Esposito';