Używanie tożsamości do tworzenia kluczy zastępczych przy użyciu dedykowanej puli SQL w usłudze Azure Synapse Analytics
W tym artykule znajdziesz zalecenia i przykłady użycia właściwości IDENTITY do tworzenia kluczy zastępczych w tabelach w dedykowanej puli SQL.
Co to jest klucz zastępczy
Klucz zastępczy w tabeli to kolumna z unikatowym identyfikatorem dla każdego wiersza. Klucz nie jest generowany na podstawie danych tabeli. Modelujący dane lubią tworzyć klucze zastępcze w swoich tabelach podczas projektowania modeli magazynu danych. Możesz użyć właściwości IDENTITY, aby osiągnąć ten cel po prostu i skutecznie bez wpływu na wydajność obciążenia.
Uwaga
W usłudze Azure Synapse Analytics:
- Wartość IDENTITY zwiększa się samodzielnie w każdej dystrybucji i nie nakłada się na wartości IDENTITY w innych dystrybucjach. Wartość IDENTITY w usłudze Synapse nie jest gwarantowana jako unikatowa, jeśli użytkownik jawnie wstawia zduplikowaną wartość z wartością "SET IDENTITY_INSERT ON" lub reseeds IDENTITY. Aby uzyskać szczegółowe informacje, zobacz CREATE TABLE (Transact-SQL) IDENTITY (Właściwość).
- Aktualizacja kolumny dystrybucji nie gwarantuje unikatowości wartości IDENTITY. Użyj polecenia DBCC CHECKIDENT (Transact-SQL) po aktualizacji w kolumnie dystrybucji, aby zweryfikować unikatowość.
Tworzenie tabeli z kolumną IDENTITY
Właściwość IDENTITY została zaprojektowana do skalowania w poziomie we wszystkich dystrybucjach w dedykowanej puli SQL bez wpływu na wydajność obciążenia. W związku z tym implementacja tożsamości jest ukierunkowana na osiągnięcie tych celów.
Tabelę można zdefiniować jako właściwość IDENTITY podczas pierwszego utworzenia tabeli przy użyciu składni podobnej do następującej instrukcji:
CREATE TABLE dbo.T1
( C1 INT IDENTITY(1,1) NOT NULL
, C2 INT NULL
)
WITH
( DISTRIBUTION = HASH(C2)
, CLUSTERED COLUMNSTORE INDEX
)
;
Następnie możesz użyć INSERT..SELECT polecenia , aby wypełnić tabelę.
W pozostałej części tej sekcji przedstawiono niuanse implementacji, które ułatwiają zrozumienie ich w pełni.
Alokacja wartości
Właściwość IDENTITY nie gwarantuje kolejności przydzielania wartości zastępczych ze względu na rozproszoną architekturę magazynu danych. Właściwość IDENTITY została zaprojektowana do skalowania w poziomie we wszystkich dystrybucjach w dedykowanej puli SQL bez wpływu na wydajność obciążenia.
Poniższy przykład przedstawia ilustrację:
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');
W poprzednim przykładzie dwa wiersze wylądowały w dystrybucji 1. Pierwszy wiersz ma wartość zastępczą 1 w kolumnie C1, a drugi wiersz ma wartość zastępczą 61. Obie te wartości zostały wygenerowane przez właściwość IDENTITY. Jednak alokacja wartości nie jest ciągła. To zachowanie jest celowe.
Niesymetryczne dane
Zakres wartości dla tego typu danych jest rozłożony równomiernie w dystrybucjach. Jeśli tabela rozproszona będzie zawierać dane niesymetryczne, zakres wartości dostępnych dla typu danych może zostać przedwcześnie wyczerpany. Jeśli na przykład wszystkie dane kończą się w jednej dystrybucji, w rzeczywistości tabela ma dostęp tylko do jednej sześćdziesiątej wartości typu danych. Z tego powodu właściwość IDENTITY jest ograniczona tylko do INT typów danych i.BIGINT
WYBIERZ.. DO
Po wybraniu istniejącej kolumny IDENTITY do nowej tabeli nowa kolumna dziedziczy właściwość IDENTITY, chyba że spełniony jest jeden z następujących warunków:
- Instrukcja SELECT zawiera sprzężenie.
- Wiele instrukcji SELECT jest połączonych przy użyciu instrukcji UNION.
- Kolumna IDENTITY jest wymieniona więcej niż raz na liście SELECT.
- Kolumna IDENTITY jest częścią wyrażenia.
Jeśli którykolwiek z tych warunków ma wartość true, kolumna jest tworzona nie ma wartości NULL zamiast dziedziczyć właściwość IDENTITY.
CREATE TABLE AS SELECT
FUNKCJA CREATE TABLE AS SELECT (CTAS) jest zgodna z tym samym zachowaniem SQL Server, które zostało udokumentowane dla funkcji SELECT. DO. Nie można jednak określić właściwości IDENTITY w definicji CREATE TABLE kolumny części instrukcji. Nie można również użyć funkcji IDENTITY w SELECT części CTAS. Aby wypełnić tabelę, należy użyć CREATE TABLE polecenia , aby zdefiniować tabelę, po której następuje INSERT..SELECT wypełnienie.
Jawne wstawianie wartości do kolumny IDENTITY
Dedykowana pula SQL obsługuje SET IDENTITY_INSERT <your table> ON|OFF składnię. Przy użyciu tej składni można jawnie wstawić wartości do kolumny IDENTITY.
Wielu modelerów danych, takich jak używanie wstępnie zdefiniowanych wartości ujemnych dla niektórych wierszy w ich wymiarach. Przykładem jest wiersz -1 lub "nieznany element członkowski".
Następny skrypt pokazuje, jak jawnie dodać ten wiersz przy użyciu zestawu 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
;
Ładowanie danych
Obecność właściwości IDENTITY ma pewne konsekwencje dla kodu ładowania danych. W tej sekcji przedstawiono podstawowe wzorce ładowania danych do tabel przy użyciu tożsamości.
Aby załadować dane do tabeli i wygenerować klucz zastępczy przy użyciu funkcji IDENTITY, utwórz tabelę, a następnie użyj metody INSERT.. WYBIERZ lub WSTAW.. WARTOŚCI do wykonania obciążenia.
W poniższym przykładzie wyróżniono podstawowy wzorzec:
--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');
Uwaga
Obecnie nie można używać CREATE TABLE AS SELECT danych podczas ładowania danych do tabeli z kolumną IDENTITY.
Aby uzyskać więcej informacji na temat ładowania danych, zobacz Projektowanie wyodrębniania, ładowania i przekształcania (ELT) dla dedykowanej puli SQL i Najlepsze rozwiązania dotyczące ładowania.
Widoki systemu
Aby zidentyfikować kolumnę zawierającą właściwość IDENTITY, możesz użyć widoku wykazu sys.identity_columns .
Aby lepiej zrozumieć schemat bazy danych, w tym przykładzie pokazano, jak zintegrować sys.identity_column z innymi widokami wykazu systemu:
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'
;
Ograniczenia
Właściwości IDENTITY nie można używać w następujących przypadkach:
- Gdy typ danych kolumny nie jest INT ani BIGINT
- Gdy kolumna jest również kluczem dystrybucji
- Gdy tabela jest tabelą zewnętrzną
Następujące powiązane funkcje nie są obsługiwane w dedykowanej puli SQL:
Typowe zadania
Ta sekcja zawiera przykładowy kod, którego można użyć do wykonywania typowych zadań podczas pracy z kolumnami IDENTITY.
Kolumna C1 jest tożsamością we wszystkich następujących zadaniach.
Znajdowanie najwyższej przydzielonej wartości dla tabeli
MAX() Użyj funkcji, aby określić najwyższą wartość przydzieloną dla tabeli rozproszonej:
SELECT MAX(C1)
FROM dbo.T1
Znajdź inicjację i przyrost dla właściwości IDENTITY
Za pomocą widoków wykazu można odnajdywać wartości konfiguracji tożsamości przyrostowej i inicjujących dla tabeli przy użyciu następującego zapytania:
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'
;