Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Jason Carter

Jason Carter has spent most of his career as a .NET developer, with time spent as a development manager, accidental DBA, and most recently a full-time DBA. Having worked with large databases as a developer, he found great interest in tuning, tweaking, and making databases run faster. With the support of his wife, he gave up his managerial duties, jumped the development ship and dove head first into his new career as a Database Administrator.

5 Things Series – Output Clause

In this series I’m going to detail the 5 T-SQL Commands that review in my  5 T-SQL Commands I’ve been Missing, but were there the whole time presentation.  The gist of this presentation details my past as an uninformed developer, as it pertains to T-SQL and its vast features.   This presentation, and accompanying blog series, will highlight some of the commands I’ve found useful.

For this demo I have downloaded the DBA.StackExchange.com data dump from this torrent, and loaded it with the Stack Overflow Data Dump Importer that Jeremiah Peschkaj has cloned over to Github.

 

Have you ever done something like this?  You insert into a table, then select @@IDENTITY, or @SCOPE_IDENTITY  to get back its newly created identity value?

insert into DBA.Badges (UserId, Name, Date)
values (21794,'Presentre', GETDATE())

select @@IDENTITY

While not celebrated as amazing or revolutionary now, SQL 2005 came with a ton of T-SQL additions and improvements.  One of those improvements was the Output Clause.

Microsoft tells us that the OUTPUT clause:

?Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, or DELETE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. Alternatively, results can be inserted into a table or table variable.
Basically, we can ‘output’  from the same statement that did the INSERT, UPDATE or delete.  So our prior example can be rewritten as this:

insert into DBA.Badges (UserId, Name, Date)
OUTPUT inserted.Id
values (21794,'Demoer

That’s pretty neat, but where did that inserted table come from? That’s a virtual table of the data that was inserted, WITH any auto-generated fields.  If you have an IDENTITY field, such as the Id field in the above example, it will be presented, anything with a default value will also be presented in the inserted table.   For the most part you can treat this table just like any other table as far as selects go.

The wonder of this command does not end with inserts, it also extends to deletes as well.  Since i have ‘Demoer’ sounds dumb, let’s get rid of that.

delete from DBA.Badges
OUTPUT Deleted.*
where UserId=21794 and Name='Demoer', GETDATE())

You can see from the above example that we also get the Deleted table.  This is a great way to validate what was deleted was what you wanted to delete.

For updates, we do not get an updated table, but we do get both the inserted and deleted tables, from which we can combine the output to see our before and after picture.

update DBA.Badges 
set name='Presenter' 
OUTPUT deleted.*, inserted.*
where UserId=21794 and name='Presentre'

While I’ve found great use for these in ad-hoc situations, I’ve also utilized the OUTPUT clause to replace the insert, select @@IDENTITY pattern I’ve shown above.  There are some gotchas that are detailed on the MSDN page for this command, but for the most part those are edge cases that you should review if the OUTPUT isn’t as you expect.

 

Comments

Leave a comment on the original post [jason-carter.net, opens in a new window]

Loading comments...