Use IDENTITY para criar chaves substitutas no pool SQL dedicado

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 IDENTITY para atingir esse objetivo de forma simples e eficaz, sem afetar o desempenho de carga.

Create a table with an IDENTITY column

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

In the preceding example, two rows landed in distribution 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 pela propriedade IDENTITY. No entanto, a alocação dos valores não é contígua. Este comportamento é intencional.

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 INTBIGINT tipos de dados.

SELECT..INTO

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

• The SELECT statement contains a join.
• Multiple SELECT statements are joined by using 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. However, you can't specify an IDENTITY property in the column definition of the CREATE TABLE part of the statement. You also can't use the IDENTITY function in the SELECT part of the CTAS. Para preencher uma tabela, você precisa usar CREATE TABLE para definir a tabela seguida para INSERT..SELECT preenchê-la.

Inserir valores explícitos numa coluna IDENTIDADE

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. An example is the -1 or unknown member row.

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 IDENTITY tem algumas implicações para o 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');

Para obter mais informações sobre como carregar dados, consulte Extrair, Carregar e Transformar (ELT) para um pool SQL dedicado e Práticas recomendadas de carregamento.

System views

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

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:

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

Tarefas comuns

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

Column C1 is the IDENTITY in all the following tasks.

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

Find the seed and increment for the IDENTITY property

You can use the catalog views to discover the identity increment and seed configuration values for a table by using the following query:

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

• Projetar tabelas usando pool SQL dedicado
• CREATE TABLE (Transact-SQL) IDENTITY (Property)
• DBCC CHECKIDENT (Transact-SQL)