OUTPUT Command in SQL Server 2005

This not about the output parameter that can be used in stored procedures. Instead, this is about returning data affected in a table with a new feature in SQL Server 2005.

SQL Server 2000

A simple question for you. If you want to retrieve the last inserted identity value, what do you do? Obviously SCOPE_IDENTITY() or @@IDENTITY will be your answer. There is a small different between these too, which I am not going to discuss right now. Even though both will satisfy the current requirement, I will use SCOPE_IDENTITY(), which is the correct one.

CREATE TABLE TempTable
 (
 ID INT IDENTITY(1 , 1)
 , Code VARCHAR(25)
 , Name VARCHAR(50)
 , Salary Numeric(10 , 2)
 ) 
INSERT INTO TempTable ( Code , Name , Salary )
  VALUES ( 'A001' , 'John' , 100 )
INSERT INTO TempTable ( Code , Name , Salary )
  VALUES ( 'A002' , 'Ricky' , 200 )
SELECT SCOPE_IDENTITY() AS LastInsertID

However, this will only be valid when you need the last inserted ID. A Problem arises when you need the last updated or deleted data. In SQL Server 2000, you don't have any other option other than writing a trigger or triggers to capture them via inserted and/or deleted tables.

SQL Server 2005

To satisfy the database developers' cry, SQL Server 2005 has an added feature called OUTPUT. Here is a short example:

INSERT INTO TempTable ( Code , Name , Salary )
OUTPUT Inserted.ID
  VALUES( 'A003' , 'Martin' , 300 )

The INSERT Statement not only inserts data to TempTable but also return the inserted ID value. Unlike, SCOPE_IDENTITY(), you have the ability of getting other affected values as well.

INSERT INTO TempTable ( Code , Name, Salary)
OUTPUT Inserted.ID
 , Inserted.Code
 , Inserted.Name
 , Inserted.Salary
  VALUES( 'A001' , 'Paul',400 )

The code above will return the ID, Code, Name and Salary fields as well.

The major improvement from the output command is that you have the ability of getting the affected values from an Update statement. In most cases you may need the information about the values that were there before they were changed, which you would get normally in an update trigger.

UPDATE TempTable
SET Salary = Salary * 1.10
OUTPUT Inserted.ID
 , Inserted.Code
 , Inserted.Name
 , Deleted.Salary PreviousSalary
 , Inserted.Salary NewSalary

The code above will return the ID, Code, Name along with previous salary and new salary and there is no need for an additional update trigger, which could have other performance issues.

Finally, let's look at deleting data. Even though it is the same as the above example, just for sake of completeness I will show this example.

DELETE FROM TempTable
OUTPUT Deleted.ID
 , Deleted.Code
 , Deleted.Name
 , Deleted.Salary
WHERE ID = 1

Conclusion

In SQL Server 2005, SCOPE_IDENTITY() and @@IDENTITY still work and there is no need to perform any modifications if you need the value of the Identity from an insert.

OUTPUT is another new feature in SQL Server 2005 to improve the productivity of Database developers. So use it whenever you think it is appropriate.