Colunas IDENTITY em Fabric Data Warehouse

Aplica-se a:✅Armazém de dados no Microsoft Fabric

Em Fabric Data Warehouse, IDENTITY colunas geram automaticamente novos valores numéricos quando você insere novas linhas em uma tabela.

Chaves substitutas são identificadores usados no data warehousing para distinguir exclusivamente linhas, independentemente de suas chaves naturais. Este artigo explica como criar e gerenciar chaves substitutas usando IDENTITY, incluindo inserir valores explícitos e reseeding.

Por que usar uma coluna do tipo IDENTITY?

IDENTITY Colunas eliminam a atribuição manual de chaves, reduzindo o risco de erros e simplificando a ingestão de dados. Os valores exclusivos gerenciados pelo sistema são ideais como chaves substitutas e chaves primárias. Comparadas às abordagens manuais, IDENTITY as colunas oferecem melhor desempenho porque chaves únicas são geradas automaticamente sem lógica adicional de consulta.

O tipo de dados bigint, necessário para as colunas IDENTITY, pode armazenar até 9,223,372,036,854,775,807 valores inteiros positivos. Esse intervalo garante que cada linha receba um valor único em sua IDENTITY coluna ao longo da vida útil da tabela.

Para obter um plano para migrar dados com chaves substitutas (surrogate keys) de outras plataformas de banco de dados, consulte Migrar colunas IDENTITY para o Fabric Data Warehouse.

Sintaxe

Para definir uma coluna IDENTITY no Fabric Data Warehouse, use a propriedade IDENTITY na definição da coluna:

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

A coluna identidade não precisa ser a primeira coluna na definição da tabela.

Como funcionam as colunas IDENTITY

Em Fabric Data Warehouse, você não pode especificar um valor inicial ou incremento personalizado. O sistema gerencia os valores internamente para garantir a singularidade. IDENTITY as colunas sempre produzem valores inteiros positivos. Cada nova linha recebe um novo valor e a exclusividade é garantida enquanto a tabela existir. Uma vez que um valor é usado, IDENTITY não usa mais esse mesmo valor. Podem aparecer lacunas nos valores que a IDENTITY coluna produz.

Alocação de valores

Devido à arquitetura distribuída do mecanismo do data warehouse, a propriedade IDENTITY não garante a ordem em que os valores substitutos são atribuídos. A propriedade é escalada horizontalmente entre os nós de computação para maximizar o paralelismo sem afetar o desempenho de carga. Como resultado, os intervalos de valor de diferentes tarefas de ingestão podem não ser sequenciais.

O seguinte exemplo a seguir ilustra esse comportamento:

-- 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;

Resultado de exemplo:

Neste exemplo, Ingestion task A e Ingestion task B executa sequencialmente como tarefas independentes. Embora as tarefas sejam executadas consecutivamente, a primeira e a última quatro linhas têm diferentes intervalos de chaves de identidade em dbo.Table1.Column1. Lacunas entre as faixas atribuídas à tarefa A e à tarefa B também podem ocorrer.

IDENTITY no Fabric Data Warehouse garante que todos os valores em uma coluna IDENTITY sejam únicos, desde que IDENTITY_INSERT não seja usado, mas podem ocorrer lacunas nas faixas geradas para uma tarefa de ingestão.

Objetos metadados do sistema

Os seguintes objetos de metadados do sistema estão disponíveis e são úteis ao projetar e trabalhar com valores de identidade em Fabric Data Warehouse.

Liste as colunas de identidade com a exibição do sistema sys.identity_columns

Use a visualização de catálogo sys.identity_columns para listar todas as colunas de identidade em um depósito. O exemplo a seguir lista todas as tabelas que contêm uma IDENTITY coluna, incluindo os nomes das colunas de esquema, tabela e identidade:

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;

No Fabric Data Warehouse, as colunas seed_value e increment_value de sys.identity_columns retornam NULL e não são atualizadas após a criação da coluna de identidade. A last_value coluna retorna NULL por padrão, mas muda permanentemente para -1 após a primeira operação de inserção de identidade na tabela.

Inserir valores com IDENTITY_INSERT

Por padrão, você não pode inserir valores em uma IDENTITY coluna. No entanto, pode ser necessário inserir valores específicos durante migração de dados, recuperação de desastres ou quando preencher valores sentinel, como -1 para "Desconhecido" nas tabelas de dimensões.

Use SET IDENTITY_INSERT para permitir temporariamente inserções explícitas em uma coluna identity:

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;

Quando IDENTITY_INSERT é ON:

Uma lista de colunas é necessária junto com a INSERT declaração.
Apenas uma tabela por sessão pode ter IDENTITY_INSERT definido como ON de cada vez.

Importante

Após desativar IDENTITY_INSERT, redefina os valores de identidade usando DBCC CHECKIDENT.

Redefinir valores de identidade com DBCC CHECKIDENT

Após inserir valores explícitos com IDENTITY_INSERT, use DBCC CHECKIDENT para redefinir a semente da coluna de identidade. A RESEED operação escaneia todas as faixas de identidade usadas e reservadas entre nós de computação distribuídos para determinar os próximos valores corretos, garantindo a unicidade e prevenindo colisões de chaves.

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

Em Fabric Data Warehouse, DBCC CHECKIDENT suporta apenas a RESEED opção. O data warehouse determina automaticamente os próximos intervalos de valores corretos, e você não pode especificar um valor personalizado de redefinição. Para obter mais informações, confira DBCC CHECKIDENT.

Limitações

Para mais informações, veja colunas IDENTITY, IDENTITY (Transact-SQL) e Criar tabelas no Warehouse em Microsoft Fabric.

Apenas o tipo de dados bigint é compatível com colunas IDENTITY no Fabric Data Warehouse. Outros tipos de dados resultam em um erro.
Definir uma semente e um incremento não tem suporte. O sistema gerencia os valores internamente.
Adicionar uma IDENTITY coluna a uma tabela existente com ALTER TABLE não é suportado. Considere usar CREATE TABLE AS SELECT (CTAS) ou SELECT... INTO para criar uma cópia de uma tabela existente e adicionar uma IDENTITY coluna.
Há limitações quanto à forma como IDENTITY colunas são preservadas quando você cria uma tabela a partir de outra tabela com CTAS ou SELECT...INTO. Para mais informações, veja a seção Tipos de Dados da cláusula SELECT - INTO (Transact-SQL).
DBCC CHECKIDENT Suporta apenas a RESEED opção. Especificar ou usar NORESEED um valor de reseed personalizado não é suportado.
IDENTITY Colunas produzem valores que são garantidamente únicos, mas os valores não são necessariamente sequenciais ou ordenados, e podem ocorrer lacunas.

Exemplos

A. Criar uma tabela com uma coluna IDENTITY

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

Essa instrução cria uma Employees tabela onde cada nova linha recebe automaticamente um valor único EmployeeID como bigint .

B. Inserir linhas em uma tabela com uma coluna de identidade

Quando você fornece valores para cada coluna não identidade em sua ordem definida, não precisa especificar uma lista de colunas:

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

Você também pode fornecer uma lista de colunas que omita a coluna identidade:

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

C. Insira valores explícitos com 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. Insira valores explícitos com COPY INTO

A COPY INTO instrução suporta a IDENTITY_INSERT opção de ingerir valores explícitos dentro do comando. COPY INTO As opções sobrepõem qualquer configuração de nível de sessão para 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. Resemear uma tabela após inserções explícitas

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

F. Crie uma tabela com CREATE TABLE AS SELECT

Use o CTAS para criar uma cópia de uma tabela e persistir a IDENTITY propriedade na tabela alvo:

CREATE TABLE RetiredEmployees
AS SELECT * FROM Employees;

A coluna na tabela de destino herda a IDENTITY propriedade da tabela de origem. Para limitações, veja a seção Tipos de Dados da cláusula SELECT - INTO.

G. Crie uma tabela com SELECT...INTO

Use SELECT...INTO para criar uma cópia de uma tabela e persistir a IDENTITY propriedade na tabela de destino:

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