go backarticles

Articles of SQLschool.gr Team

The OUTPUT Clause in DML statements

Antonios Chatzipavlis

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 sample - SQL Script

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 θα είναι

SQL Script

INSERT INTO T (col3) 
OUTPUT inserted.*
VALUES ('Antonios')
Result output
col1 col2 col3
1 2020-06-17 08:00:20 Antonios

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

SQL Script

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;
Result output
col1 col2 col3
2 2020-06-17 08:01:10 SQLschool.gr

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

SQL Script

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;
Result output
col1 col2 col3
3 2020-06-17 08:02:10 Azure SQL DB
4 2020-06-17 08:02:10 Azure Synapse

UPDATE

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

SQL Script

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

SQL Script

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;
Result output
old_col1 old_col2 old_col3 new_col1 new_col2 new_col3
1 2020-06-17 08:20:20 Antonios 1 2020-06-17 08:20:20 Antonios *

DELETE

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

SQL Script

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

SQL Script

DECLARE @O TABLE( col1 INT, col2 DATETIME2(0), col3 NVARCHAR(20));
DELETE 
FROM T
OUTPUT deleted.* INTO @O
WHERE col1 = 2;
SELECT * FROM @O;
Result output
col1 col2 col3
2 2020-06-17 08:01:10 SQLschool.gr

Conclusion

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


//Antonios Chatzipavlis


Antonios Chatzipavlis

Antonios Chatzipavlis

Antonios is a Data Solutions Consultant and Trainer. He has been working in IT since 1988. In his career, he has worked as senior developer, IT Manager, Solutions Architect and IT Consultant. Since 1995 he has been devoted on new technologies and software development tools, mainly by Microsoft, either by training company staff and colleagues or assisting them in design, development and implementation as a consultant or chief developer. He has focused in Databases and Data Science since 1995. He specialized in Microsoft SQL Server since version 6.0 in areas like SQL Server Internals, Database Design and Development, Business Intelligence and in 2010 he has started working with Azure Data Platform, NoSQL databases, Big Data Technologies and Machine Learning. He is an active member of many IT communities in Greece, answering colleagues' questions and writing articles in his web site. He is the owner of SQLschool.gr which is a community portal with a lot of information about Microsoft SQL Server. He has been a Microsoft Certified Trainer (MCT) since 2000. Microsoft honored him as MVP on Data Platform due to his activities in SQL Server since 2010. He holds a large number of Microsoft Certifications and Microsoft SQL Server Certifications since version 6.5.


Leave your comment

COMMENT

FULL NAME

EMAIL ADDRESS

We use Gravatar

WEB SITE



captcha


 

Newsletter

If you want to receive updates from us subscribe below with your email.
Follow us in
PASS chapter logo

The Official PASS Local Group for Greece

About us Contact us Terms of Use Privacy Sing in Register
sql school greece logo
© 2010-2020 All rights reserved

This site uses cookies for operational and analytics purposes only. By continuing to browse this site, you agree to their use.