sqlschool.gr logo

articles

Articles of SQLschool.gr Team

The OUTPUT Clause in DML statements

Antonios Chatzipavlis
Wednesday 17 June 2020

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 Chatzipavlis is a highly experienced Data Solutions Consultant and Trainer. He has been working in the IT industry since 1988, holding various roles such as senior developer, IT Manager, Data & AI Solutions Architect and Consultant.

Since 1995, Antonios has focused on modern technologies and software development tools, primarily by Microsoft. He has specialized in Data & AI since 2000, with expertise in Microsoft Data Platform (SQL Server, Azure SQL Databases, Azure Synapse Analytics, Microsoft Fabric, Power BI, AI) and Databricks.

Antonios is also a Microsoft Certified Trainer (MCT) for over 25 years, has been recognized as a Microsoft Most Valuable Professional (MVP) in Data Platform since 2010 and he is in the Data Expert 40 Powerlist 2024 by Boussias. He is the co-founder and visionary behind XLYTiCA, a company dedicated to Data & AI solutions.

Episode

Task Flows in Microsoft Fabric

image

More Episodes...

Tip

Get Certified: Become a Fabric Data Engineer

More Tips...

Become a member

If you want to receive updates from us become a member to our community.

Connect

Explore

Learn

sqlschool.gr © 2010-2025 All rights reserved

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