IDENTITY-Eigenschaft (Transact-SQL)

Artikel
10/31/2013

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.

Themenlink (Symbol) Transact-SQL-Syntaxkonventionen

Syntax

IDENTITY [ (seed , increment) ]

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.

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).

Hinweise

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 Ausgangswertes und Inkrements generiert.
Jeder neue Wert für eine bestimmte Transaktion kann sich von anderen gleichzeitigen Transaktionen für die Tabelle unterscheiden.

Die Identitätseigenschaft für eine Spalte garantiert nicht Folgendes:

Eindeutigkeit des Werts – Eindeutigkeit muss erzwungen werden, indem Sie die Einschränkung PRIMARY KEY oder UNIQUE oder den Index UNIQUE verwenden.
Aufeinanderfolgende Werte innerhalb einer Transaktion – Bei einer Transaktion, bei der mehrere Zeilen eingefügt werden, ist nicht sichergestellt, dass aufeinanderfolgende Werte für die Zeilen abgerufen werden, da für die Tabelle möglicherweise andere gleichzeitige Einfügungen auftreten. Wenn Werte dann 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 einen Sequenz-Generator mit der Option NOCACHE oder ihren eigenen Mechanismus verwenden, um Schlüsselwerte zu generieren.
Wiederverwendung von Werten – Für eine gegebene Identitätseigenschaft mit bestimmtem Ausgangswert/Inkrement werden die Identitätswerte nicht vom Modul 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 Zahlenzuweisung für Ihre Anwendung verwalten.

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

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

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 AdventureWorks2012
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 der generischen Syntax zum Auffinden von Lücken in Identitätswerten

Im folgenden Beispiel wird die 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