The overlooked OUTPUT clause

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…

 

Published 17 January 2011 22:01 by steveh99999
Filed under: ,

Comments

# re: The overlooked OUTPUT clause

I've get several blog posts on OUTPUT. The great thing about using OUTPUT is that you can often get rid of triggers by using it, this can makes code more transparent.

18 January 2011 21:47 by MartinBell

# SQL Server Blogs and news for the week 21/01/11 | John Sansom - SQL Server DBA in the UK

Pingback from  SQL Server Blogs and news for the week  21/01/11 | John Sansom - SQL Server DBA in the UK

# re: The overlooked OUTPUT clause

It's a vital part of merge too. I'm sometimes surprised on how slowly new aspects/features are used - we often update to the newest versions but remain enshrined in the constraints and code of the version we used three generations ago.

21 January 2011 13:20 by GrumpyOldDBA

# re: The overlooked OUTPUT clause

Very true Colin, the other 'new feature' I see being ingnored surprisingly ofen is TRY..CATCH - is this just my experience ?

21 January 2011 14:28 by steveh99999

# The under-used OVER clause!

You may be familiary with using the OVER clause with a ranking function, but how many people know it can be used with an aggregate function?

23 January 2011 13:38 by Martin Bell UK SQL Server MVP