Compartilhar via

Usar IDENTITY para criar chaves substitutas no pool de SQL dedicado

  • Artigo

Neste artigo, você encontrará recomendações e exemplos para usar a propriedade IDENTITY para criar chaves alternativas em tabelas no pool de SQL dedicado.

O que é uma chave substituta?

Uma chave substituta em uma tabela é uma coluna com um identificador exclusivo para cada linha. A chave não é gerada de dados da tabela. Os modeladores de dados gostam de criar chaves substitutas em suas tabelas quando criam modelos de data warehouse. Você pode usar a propriedade IDENTITY para atingir esse objetivo de forma simples e eficiente, sem afetar o desempenho de carga.

Observação

No Azure Synapse Analytics:

  • O valor de IDENTITY aumenta sozinho em cada distribuição e não se sobrepõe aos valores de IDENTITY em outras distribuições. Não será garantido que o valor de IDENTITY no Synapse seja exclusivo se o usuário inserir explicitamente um valor duplicado com SET IDENTITY_INSERT ON ou propagar novamente a IDENTITY. Para obter mais detalhes, veja IDENTITY (propriedade) com CREATE TABLE (Transact-SQL) .
  • UPDATE na coluna de distribuição não garante que o valor IDENTITY seja exclusivo. Use DBCC CHECKIDENT (Transact-SQL) após UPDATE na coluna de distribuição para verificar a exclusividade.

Criar uma tabela com uma coluna IDENTITY

A propriedade IDENTITY foi projetada para escalar horizontalmente em todas as distribuições no pool de SQL dedicado sem afetar o desempenho de carga. Portanto, a implementação de IDENTITY é orientada para atingir esses objetivos.

Você pode definir uma tabela como tendo a propriedade IDENTITY quando você cria a tabela pela primeira vez usando uma sintaxe semelhante à instrução a seguir:

CREATE TABLE dbo.T1
(    C1 INT IDENTITY(1,1) NOT NULL,
     C2 INT NULL
)
WITH
(   DISTRIBUTION = HASH(C2),
    CLUSTERED COLUMNSTORE INDEX
);

Você pode usar INSERT..SELECT para popular a tabela.

O restante desta seção destaca as nuances da implementação para ajudá-lo a entendê-los mais detalhadamente.

Alocação de valores

A propriedade IDENTITY não garante a ordem em que os valores substitutos são alocados devido à arquitetura distribuída do data warehouse. A propriedade IDENTITY foi projetada para escalar horizontalmente em todas as distribuições no pool de SQL dedicado sem afetar o desempenho de carga.

O exemplo a seguir é uma ilustração:

CREATE TABLE dbo.T1
(    C1 INT IDENTITY(1,1) NOT NULL,
     C2 VARCHAR(30) NULL
)
WITH
(   DISTRIBUTION = HASH(C2),
    CLUSTERED COLUMNSTORE INDEX
);

INSERT INTO dbo.T1
VALUES (NULL);

INSERT INTO dbo.T1
VALUES (NULL);

SELECT *
FROM dbo.T1;

DBCC PDW_SHOWSPACEUSED('dbo.T1');

No exemplo anterior, duas linhas foram descarregadas na distribuição 1. A primeira linha tem o valor substituto de 1 na coluna C1, e a segunda linha tem o valor substituto 61. Ambos os valores foram gerados pela propriedade IDENTITY. No entanto, a alocação dos valores não é contígua. Este comportamento ocorre por design.

Dados distorcidos

Os intervalos de valores para o tipo de dados são distribuídos igualmente pelas distribuições. Se uma tabela distribuída tiver dados distorcidos, então o intervalo de valores disponível para o tipo de dados pode ser esgotado prematuramente. Por exemplo, se todos os dados resultam em uma única distribuição, então efetivamente a tabela tem acesso a apenas um sexagésimo dos valores do tipo de dados. Por esse motivo, a propriedade IDENTITY é limitada somente aos tipos de dados INT e BIGINT.

SELECT..INTO

Quando uma coluna IDENTITY existente é seleciona para uma nova tabela, a nova coluna herda a propriedade IDENTITY, a menos que uma das seguintes condições seja verdadeira:

  • A instrução SELECT contém uma junção.
  • Várias instruções SELECT são unidas usando UNION.
  • A coluna IDENTITY é listada mais de uma vez na lista SELECT.
  • A coluna IDENTITY faz parte de uma expressão.

Se alguma dessas condições for verdadeira, a coluna será criada como NOT NULL em vez de herdar a propriedade IDENTITY.

CREATE TABLE AS SELECT

CREATE TABLE AS SELECT (CTAS) segue o mesmo comportamento do SQL Server documentado para SELECT..INTO. No entanto, você não pode especificar uma propriedade IDENTITY na definição de coluna da parte CREATE TABLE da instrução. Você também não pode usar a função IDENTITY na parte SELECT do CTAS. Para popular uma tabela, você precisa usar CREATE TABLE para definir a tabela, seguido de INSERT..SELECT para preenchê-la.

Inserir valores explícitos em uma coluna IDENTITY

O pool SQL dedicado dá suporte à sintaxe SET IDENTITY_INSERT <your table> ON|OFF. Você pode usar essa sintaxe para inserir explicitamente os valores na coluna IDENTITY.

Muitos modeladores de dados gostam de usar valores negativos predefinidos para determinadas linhas em suas dimensões. Um exemplo é de -1 ou a linha membro desconhecido.

O próximo script mostra como adicionar essa linha explicitamente usando SET IDENTITY_INSERT:

SET IDENTITY_INSERT dbo.T1 ON;

INSERT INTO dbo.T1
(   C1,
    C2
)
VALUES (-1,'UNKNOWN');

SET IDENTITY_INSERT dbo.T1 OFF;

SELECT     *
FROM    dbo.T1;

Carregar dados

A presença da propriedade IDENTITY tem algumas implicações no seu código de carregamento de dados. Esta seção destaca alguns padrões básicos para carregar dados em tabelas usando IDENTITY.

Para carregar dados em uma tabela e gerar uma chave substituta usando IDENTITY, crie a tabela e, em seguida, use INSERT..SELECT ou INSERT..VALUES para executar o carregamento.

O exemplo a seguir destaca o padrão básico:

--CREATE TABLE with IDENTITY
CREATE TABLE dbo.T1
(    C1 INT IDENTITY(1,1),
     C2 VARCHAR(30)
)
WITH
(   DISTRIBUTION = HASH(C2),
    CLUSTERED COLUMNSTORE INDEX
);

--Use INSERT..SELECT to populate the table from an external table
INSERT INTO dbo.T1
(C2)
SELECT     C2
FROM    ext.T1;

SELECT *
FROM   dbo.T1;

DBCC PDW_SHOWSPACEUSED('dbo.T1');

Observação

Não é possível usar CREATE TABLE AS SELECT atualmente ao carregar dados em uma tabela com uma coluna IDENTITY.

Para obter mais informações sobre o carregamento de dados, veja Criar o processo ELT (Extração, Carregamento e Transformação) para o pool de SQL dedicado e Práticas recomendadas de carregamento.

Exibições do sistema

Você pode usar a exibição de catálogo sys.identity_columns para identificar uma coluna que tem a propriedade IDENTITY.

Para ajudá-lo a entender melhor o esquema de banco de dados, este exemplo mostra como integrar sys.identity_columns com outra exibições de catálogo do sistema:

SELECT  sm.name
,       tb.name
,       co.name
,       CASE WHEN ic.column_id IS NOT NULL
             THEN 1
        ELSE 0
        END AS is_identity
FROM        sys.schemas AS sm
JOIN        sys.tables  AS tb           ON  sm.schema_id = tb.schema_id
JOIN        sys.columns AS co           ON  tb.object_id = co.object_id
LEFT JOIN   sys.identity_columns AS ic  ON  co.object_id = ic.object_id
                                        AND co.column_id = ic.column_id
WHERE   sm.name = 'dbo'
AND     tb.name = 'T1'
;

Limitações

A propriedade IDENTITY não pode ser usada:

  • Quando o tipo de dados de coluna não é INT ou BIGINT
  • Quando a coluna é também a chave de distribuição
  • Quando a tabela é uma tabela externa

As funções relacionadas a seguir não têm suporte no pool de SQL dedicado:

Tarefas comuns

Você pode usar o código de exemplo a seguir para executar tarefas comuns quando trabalha com colunas IDENTITY.

A coluna C1 é IDENTITY em todas as tarefas a seguir.

Localizar o maior valor alocado para uma tabela

Use a função MAX() para determinar o valor mais alto alocado para uma tabela distribuída:

SELECT MAX(C1)
FROM dbo.T1

Localizar a semente e o incremento para a propriedade IDENTITY

Você pode usar as exibições de catálogo para descobrir os valores de configuração da semente e do incremento da identidade para uma tabela usando a consulta a seguir:

SELECT  sm.name
,       tb.name
,       co.name
,       ic.seed_value
,       ic.increment_value
FROM        sys.schemas AS sm
JOIN        sys.tables  AS tb           ON  sm.schema_id = tb.schema_id
JOIN        sys.columns AS co           ON  tb.object_id = co.object_id
JOIN        sys.identity_columns AS ic  ON  co.object_id = ic.object_id
                                        AND co.column_id = ic.column_id
WHERE   sm.name = 'dbo'
AND     tb.name = 'T1'
;

Conteúdo relacionado