CREATE TABLE (Transact-SQL) IDENTITY (Eigenschaft)

  • Artikel

Gilt für:SQL ServerAzure SQL-DatenbankAzure SQL Managed InstanceAzure 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) ]

Hinweis

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

Hinweis

Informationen zum Anzeigen der Transact-SQL-Syntax für SQL Server 2014 oder früher finden Sie unter Dokumentation zu früheren Versionen.

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 Folgendes:

  • 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 Folgendes:

  • 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 Wert festlegen, der nicht 1 ist, wird der folgende Fehler ausgelöst: The use of seed and increment values other than 1 is not supported with memory optimized tables. (Die Verwendung von Seed- und Inkrementwerten, die nicht 1 betragen, wird für arbeitsspeicheroptimierte Tabellen nicht unterstützt.)

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;  
  
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;

Weitere Informationen

ALTER TABLE (Transact-SQL)
CREATE TABLE (Transact-SQL)
DBCC CHECKIDENT (Transact-SQL)
IDENT_INCR (Transact-SQL)
@@IDENTITY (Transact-SQL)
IDENTITY (Funktion) (Transact-SQL)
IDENT_SEED (Transact-SQL)
SELECT (Transact-SQL)
SET IDENTITY_INSERT (Transact-SQL)
Replizieren von Identitätsspalten