Schlagwort-Archive: identity

MS SQL Server

Sichere Logging-Tabellen trotz @@identity

Schreibe einen Kommentar

In meinem letzten Blog-Eintrag hatte ich geschrieben, man solle möglichst scope_identity() statt @@identity verwenden.

Ich hatte bei einem Kunden genau den Fall, dass wir nachträglich in einer bestehenden Datenbank-Applikation über Trigger Protokollierungen erstellen wollten, um genauere Informationen über ein Fehlverhalten zu bekommen.

Deshalb legten wir eine Logging-Tabelle an, die die entsprechenden Informationen aufnehmen sollte und einen Trigger auf bestimmte Tabellen, um diese Informationen zu sammeln und wegzuschreiben.

Leider hatte der damalige Entwickler @@identity und nicht scope_identity() verwendet, so dass eine Identity-Column in unserer Logging-Tabelle zu Fehlern führte: Beim INSERT erhält das aufrufende Programm eine falsche ID. Hoffentlich ist das Programm sonst sauber programmiert, dann wird man dies wahrscheinlich über krachende Foreign Key-Beziehungen erfahren.

Wir kann man so ein Problem aber lösen, wenn man nichts von dem zugrunde liegenden Programm weiß?

Man  könnte die Identity-Column in der Logging-Tabelle entfernen und stattdessen Unique Identifiers verwenden. Ich hatte ja schon einiges über die Nachteile von Unique Identifiers geschrieben.

In diesem Fall scheint es mir aber gerechtfertigt. Natürlich muss man die Spalte mit newSequentialId() initialisieren, also etwa so:

CREATE TABLE [dbo].[Logging](
    [LogID] [uniqueidentifier]  NOT NULL default(newSequentialId()),
    [Timestamp] [datetime] NOT NULL default(getdate()),
    [Logtext] [nvarchar](max) NULL,
CONSTRAINT [PK_Logging] PRIMARY KEY CLUSTERED
(
    [LogID] ASC
)
)

Dann ist die Änderung unschädlich für den uns unbekannten Code, der möglicherweise @@identity verwendet.

MS SQL Server

Scope_Identity statt @@Identity

Schreibe einen Kommentar

Beides sind Funktionen, um den Wert der Identity-Column nach dem Insert zu erhalten. Als ich SQL Server gelernt hatte (1997), hatte ich nur @@identity gekannt.

@@Identity hat aber einen entscheidenden Nachteil, aber dazu später mehr.

Nehmen wir an, wir haben eine Kunden-Tabelle mit Identity-Column KundeID:

CREATE TABLE [dbo].[Kunden](
    [KundeID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Kunden] PRIMARY KEY CLUSTERED
(
    [KundeID] ASC
)
)

Wenn wir in diese Tabelle einen Kunden eintragen und dann das Ergebnis mit @@Identity oder SCOPE_Identity() abfragen, gibt es keinen Unterschied:

INSERT INTO Kunden SELECT ‚Martin‘
SELECT @@IDENTITY
SELECT SCOPE_Identity()

Beides liefert den Wert 1.

Wenn wir aber nun eine Logging-Tabelle hinzufügen, die ebenfalls eine Identity-Column hat:

CREATE TABLE [dbo].[Logging](
    [LogID] [int] IDENTITY(1,1) NOT NULL,
    [Timestamp] [datetime] NOT NULL default(getdate()),
    [Logtext] [nvarchar](max) NULL,
CONSTRAINT [PK_Logging] PRIMARY KEY CLUSTERED
(
    [LogID] ASC
)
)

Und der Tabelle Kunden einen Insert-Trigger geben, der in diese Tabelle schreibt:

CREATE TRIGGER dbo.tr_i_Kunden
   ON  dbo.Kunden
   AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
   
    INSERT INTO Logging (LogText) VALUES (‚Kunde angelegt‘)
    INSERT INTO Logging (LogText) VALUES (‚und dann habe ich noch überprüft, ob er schon da ist‘)
    INSERT INTO Logging (LogText) VALUES (‚und noch irgendwas anderes auch gemacht‘)

END

Wenn wir jetzt einen Kunden anlegen, werden automatisch noch 3 Zeilen in die Logging-Tabelle geschrieben.

Da @@identity die letzte Identity-Column der aktuellen Connection zurückliefert, liefert dies den Identity-Wert aus der Logging-Tabelle.

Da SCOPE_Identity() die letzte Identity-Column des aktuellen Scopes (also der betrachteten Tabelle) in der aktuellen Connection zurückliefert, gibt das den Identity-Wert der Kunden-Tabelle.

Wir sehen das hier:

INSERT INTO Kunden SELECT ‚mein Schatz‘
SELECT @@IDENTITY
SELECT SCOPE_Identity()

liefert die Werte 3 für @@identity (da in Logging als letztes die Zeile 3 eingefügt wurde) bzw. 2 für Scope_identity().

Man sollte deshalb grundsätzlich SCOPE_IDENTITY() verwenden, da dies das in der Regel gewünschte Verhalten ist.

Es gibt noch eine Funktion IDENT_CURRENT(‘Tabellenname’). Diese liefert den letzten Identity-Wert für diese Tabelle – egal ob aus meiner Connection oder nicht. Damit sollte man also nicht versuchen, den gerade eingefügten Identity-Wert zu ermitteln. Wenn nämlich parallel eine andere Connection schreibt, hat man Pech gehabt.