Utilizar o IDENTITY para criar chaves de substituição com o conjunto de SQL dedicado no Azure Synapse Analytics

Neste artigo, encontrará recomendações e exemplos para utilizar a propriedade IDENTITY para criar chaves de substituição em tabelas no conjunto de SQL dedicado.

O que é uma chave de substituição

Uma chave de substituição numa 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 de substituição nas respetivas tabelas quando criam modelos de armazém de dados. Pode utilizar a propriedade IDENTITY para alcançar este objetivo de forma simples e eficaz sem afetar o desempenho da carga.

Criar uma tabela com uma coluna IDENTITY

A propriedade IDENTITY foi concebida para aumentar horizontalmente em todas as distribuições no conjunto de SQL dedicado sem afetar o desempenho da carga. Por conseguinte, a implementação da IDENTIDADE está orientada para a consecução destes objetivos.

Pode definir uma tabela como tendo a propriedade IDENTITY quando cria a tabela pela primeira vez com 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, pode utilizar INSERT..SELECT para preencher a tabela.

Este resto desta secção realça as nuances da implementação para ajudá-lo a compreendê-las mais detalhadamente.

Alocação de valores

A propriedade IDENTITY não garante a ordem pela qual os valores de substituição são alocados devido à arquitetura distribuída do armazém de dados. A propriedade IDENTITY foi concebida para aumentar horizontalmente em todas as distribuições no conjunto de SQL dedicado sem afetar o desempenho da carga.

O exemplo seguinte é 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 desembarcadas na distribuição 1. A primeira linha tem o valor de substituição de 1 na coluna C1e a segunda linha tem o valor de substituição de 61. Ambos os valores foram gerados pela propriedade IDENTITY. No entanto, a alocação dos valores não é contígua. Este comportamento é propositado.

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 numa única distribuição, a tabela tem acesso a apenas um sessenta dos valores do tipo de dados. Por este motivo, a propriedade IDENTIDADE está limitada a INT e BIGINT apenas aos tipos de dados.

SELECT.. INTO

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

• A instrução SELECT contém uma associação.
• São associadas múltiplas instruções SELECT com UNION.
• A coluna IDENTITY está listada mais do que uma vez na lista SELECT.
• A coluna IDENTITY faz parte de uma expressão.

Se uma destas condições for verdadeira, a coluna será criada COMO NÃO NULL em vez de herdar a propriedade IDENTITY.

CREATE TABLE AS SELECT

CREATE TABLE AS SELECT (CTAS) segue o mesmo comportamento de SQL Server documentado para SELECT.. INTO. No entanto, não pode especificar uma propriedade IDENTITY na definição de coluna da CREATE TABLE parte da instrução. Também não pode utilizar a função IDENTIDADE na SELECT parte do CTAS. Para preencher uma tabela, tem de utilizar CREATE TABLE para definir a tabela seguida por INSERT..SELECT para a preencher.

Inserir explicitamente valores numa coluna IDENTITY

O conjunto de SQL dedicado suporta SET IDENTITY_INSERT <your table> ON|OFF sintaxe. Pode utilizar esta sintaxe para inserir explicitamente valores na coluna IDENTITY.

Muitos modeladores de dados gostam de utilizar valores negativos predefinidos para determinadas linhas nas respetivas dimensões. Um exemplo é a linha -1 ou "membro desconhecido".

O script seguinte mostra como adicionar explicitamente esta linha através de 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
;

A carregar os dados

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

Para carregar dados para uma tabela e gerar uma chave de substituição com o IDENTITY, crie a tabela e, em seguida, utilize INSERT.. SELECIONE ou INSERT.. VALORES para efetuar a carga.

O exemplo seguinte realça 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');

Para obter mais informações sobre como carregar dados, veja Compilação de Extração, Carregamento e Transformação (ELT) para o conjunto de SQL dedicado e As melhores práticas de carregamento.

Vistas do sistema

Pode utilizar a vista de catálogo sys.identity_columns para identificar uma coluna que tenha a propriedade IDENTIDADE.

Para o ajudar a compreender melhor o esquema da base de dados, este exemplo mostra como integrar sys.identity_column" com outras vistas de catálogo de 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

Não é possível utilizar a propriedade IDENTITY:

• 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 conjunto de SQL dedicado:

• IDENTIDADE()
• @@IDENTITY
• SCOPE_IDENTITY
• IDENT_CURRENT
• IDENT_INCR
• IDENT_SEED

Tarefas comuns

Esta secção fornece algum código de exemplo que pode utilizar para realizar tarefas comuns quando trabalha com colunas IDENTITY.

A coluna C1 é a IDENTIDADE em todas as seguintes tarefas.

Localizar o valor alocado mais elevado para uma tabela

Utilize a MAX() função para determinar o valor mais alto atribuído a uma tabela distribuída:

SELECT MAX(C1)
FROM dbo.T1

Localizar a semente e o incremento da propriedade IDENTITY

Pode utilizar as vistas de catálogo para detetar os valores de configuração do incremento de identidade e das sementes de uma tabela com 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'
;

Passos seguintes

• Descrição geral da tabela
• CREATE TABLE (Transact-SQL) IDENTITY (Property)
• DBCC CHECKIDENT