Udostępnij za pośrednictwem

CREATE TABLE (Transact-SQL) IDENTITY (właściwość)

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsBaza danych SQL w usłudze Microsoft Fabric (wersja zapoznawcza)

Tworzy kolumnę tożsamości w tabeli. Ta właściwość jest używana z instrukcjami CREATE TABLE i ALTER TABLE Transact-SQL.

Note

Właściwość IDENTITY różni się od właściwości SQL-DMO Identity , która uwidacznia właściwość tożsamości wiersza kolumny.

Transact-SQL konwencje składni

Syntax

IDENTITY [ (seed , increment) ]

Ta składnia nie jest obsługiwana przez bezserwerową pulę SQL w usłudze Azure Synapse Analytics.

Arguments

seed

Wartość używana dla pierwszego wiersza załadowanego do tabeli.

increment

Wartość przyrostowa dodawana do wartości tożsamości poprzedniego wiersza, który został załadowany.

Note

W usłudze Azure Synapse Analytics wartości tożsamości nie są przyrostowe ze względu na architekturę rozproszoną magazynu danych. Aby uzyskać więcej informacji, zobacz Using IDENTITY to create surrogate keys in a Synapse SQL pool (Używanie tożsamości do tworzenia kluczy zastępczych w puli SQL usługi Synapse).

Należy określić zarówno nasion, jak i inkrementację, albo nie. Jeśli żadna z nich nie zostanie określona, wartość domyślna to (1,1).

Remarks

Kolumny tożsamości mogą służyć do generowania wartości kluczy. Właściwość tożsamości w kolumnie gwarantuje następujące warunki:

  • Każda nowa wartość jest generowana na podstawie bieżącego inicjatora i przyrostu.

  • Każda nowa wartość dla określonej transakcji różni się od innych współbieżnych transakcji w tabeli.

Właściwość tożsamości w kolumnie nie gwarantuje następujących warunków:

  • Unikatowość wartości — unikatowość musi być wymuszana przy użyciu PRIMARY KEY ograniczenia lub UNIQUE indeksu.UNIQUE

    Note

    Usługa Azure Synapse Analytics nie obsługuje ani UNIQUE nie ogranicza PRIMARY KEY ani UNIQUE indeksu. Aby uzyskać więcej informacji, zobacz Using IDENTITY to create surrogate keys in a Synapse SQL pool (Używanie tożsamości do tworzenia kluczy zastępczych w puli SQL usługi Synapse).

  • Kolejne wartości w ramach transakcji — transakcja wstawiająca wiele wierszy nie jest gwarantowana, aby uzyskać kolejne wartości dla wierszy, ponieważ inne współbieżne operacje wstawiania mogą wystąpić w tabeli. Jeśli wartości muszą być następujące po sobie, transakcja powinna używać blokady wyłącznej w tabeli lub użyć SERIALIZABLE poziomu izolacji.

  • Kolejne wartości po ponownym uruchomieniu serwera lub innych awariach — program SQL Server może buforować wartości tożsamości ze względu na wydajność, a niektóre z przypisanych wartości mogą zostać utracone podczas awarii bazy danych lub ponownego uruchomienia serwera. Może to spowodować przerwy w wartości tożsamości podczas wstawiania. Jeśli luki nie są akceptowalne, aplikacja powinna używać własnego mechanizmu do generowania wartości kluczy. Użycie generatora sekwencji z opcją NOCACHE może ograniczyć luki do transakcji, które nigdy nie są zatwierdzane.

  • Ponowne użycie wartości — dla danej właściwości tożsamości z określonym inicjatorem/przyrostem wartości tożsamości nie są ponownie używane przez aparat. Jeśli określona instrukcja insert nie powiedzie się lub instrukcja insert zostanie wycofana, używane wartości tożsamości zostaną utracone i nie zostaną wygenerowane ponownie. Może to spowodować przerwy w generowaniu kolejnych wartości tożsamości.

Te ograniczenia są częścią projektu w celu poprawy wydajności i dlatego, że są one akceptowalne w wielu typowych sytuacjach. Jeśli nie możesz używać wartości tożsamości z powodu tych ograniczeń, utwórz oddzielną tabelę zawierającą bieżącą wartość i zarządzaj dostępem do tabeli i przypisania liczb w aplikacji.

Jeśli tabela z kolumną tożsamości zostanie opublikowana na potrzeby replikacji, kolumna tożsamości musi być zarządzana w sposób odpowiedni dla używanego typu replikacji. Aby uzyskać więcej informacji, zobacz Replikowanie kolumn tożsamości.

Dla tabeli można utworzyć tylko jedną kolumnę tożsamości.

W tabelach zoptymalizowanych pod kątem pamięci inicjator i przyrost musi być ustawiony na 1, 1wartość . Ustawienie inicjacji lub przyrostu na wartość inną niż 1 powoduje następujący błąd: The use of seed and increment values other than 1 is not supported with memory optimized tables.

Po ustawieniu właściwości tożsamości w kolumnie nie można jej usunąć. Typ danych można zmienić tak długo, jak nowy typ danych jest zgodny z właściwością tożsamości.

Examples

A. Używanie właściwości IDENTITY z funkcją CREATE TABLE

Poniższy przykład tworzy nową tabelę przy użyciu IDENTITY właściwości automatycznie zwiększającej numer identyfikacyjny.

USE AdventureWorks2022;
GO
IF OBJECT_ID('dbo.new_employees', 'U') IS NOT NULL
    DROP TABLE new_employees;
GO

CREATE TABLE new_employees (
    id_num INT IDENTITY(1, 1),
    fname VARCHAR(20),
    minit CHAR(1),
    lname VARCHAR(30)
);

INSERT new_employees (fname, minit, lname)
VALUES ('Karin', 'F', 'Josephs');

INSERT new_employees (fname, minit, lname)
VALUES ('Pirkko', 'O', 'Koskitalo');

B. Używanie składni ogólnej do znajdowania luk w wartościach tożsamości

W poniższym przykładzie przedstawiono ogólną składnię umożliwiającą znalezienie luk w wartościach tożsamości po usunięciu danych.

Note

Pierwsza część poniższego skryptu Transact-SQL jest przeznaczona tylko dla ilustracji. Możesz uruchomić skrypt Transact-SQL rozpoczynający się od komentarza: -- Create the img table.

-- Here is the generic syntax for finding identity value gaps in data.
-- The illustrative example starts here.
SET IDENTITY_INSERT tablename ON;

DECLARE @minidentval column_type;
DECLARE @maxidentval column_type;
DECLARE @nextidentval column_type;

SELECT @minidentval = MIN($IDENTITY),
    @maxidentval = MAX($IDENTITY)
FROM tablename

IF @minidentval = IDENT_SEED('tablename')
    SELECT @nextidentval = MIN($IDENTITY) + IDENT_INCR('tablename')
    FROM tablename t1
    WHERE $IDENTITY BETWEEN IDENT_SEED('tablename')
            AND @maxidentval
        AND NOT EXISTS (
            SELECT *
            FROM tablename t2
            WHERE t2.$IDENTITY = t1.$IDENTITY + IDENT_INCR('tablename')
            )
ELSE
    SELECT @nextidentval = IDENT_SEED('tablename');

SET IDENTITY_INSERT tablename OFF;

-- Here is an example to find gaps in the actual data.
-- The table is called img and has two columns: the first column
-- called id_num, which is an increasing identification number, and the
-- second column called company_name.
-- This is the end of the illustration example.
-- Create the img table.
-- If the img table already exists, drop it.
-- Create the img table.
IF OBJECT_ID('dbo.img', 'U') IS NOT NULL
    DROP TABLE img;
GO

CREATE TABLE img (
    id_num INT IDENTITY(1, 1),
    company_name SYSNAME
);

INSERT img (company_name)
VALUES ('New Moon Books');

INSERT img (company_name)
VALUES ('Lucerne Publishing');

-- SET IDENTITY_INSERT ON and use in img table.
SET IDENTITY_INSERT img ON;

DECLARE @minidentval SMALLINT;
DECLARE @nextidentval SMALLINT;

SELECT @minidentval = MIN($IDENTITY)
FROM img

IF @minidentval = IDENT_SEED('img')
    SELECT @nextidentval = MIN($IDENTITY) + IDENT_INCR('img')
    FROM img t1
    WHERE $IDENTITY BETWEEN IDENT_SEED('img')
            AND 32766
        AND NOT EXISTS (
            SELECT *
            FROM img t2
            WHERE t2.$IDENTITY = t1.$IDENTITY + IDENT_INCR('img')
            )
ELSE
    SELECT @nextidentval = IDENT_SEED('img');

SET IDENTITY_INSERT img OFF;

Related content