Advanced T-SQL: OUTPUT und $IDENTITY

6. Oktober 2014
Wenn innerhalb des Microsoft SQL-Servers neue Daten in eine Tabelle mit einer Identitätsspalte eingefügt werden, stellt sich häufig folgende Frage: Wie können am effektivsten die neuen ID-Werte der zuletzt eingefügten Datensätze ermittelt werden?
 
Der folgende Flurfunkartikel repräsentiert einen weiteren Teil einer kleinen Serie zum Thema "Advanced T-SQL", zu welchem bereits die Artikel Advanced T-SQL: ISNULL vs. COALESCE und Advanced T-SQL: WITH TIES erschienen sind.
 
Die eben beschriebene Problemstellung soll im Beitrag zunächst anhand einfacher T-SQL-Code-Beispiele dargelegt werden. Anschließend werden durch Einsatz der OUTPUT-Klausel elegante Lösungsansätze aufgezeigt.
Neue Werte in Identitätsspalte einfügen

Per Default wird der Wert einer Identitätsspalte nach Absetzen des INSERT-Befehls automatisch ermittelt. Wird beispielsweise folgende Tabellenstruktur 

   1: CREATE TABLE [TblExpert]

   2: (

   3:     [ExpertID] INT IDENTITY(1, 1) NOT NULL,

   4:      [Name] NVARCHAR(MAX) NOT NULL,

   5:     [Nachname] NVARCHAR(MAX) NOT NULL

   6: );

verwendet und anschließend ein INSERT-Statement in der Form 

   1: INSERT INTO TblExpert(Name, Nachname) VALUE ('Matthias', 'Pohl');

auf die neu angelegte Tabelle abgesetzt, so kann mit einer geeigneten Abfrage, z.B. 

   1: SELECT [ExpertID], [Name], [Nachname]

   2: FROM [TblExpert]

   3: WHERE [ExpertID] = 

   4: (

   5:     SELECT MAX([ExpertID])

   6:     FROM [TblExpert]

   7: );

der neue Datensatz mit der ID-Spalte (hier im konkreten Fall: “[ExpertId]”) ermittelt werden.

Die nachfolgende Abbildung zeigt das Ergebnis der Abfrage:
image3_thumb
 
Der Nachteil bei diesem Verfahren ist, dass – ohne Kenntnis des CREATE-Statements zur Tabelle – die “[ExpertId]”-Spalte nicht automatisch als Identitätsspalte erkannt wird. Des Weiteren muss sowohl für das Einfügen als auch das anschließende Auslesen der Daten jeweils einmal auf die Datenbank zugegriffen werden. Wird zusätzlich noch berücksichtigt, dass ohne Transaktionsblock um das INSERT- und SELECT-Statement zusätzlich Dateninkonsistenzen durch Nebenläufigkeit bzw. Parallelität entstehen können, zeigt sich sehr schnell, dass diese Variante der Wertermittlung für die ID-Spalte nicht sonderlich geeignet ist.
 
Systemfunktionen für Identitätsspalte
Um Identitätsspalten auszulesen, bietet der SQL-Server nun einige Systemfunktionen an. Die bekanntesten sind hierbei sicherlich: 
Mit vorangestelltem SELECT-Schlüsselwort kann durch diese Funktionen der neu eingefügte Wert der Identitätsspalte ermittelt werden.
Doch auch die Verwendung dieser Systemfunktionen birgt einige Nachteile. Die oben aufgeführten Funktionen im SQL-Server können durch zusätzliche mögliche Schreiboperationen signifikant beeinflusst werden. Hierzu zählen insbesondere parallele Datenbank-Zugriffe ohne Transaktionskontrolle sowie möglicherwiese ausgelöste Trigger-Aktionen.
 
Wird beispielsweise für die [TblExpert]-Tabelle ein Trigger in der Form 
   1: CREATE TRIGGER TblExpertAfterInsert

   2: ON [TblExpert]

   3: AFTER INSERT

   4: AS 

   5: BEGIN

   6:     IF TRIGGER_NESTLEVEL() > 1 RETURN

   7:     

   8:     INSERT [TblExpert] 

   9:     (Name, Nachname)

  10:     SELECT Name+'2', Nachname+'2' FROM Inserted;

  11: END

 
hinterlegt und werden anschließend die folgenden beiden SQL-Statements ausgeführt
   1: INSERT INTO TblExpert(Name, Nachname)

   2: VALUES ('Matthias', 'Pohl');

   3:  

   4: SELECT 

   5: @@IDENTITY AS '@@IDENTITY', 

   6: SCOPE_IDENTITY() 'SCOPE_IDENTITY()', 

   7: IDENT_CURRENT('[TblExpert]') AS 'IDENT_CURRENT(''[TblExpert]'')';

so liefern die oben erwähnten Systemfunktionen, wie in der folgenden Abbildung dargestellt, unterschiedliche Werte:
 
image
 
Des Weiteren wird auch hier, wie im vorrangegangen Beispiel, beim Ermitteln des Wertes ein zweiter (unnötiger) Lesezugriff auf die Datenbank erfordert.
Die folgende Abbildung, welche die Message-Konsole des SQL-Server Management Studios zeigt, legt dar, dass sowohl für den INSERT- als auch den anschließenden SELECT-Befehl jeweils ein Zugriff erfolgt:
image_thumb2
 
Verwendung der OUTPUT-Klausel
Datenmanipulationsbefehle (kurz: DML-Anweisungen) wie MERGE, INSERT, UPDATE oder DELETE sind hauptsächlich für das Bearbeiten und Verändern von Datensätzen konzipiert worden. Durch Einsatz der OUTPUT-Klausel können jedoch zusätzlich (und quasi gleichzeitig) noch Datensätze mit zurückgegeben werden. 
Als Beispiel dient folgende Einfüge-Operation, welche sich am obigen Szenario orientiert und das $IDENTITY-Schlüsselwortes/-Prädikats verwendet: 
   1: INSERT INTO TblExpert([Name], [Nachname]) 

   2: OUTPUT INSERTED.$IDENTITY

   3: VALUES ('Matthias', 'Pohl');

 
Mit Hilfe der OUTPUT-Klausel und des $IDENTITY-Prädikats innerhalt des obigen SQL-(INSERT)-Statements können mittels Zugriff auf die INSERTED-Tabelle durch eine einzige atomare SQL-Anweisung neue Datensätze eingefügt und zeitgleich die neu eingefügten Werte in der ermittelt werden. Hierfür sind keine(!) weiteren Lesezugriffe auf der Datenbank und somit auch keine Zugriffskontrolle notwendig, der Zugriff auf die Datenbank erfolgt hier nur einmal.
 
Als Ergebnis des obigen INSERT-Statements wird folgender Datensatz zurückgegeben:
image_thumb41
Des Weiteren werden keine zusätzlichen Funktionaltäten (z.B. komplexe Aggregationen etc.) gegenüber einem INSERT-Statement  ohne OUTPUT-Klausel benötigt. Die folgende Abbildung, welche den Ausführungsplan eines INSERT-Statement mit und eines ohne OUTPUT-Klausel gegenüber stellt, belegt dies: 
image_thumb4
 
Fazit
Der Artikel zeigt den Einsatz der OUTPUT-Klausel vorrangig anhand von einfachen INSERT-Befehlen  für Tabellen mit Identitätsspalte. Der Einsatzes des OUTPUT-Schlüsselworts offeriert jedoch noch weitere Vorteile. Zum einen kann er auch für MERGE-, DELETE- und UPDATE-Statements verwendet werden und zum anderen funktioniert er auch wenn eine T-SQL-Anweisungen Einfluss auf mehrere Datensätze, z.B. multipler INSERT-Befehl, hat. Dann können nämlich sämtliche neu eigefügte Datensätze ermittelt werden.
 
Quellen