Partilhar via

Use IDENTITY para criar chaves substitutas no pool SQL dedicado

  • Artigo

Neste artigo, você encontrará recomendações e exemplos para usar a propriedade para criar chaves substitutas IDENTITY em tabelas no pool 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 a partir dos dados da tabela. Os modeladores de dados gostam de criar chaves substitutas em suas tabelas quando projetam modelos de data warehouse. Você pode usar a propriedade para atingir esse objetivo de forma simples e eficaz, sem afetar o desempenho da IDENTITY carga.

Nota

No Azure Synapse Analytics:

  • O valor IDENTITY aumenta por si só em cada distribuição e não se sobrepõe aos valores IDENTITY noutras distribuições. Não é garantido que o valor IDENTITY no Synapse seja exclusivo se o usuário inserir explicitamente um valor duplicado com SET IDENTITY_INSERT ON IDENTITY ou repropagar. Para obter detalhes, consulte CREATE TABLE (Transact-SQL) IDENTITY (Property).
  • 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 IDENTITY propriedade foi projetada para ser dimensionada em todas as distribuições no pool SQL dedicado sem afetar o desempenho da carga. Portanto, a implementação do IDENTITY é orientada para o alcance desses objetivos.

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

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

Em seguida, você pode usar INSERT..SELECT para preencher a tabela.

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

Atribuição de valores

A IDENTITY propriedade não garante a ordem em que os valores substitutos são alocados devido à arquitetura distribuída do data warehouse. A IDENTITY propriedade foi projetada para ser dimensionada em todas as distribuições no pool SQL dedicado sem afetar o desempenho da 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 aterrissaram na distribuição 1. A primeira linha tem o valor substituto de 1 na coluna C1e a segunda linha tem o valor substituto de 61. Ambos os valores foram gerados pelo IDENTITY imóvel. No entanto, a alocação dos valores não é contígua. Este comportamento é a predefinição.

Dados distorcidos

O intervalo de valores para o tipo de dados é distribuído uniformemente pelas distribuições. Se uma tabela distribuída tiver dados distorcidos, o intervalo de valores disponível para o tipo de dados poderá ser esgotado prematuramente. Por exemplo, se todos os dados acabarem em uma única distribuição, então efetivamente a tabela terá acesso a apenas um sexagésimo dos valores do tipo de dados. Por esse motivo, a IDENTITY propriedade é limitada apenas aos BIGINTINT tipos de dados.

SELECIONE.. EM

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

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

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

CREATE TABLE AS SELECT

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

Inserir valores explícitos em uma coluna IDENTITY

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

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

O próximo script mostra como adicionar explicitamente essa linha 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 tem algumas implicações para o IDENTITY 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 execute a carga.

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

Nota

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

Para obter mais informações sobre como carregar dados, consulte Projetando extração, carga e transformação (ELT) para pool SQL dedicado e Carregando práticas recomendadas.

Vistas de sistema

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

Para ajudá-lo a entender melhor o esquema do banco de dados, este exemplo mostra como integrar sys.identity_columns com outras exibições do 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 IDENTITY propriedade não pode ser usada:

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

As seguintes funções relacionadas não são suportadas no pool SQL dedicado:

Tarefas comuns

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

A coluna C1 é a IDENTITY presente em todas as tarefas seguintes.

Encontrar o valor alocado mais alto para uma tabela

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

SELECT MAX(C1)
FROM dbo.T1

Encontre a semente e o incremento para a propriedade IDENTITY

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

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údos relacionados