Tworzenie kluczy zastępczych w dedykowanej puli SQL przy użyciu tożsamości
W tym artykule znajdziesz zalecenia i przykłady użycia IDENTITY właściwości 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. Osoby modelające dane lubią tworzyć klucze zastępcze w tabelach podczas projektowania modeli magazynu danych. Możesz użyć IDENTITY właściwości , 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ę samoczynnie w każdej dystrybucji i nie nakłada się na wartości IDENTITY w innych dystrybucjach. Wartość IDENTITY w usłudze Synapse nie ma gwarancji, że jest unikatowa, jeśli użytkownik jawnie wstawia zduplikowaną wartość z tożsamością
SET IDENTITY_INSERT ONlub ponownie je zmienia. Aby uzyskać szczegółowe informacje, zobacz CREATE TABLE (Transact-SQL) IDENTITY (Właściwość). - AKTUALIZACJA w kolumnie dystrybucji nie gwarantuje, że wartość IDENTITY jest unikatowa. Użyj polecenia DBCC CHECKIDENT (Transact-SQL) po aktualizacji w kolumnie dystrybucji, aby zweryfikować unikatowość.
Tworzenie tabeli z kolumną IDENTITY
Właściwość została zaprojektowana do skalowania IDENTITY w poziomie we wszystkich dystrybucjach w dedykowanej puli SQL bez wpływu na wydajność obciążenia. W związku z IDENTITY tym wdrożenie programu jest ukierunkowane na osiągnięcie tych celów.
Tabelę można zdefiniować jako IDENTITY właściwość podczas pierwszego tworzenia 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 pomogą Ci zrozumieć je 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ść została zaprojektowana do skalowania IDENTITY w poziomie we wszystkich dystrybucjach w dedykowanej puli SQL bez wpływu na wydajność obciążenia.
Poniższy przykład to ilustracja:
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 IDENTITY właściwość . Jednak alokacja wartości nie jest ciągła. Wynika to z ustawienia fabrycznego.
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 zostaną zakończone w jednej dystrybucji, tabela będzie mieć dostęp tylko do jednej sześćdziesiątej wartości typu danych. Z tego powodu właściwość jest ograniczona IDENTITY tylko do INT typów danych i BIGINT .
WYBRAĆ.. DO
Po wybraniu istniejącej IDENTITY kolumny do nowej tabeli nowa kolumna dziedziczy IDENTITY właściwość, chyba że spełniony jest jeden z następujących warunków:
- Instrukcja
SELECTzawiera sprzężenia. - Wiele
SELECTinstrukcji jest dołączanych przy użyciu poleceniaUNION. - Kolumna
IDENTITYjest wyświetlana więcej niż raz naSELECTliście. - Kolumna
IDENTITYjest częścią wyrażenia.
Jeśli którykolwiek z tych warunków jest spełniony, kolumna zostanie utworzona NOT NULL zamiast dziedziczyć IDENTITY właściwość.
CREATE TABLE AS SELECT
CREATE TABLE AS SELECT (CTAS) jest zgodne z tym samym zachowaniem programu SQL Server, które zostało udokumentowane dla programu SELECT..INTO. Nie można jednak określić IDENTITY właściwości w definicji kolumny CREATE TABLE części instrukcji. Nie można również użyć IDENTITY funkcji w SELECT części CTAS. Aby wypełnić tabelę, należy użyć CREATE TABLE polecenia , aby zdefiniować tabelę, po której INSERT..SELECT następuje wypełnienie.
Wstawianie jawnych wartości do kolumny IDENTITY
Dedykowana pula SQL obsługuje SET IDENTITY_INSERT <your table> ON|OFF składnię. Ta składnia służy do jawnego wstawiania 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 polecenia 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;
Ładowanie danych
Obecność IDENTITY właściwości ma pewne konsekwencje dla kodu ładowania danych. W tej sekcji przedstawiono podstawowe wzorce ładowania danych do tabel przy użyciu polecenia IDENTITY.
Aby załadować dane do tabeli i wygenerować klucz zastępczy przy użyciu polecenia IDENTITY, utwórz tabelę, a następnie użyj polecenia INSERT..SELECT lub INSERT..VALUES wykonaj obciążenie.
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 systemowe
Widok wykazu sys.identity_columns umożliwia zidentyfikowanie kolumny zawierającej IDENTITY właściwość .
Aby lepiej zrozumieć schemat bazy danych, w tym przykładzie pokazano, jak zintegrować sys.identity_columns 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
IDENTITY Nie można użyć właściwości:
- Gdy typ danych kolumny nie
INTjest lubBIGINT - 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
Poniższy przykładowy kod umożliwia wykonywanie typowych zadań podczas pracy z kolumnami IDENTITY .
Kolumna C1 znajduje się IDENTITY we wszystkich poniższych 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
Znajdowanie inicjatora i przyrostu dla właściwości IDENTITY
Za pomocą widoków wykazu można odnaleźć wartości konfiguracji inkrementacji tożsamości i inicjacji 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'
;