I often find myself applying ad-hoc data updates to production systems – usually running scripts written by other people. One of my favourite features of SQL syntax is the OUTPUT clause – I find this is rarely used, and I often wonder if this is due to a lack of awareness of this feature..

The OUTPUT clause was added to SQL Server in the SQL 2005 release – so has been around for quite a while now, yet I often see scripts like this…

SELECT somevalue FROM sometable WHERE keyval = XXX

UPDATE sometable

SET somevalue = newvalue

WHERE keyval = XXX

-- now check the update has worked…

SELECT somevalue FROM sometable WHERE keyval = XXX

This can be rewritten to achieve the same end-result using the OUTPUT clause.

UPDATE sometable

SET somevalue = newvalue

OUTPUT deleted.somevalue AS ‘old value’,

             inserted.somevalue AS ‘new value’

WHERE keyval = XXX

The Update statement with output clause also requires less IO - ie I've replaced three SQL Statements with one, using only a third of the IO. 

If you are not aware of the power of the output clause – I recommend you look at the output clause in books online

And finally here’s an example of the output produced using the Northwind database…