This is part of the series of posts that I am running under the title "Under Appreciated Features of SQL Server". The series has been inspired by Andy Warren’s editorial on SQLServerCentral.com.

The UPDATE Statement

Today, I will attempt to approach describing the OUTPUT clause in a slightly different manner. We will simply forget about it for a moment. We will talk about something much more simple and well-known like the UPDATE.

But, the UPDATE statement is not that simple – it’s actually two statements rolled into one – a DELETE and an INSERT are actually wrapped into a single statement.

Does that sound familiar? I am sure that you would have heard about the two special tables that SQL Server provides – the DELETED and INSERTED tables (Reference: http://msdn.microsoft.com/en-us/library/aa214435(SQL.80).aspx). These tables are used within triggers to:

• Extend referential integrity between tables
• Insert or update data in base tables underlying a view
• Check for errors and take action based on the error
• Find the difference between the state of a table before and after a data modification and take action(s) based on that difference

Here’s a quick example:

USE AdventureWorks2008R2
GO
--Step 01: Create two tables - the test table, and an Audit table
CREATE TABLE MyTABLE (MyId INT, MyCity VARCHAR(20))
CREATE TABLE MyAudit (MyChangedId INT, OldCityValue VARCHAR(20), NewCityValue VARCHAR(20), ModificationDate DATETIME)
GO

--Step 02: Insert some test data into these tables
INSERT INTO MyTABLE VALUES (1,'Gandhinagar'),
                           (2,'Ahmedabad'),
                           (3,'Mumbai'),
                           (4,'Delhi'),
                           (5,'Bangalore')
GO

--Step 03: Create a test trigger to demonstrate the most typical use of DELETED & INSERTED tables
CREATE TRIGGER trig_MyTable
ON MyTABLE
FOR UPDATE
AS
BEGIN
    INSERT INTO MyAudit (MyChangedId, OldCityValue, NewCityValue, ModificationDate)
                SELECT [DELETED].[MyId],
                       [DELETED].[MyCity],
                       [INSERTED].[MyCity],
                       GETUTCDATE()
                FROM [DELETED] [DELETED]
                INNER JOIN [INSERTED] [INSERTED] ON [DELETED].[MyId] = [INSERTED].[MyId]
END
GO

--Step 04: Perform a test update
SET STATISTICS IO, TIME ON

UPDATE mt
SET mt.MyCity = 'New Delhi'
FROM MyTABLE mt
WHERE mt.MyId = 4

SET STATISTICS IO, TIME OFF

--Step 05: Take a quick look at the data
SELECT MyId, MyCity FROM MyTABLE
SELECT MyChangedId, OldCityValue, NewCityValue, ModificationDate FROM MyAudit

--Step 06 - Perform some cleanup
DROP TABLE MyTABLE
DROP TABLE MyAudit

Upon running these scripts, you would notice that this is quite an reasonable method to maintain a custom log of critical transactions/queries.

The OUTPUT Clause

However, it might be possible that sometimes, you may not want to use triggers (because of personal choice, or because of the fact that triggers can be disabled); or this is a one-off debugging exercise or just an effort to learn the internals of SQL Server. In such cases, the recently introduced OUTPUT clause can be very useful.

USE AdventureWorks2008R2
GO
--Step 01: Create two tables - the test table, and an Audit table
CREATE TABLE MyTABLE (MyId INT, MyCity VARCHAR(20))
CREATE TABLE MyAudit (MyChangedId INT, OldCityValue VARCHAR(20), NewCityValue VARCHAR(20), ModificationDate DATETIME)
GO

--Step 02: Insert some test data into these tables
INSERT INTO MyTABLE VALUES (1,'Gandhinagar'),
                           (2,'Ahmedabad'),
                           (3,'Mumbai'),
                           (4,'Delhi'),
                           (5,'Bangalore')
GO

--Step 03: Perform a test update
SET STATISTICS IO, TIME ON

UPDATE mt
SET mt.MyCity = 'New Delhi'
    OUTPUT [deleted].[MyId], [deleted].MyCity, [inserted].MyCity, GETUTCDATE()
    INTO MyAudit
FROM MyTABLE mt
WHERE mt.MyId = 4

SET STATISTICS IO, TIME OFF

--Step 04: Take a quick look at the data
SELECT MyId, MyCity FROM MyTABLE
SELECT MyChangedId, OldCityValue, NewCityValue, ModificationDate FROM MyAudit

--Step 05 - Perform some cleanup
DROP TABLE MyTABLE
DROP TABLE MyAudit

What’s the benefit?

Drastically reduced & simplified code, and yet we get the same results. At the end of the day, it’s all about the benefits of a particular approach. So, do we have any improvement? We had the I/O and TIME statistics ON during the UPDATE statements. Let’s see what stories they tell us.

Here’s the output from the conventional trigger-based approach.

Table ‘MyTABLE’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

(1 row(s) affected)

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 8 ms.

Table ‘MyAudit’. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

(1 row(s) affected)

SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 120 ms.

SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 131 ms.

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

And here’s the same from the OUTPUT clause:

SQL Server parse and compile time:

   CPU time = 10 ms, elapsed time = 15 ms.

Table ‘MyAudit’. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ‘MyTABLE’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

(1 row(s) affected)

SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 58 ms.

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

While there is no difference in the I/O (1 scan, 2 logical reads), the execution time has definitely improved with a total of (8+120+131) ms = 159ms with the trigger based approach v/s (15+58) ms = 75ms with the OUTPUT clause.

Finally, the OUTPUT clause can be used with all 3 of the major T-SQL statements – UPDATE, INSERT and DELETE!

Some finer points

The OUTPUT clause does not support:

1. Subqueries or user-defined functions that perform user or system data access, or are assumed to perform such access. User-defined functions are assumed to perform data access if they are not schema-bound
2. A remote table, view, or common table expression as it’s target
3. A target with a FOREIGN KEY constraint, or referenced by a FOREIGN KEY constraint
4. Triggers on the target
5. A target participating in replication

Besides the above, be careful with the behaviour of @@ROWCOUNT. It will only return the number of rows affected by the outer INSERT statement.

Lots more information can be obtained by visiting the Books-On-Line page at: http://msdn.microsoft.com/en-us/library/ms177564(v=SQL.110).aspx

In Conclusion – a small challenge

Now that you are powered with the knowledge of the OUTPUT clause, can you answer the following Question of the Day at SQLServerCentral?

http://www.sqlservercentral.com/questions/T-SQL/72013/

Do leave a note as to how you did, and what are your thoughts on the same.

Until we meet next time,

Be courteous. Drive responsibly.