CREATE TABLE (Transact-SQL) IDENTITY (Eigenschaft)

  • Artikel

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance Azure Synapse Analytics

Erstellt eine Identitätsspalte in einer Tabelle. Diese Eigenschaft wird in den Transact-SQL-Anweisungen CREATE TABLE und ALTER TABLE verwendet.

Hinweis

Die IDENTITY-Eigenschaft unterscheidet sich von der SQL-DMO-Eigenschaft Identity, die die IDENTITY-Eigenschaft für Zeilen einer Spalte verfügbar macht.

Transact-SQL-Syntaxkonventionen

Syntax

IDENTITY [ (seed , increment) ]

Diese Syntax wird vom serverlosen SQL-Pool in Azure Synapse Analytics nicht unterstützt.

Argumente

seed

Der Wert, der für die erste in die Tabelle geladene Zeile verwendet wird.

increment

Der inkrementelle Wert, der zum Identitätswert der zuvor geladenen Zeile addiert wird.

Hinweis

In Azure Synapse Analytics sind Werte für die Identität aufgrund der verteilten Architektur des Data Warehouse nicht inkrementell. Weitere Informationen finden Sie unter Erstellen von Ersatzschlüsseln in einem Synapse SQL-Pool mit IDENTITY.

Sie müssen entweder sowohl den Ausgangswert als auch den inkrementellen Wert oder keinen von beiden angeben. Wurden Ausgangswert und inkrementeller Wert nicht angegeben, ist der Standardwert (1,1).

Bemerkungen

Identitätsspalten können zum Generieren von Schlüsselwerten verwendet werden. Die Identitätseigenschaft für eine Spalte garantiert die folgenden Bedingungen:

  • Jeder neue Wert wird auf Grundlage des aktuellen Seeds und Inkrements generiert.

  • Jeder neue Wert für eine bestimmte Transaktion unterscheidet sich von anderen gleichzeitigen Transaktionen für die Tabelle.

Die Identitätseigenschaft für eine Spalte garantiert nicht die folgenden Bedingungen:

  • Eindeutigkeit des Werts: Die Eindeutigkeit muss mit einer PRIMARY KEY- oder UNIQUE-Einschränkung bzw. einem UNIQUE-Index erzwungen werden.

    Hinweis

    Azure Synapse Analytics unterstützt weder PRIMARY KEY- oder UNIQUE-Einschränkungen noch einen UNIQUE-Index. Weitere Informationen finden Sie unter Erstellen von Ersatzschlüsseln in einem Synapse SQL-Pool mit IDENTITY.

  • Aufeinanderfolgende Werte innerhalb einer Transaktion: Bei einer Transaktion, durch die mehrere Zeilen eingefügt werden, ist nicht sichergestellt, dass Sie aufeinanderfolgende Werte für die Zeilen erhalten, da für die Tabelle möglicherweise andere gleichzeitige Einfügungsvorgänge stattfinden. Wenn Werte fortlaufend sein müssen, sollte die Transaktion eine exklusive Sperre für die Tabelle oder die Isolationsstufe SERIALIZABLE verwenden.

  • Aufeinanderfolgende Werte nach Serverneustart oder anderen Fehlern: SQL Server kann Identitätswerte aus Leistungsgründen zwischenspeichern. Einige der zugewiesenen Werte können während eines Datenbankausfalls oder Serverneustarts verloren gehen. Dies kann zu Lücken im Identitätswert beim Einfügen führen. Wenn Lücken nicht zulässig sind, sollte die Anwendung ihren eigenen Mechanismus verwenden, um Schlüsselwerte zu generieren. Die Verwendung eines Sequenzgenerators mit der NOCACHE-Option kann die Lücken auf Transaktionen beschränken, für die nie ein Commit ausgeführt wird.

  • Wiederverwendung von Werten: Für eine bestimmte Identitätseigenschaft mit spezifischem Ausgangswert/Inkrement werden die Identitätswerte von der Engine nicht wiederverwendet. Wenn eine bestimmte INSERT-Anweisung fehlschlägt oder für die INSERT-Anweisung ein Rollback ausgeführt wird, gehen die verwendeten Identitätswerte verloren und werden nicht erneut generiert. Es können Lücken entstehen, wenn die nachfolgenden Identitätswerte generiert werden.

Diese Einschränkungen sind beabsichtigt, um die Leistung zu verbessern. Ferner sind sie in vielen Situationen akzeptabel. Wenn Sie Identitätswerte aufgrund dieser Einschränkungen nicht verwenden können, sollten Sie eine separate Tabelle mit einem aktuellen Wert erstellen und den Zugriff auf die Tabelle und die Nummernzuweisung für die Anwendung verwalten.

Wenn eine Tabelle mit einer Identitätsspalte für die Replikation veröffentlicht wird, muss die Identitätsspalte entsprechend dem verwendeten Replikationstyp verwaltet werden. Weitere Informationen finden Sie unter Replizieren von Identitätsspalten.

Es kann nur eine Identitätsspalte pro Tabelle erstellt werden.

In speicheroptimierten Tabellen müssen sowohl der Ausgangswert als auch das Inkrement auf 1, 1 festgelegt werden. Wenn Sie den Seed oder das Inkrement auf einen anderen Wert als 1 festlegen, kommt es zu folgendem Fehler: The use of seed and increment values other than 1 is not supported with memory optimized tables.

Nachdem die Identitätseigenschaft für eine Spalte festgelegt wurde, kann sie nicht entfernt werden. Der Datentyp kann geändert werden, solange der neue Datentyp mit der Identitätseigenschaft kompatibel ist.

Beispiele

A. Verwenden der IDENTITY-Eigenschaft mit CREATE TABLE

Im folgenden Beispiel wird eine neue Tabelle erstellt. Dabei wird die IDENTITY-Eigenschaft zum automatischen Erhöhen der Identifikationsnummer verwendet.

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. Verwenden generischer Syntax zum Auffinden von Lücken in Identitätswerten

Im folgenden Beispiel wird generische Syntax zum Auffinden von Lücken in Identitätswerten dargestellt. Diese Lücken entstehen, wenn Daten entfernt werden.

Hinweis

Der erste Teil des folgenden Transact-SQL-Skripts dient lediglich zur Veranschaulichung. Sie können das Transact-SQL-Skript ausführen, das mit folgendem Kommentar beginnt: -- 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;

Zugehöriger Inhalt