What did you learn today? - Cool new features I found in SQL 2005

PERSISTED computed columns and the OUTPUT clause are 2 "new" features I discovered in SQL 2005 while researching SQL 2008.

OUTPUT Clause

A lot of people already know about this pretty cool feature, but I didn’t. In the past, if you do an insert to a table, and the table has an identity column, after the insert, you would look at @@IDENTITY or scope_identity(). That always seemed hackish to me.

You can now add an OUTPUT clause to the end of an INSERT, UPDATE, or DELETE statement. That will return the rows changed by the statement.

INSERT INTO Person.Address

(AddressLine1,AddressLine2,City

,StateProvinceID,PostalCode,rowguid,ModifiedDate)

VALUES(@AddressLine1,@AddressLine2,@City

,@StateProvinceID,@PostalCode,@rowguid,@ModifiedDate)

OUTPUT INSERTED.AddressID;

You use the pseudo tables INSERTED and DELETED, just like in triggers.

What did you learn today? - Cool new features I found in SQL 2005