The overlooked OUTPUT clause
- by steveh99999
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…
