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.
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. O valor IDENTITY no Synapse não é garantido para ser exclusivo se o utilizador inserir explicitamente um valor duplicado com "DEFINIR IDENTITY_INSERT ATIVADO" ou reencatilizar IDENTIDADE. Para obter detalhes, veja CREATE TABLE (Transact-SQL) IDENTITY (Property).
- A atualização na coluna de distribuição não garante que o valor IDENTITY seja exclusivo. Utilize DBCC CHECKIDENT (Transact-SQL) após ATUALIZAR na coluna de distribuição para verificar a exclusividade.
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');
Nota
Não é possível utilizar CREATE TABLE AS SELECT atualmente ao carregar dados para uma tabela com uma coluna IDENTITY.
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:
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'
;