Overview

Κάθε φορά που εκτελούμε ένα DML statement (INSERT, UPDATE, DELETE) και εφόσον δεν έχουμε κάνει χρήση της SET NOCOUNT ON λαμβάνουμε σαν απάντηση το πόσα rows είναι affected.

Υπάρχουν όμως περιπτώσεις ιδιαίτερα όταν εκτελούμε μια stored procedure, που περιέχει DML statements και θέλουμε το αποτέλεσμα να το χρησιμοποιήσουμε στο επόμενο στάδιο της εκτέλεσης της.

Έχουμε μάθει να χρησιμοποιούμε (σοφά φαντάζομαι) temporary tables για τα SELECT statements, αλλά η δημιουργία τέτοιων στα DML statements δεν είναι εύκολα εφικτή με τους παραδοσιακούς γνωστούς τρόπους.

Παρόλα αυτά υπάρχει τρόπος που είναι διαθέσιμος από τον SQL Server 2005 και στις Azure SQL Databases και δεν είναι άλλος από την χρήση του OUTPUT clause.

NOTE
Να σημειώσω ότι εκτός από τα DML statements το OUTPUT clause μπορεί να χρησιμοποιηθεί και στη MERGE.

Usage

Προσωπικά έχω βρει αρκετές χρήσεις στο OUTPUT clause. Aν και διαφωνώ σε ορισμένες χρήσεις που αναφέρονται στο documentation (δείτε το section Remarks), όπως αυτή που γράφει για την εύρεση της τιμής του identity. Eντούτοις θα πρέπει να πω ότι το έχω κάνει αλλά όχι για τις περιπτώσεις που περνάω ένα record κατά το INSERT, αλλά για αυτές που περνάω πολλά μαζί και θέλω να πάρω τις τιμές για το κάθε record. Με έχει εξυπηρετήσει αρκετά στο να αποφύγω Triggers, και μου έχει λύσει τα χέρια στα DELETE και UPDATE statements καθώς μπορώ να δω τα διεγραμμένα rows αλλά και την προηγούμενη και αλλαγμένη εικόνα αυτών.

How it works

To OUTPUT clause πατάει στην λογική που έχουν οι triggers αλλά δεν είναι triggers και εκτελούνται πριν από τους triggers αν έχουμε στους πίνακες. Όπως στους triggers έχουμε τα virtual tables inserted και deleted έτσι και εδώ έχουμε τα ίδια με τα οποία μπορούμε να πάρουμε να νέα rows (inserted) ή τα διαγραμμένα rows (deleted). Δεν υπάρχει updated, αλλά το καλύπτει αυτό με τα deleted που περιέχει την εικόνα του κάθε row πριν την αλλαγή και το inserted που περιέχει την αλλαγμένη εικόνα του κάθε row.

ATTENTION
Υπάρχουν όμως και πράγματα που δεν επιτρέπονται σε αυτό και τα οποία αναφέρονται στο documentation. Θα σταθώ όμως στο ότι το OUTPUT clause επιστρέφει τα αποτελέσματα του στη εκάστοτε χρήση του πχ εφαρμογή χρησιμοποιώντας σειριακό πλάνο εκτέλεσης. Με άλλα λόγια δεν παραλληλίζει και αυτό μπορεί να δώσει performance overhead αν το εφαρμόζουμε πχ σε ένα dataset με μεγάλο αριθμό rows.

Use cases

Για τα παραδείγματα μας θα φτιάξουμε ένα απλό πίνακα

CREATE TABLE T
( 
    col1 INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    col2 DATETIME2(0) NOT NULL DEFAULT (SYSDATETIME()),
    col3 NVARCHAR(20)
);
GO

INSERT

Ας ξεκινήσουμε με το INSERT ας βάλουμε ένα row και το αποτέλεσμα που θα έχουμε στο output θα είναι

INSERT INTO T (col3) 
OUTPUT inserted.*
VALUES ('Antonios')

Αν θέλουμε να επεξεργαστούμε το αποτέλεσμα θα πρέπει να το βάλουμε σε table. Στο παράδειγμα το βάζω σε table variable.

DECLARE @O TABLE( col1 INT, col2 DATETIME2(0), col3 NVARCHAR(20));
INSERT INTO T (col3) 
OUTPUT inserted.*  INTO @O 
VALUES ('SQLschool.gr');
SELECT * FROM @O;

Το ίδιο μπορεί να γίνει αν βάλουμε περισσότερα rows.

DECLARE @O TABLE( col1 INT, col2 DATETIME2(0), col3 NVARCHAR(20));
INSERT INTO T (col3) 
OUTPUT inserted.*  INTO @O 
VALUES ('Azure SQL DB'),('Azure Synapse');
SELECT * FROM @O;

UPDATE

Αντίστοιχα με το UPDATE

UPDATE T
SET col3 += ' *'
OUTPUT deleted.*,inserted.*;

DECLARE @O TABLE( old_col1 INT, old_col2 DATETIME2(0), old_col3 NVARCHAR(20),new_col1 INT, new_col2 DATETIME2(0), new_col3 NVARCHAR(20));
UPDATE T
SET col3 += ' *'
OUTPUT deleted.*,inserted.* INTO @O;
WHERE col1 = 1;
SELECT * FROM @O;

DELETE

Αντίστοιχα με το DELETE

DELETE 
FROM T
OUTPUT deleted.* 
WHERE col1 = 1;

DECLARE @O TABLE( col1 INT, col2 DATETIME2(0), col3 NVARCHAR(20));
DELETE 
FROM T
OUTPUT deleted.* INTO @O
WHERE col1 = 2;
SELECT * FROM @O;

Conclusion

Είδαμε μερικά παραδείγματα για το πως μπορούμε να χρησιμοποιήσουμε το OUTPUT clause, υπάρχουν πολλά ακόμα. Αυτό που λέω πάντα είναι ότι χρησιμοποιούμε πάντα αυτό που έχει την καλύτερη απόδοση στην εκάστοτε περίπτωση.

//Antonios Chatzipavlis