OUTPUT clause – knowing what goes in, and what you’ve accidentally taken out

The OUTPUT clause has to be one of the best T-SQL features out there. It was new in SQL Server 2005, but it’s still remarkably little known. I guess like many of the features that were introduced in SQL 2005, many people just make do with the way they did things before.

The basic point of the OUTPUT clause is to turn a data modification statement into one that returns data (although this data can be redirected to an existing table by simply adding “INTO tablename” after it), by providing a way of accessing the inserted and deleted tables that we have been using in triggers for years. If a DELETE statement is being issued, only the deleted table is available, and similarly inserted for an INSERT statement. UPDATE provides both – and the MERGE statement (new in SQL Server 2008) populates both (although it feels as if a FULL JOIN has been applied to them).

I’m sure it’s easy to imagine the tremendous advantage to using it with UPDATE statements – auditing. It’s very easy to push the changes to an audit table, without the need to create a trigger to do it for us. But I find that the biggest advantage is for those times when carelessness has got the better of us. Hopefully these times are rare (and the more experienced we get with databases the more we seem to respect the sanctity of the data), but if you always use “OUTPUT deleted.*” when deleting, or “OUTPUT deleted.*, inserted.*”, then the odd time when you see more rows come back that you expected, you can have a plan associated with your “Oops” moment.

If you don’t use the OUTPUT clause, you get a message that tells you how many rows were affected by your query. If you expect that number to be small, and it’s actually quite large, you’ve probably done something wrong. “Oops” is probably an understatement. You may have started a transaction and be able to roll it back, but until such time as you get to that, you have locks which aren’t being released. Regardless of whether or not you can roll it back, having something which shows you what you’ve just done can really help you out. You can copy the data presented into Excel, or Notepad, or whatever, and work out your problem. If you can’t roll it back, then this may involve some sort of import process being quickly thrown together.

The benefit is far less obvious when inserting data – but in some ways, it’s actually even more useful.

We’ve had the @@IDENTITY and SCOPE_IDENTITY() functions available for some time, and they’re widely used. But if multiple records are inserted, or if the targetted table doesn’t contain an identity field, then they’re actually not quite so great. Knowing which record is which is a question of re-querying the data and hoping you can tell. But if you “OUTPUT inserted.*”, your application can immediately tell which row was inserted with which surrogate key (it’s less of an issue if you use a natural primary key of course). With a large number of systems opting to use guids for PKs, defaulting to a value of newid(), it really helps to have an OUTPUT clause so that the guid doesn’t have to be generating prior to performing the insert.

The biggest caveat with the OUTPUT clause is that it can be ruined by triggers. A trigger being called can prevent the inserted and deleted tables from being available at the end of the statement. I’d like to have a way of stating that the OUTPUT clause should still work, returning the state of the inserted and deleted tables prior to any triggers being called, but I suppose I can understand the logic behind the decision to disallow it.

This was one of the tips in my TechEd Australia presentation, which was re-delivered to user groups in Adelaide and Melbourne. The scripts and slides are available for download. This was one of the more popular tips, based on feedback.