A lot of SQL Server developers still don't seem to know about the OUTPUT clause (SQL Server 2005 and newer) on the DELETE, INSERT and UPDATE statement.

It can be extremely useful to know which rows have been INSERTed, UPDATEd, or DELETEd, and the OUTPUT clause allows to do this very easily - it allows access to the "virtual" tables called inserted and deleted (like in triggers):

DELETE FROM (table) OUTPUT deleted.ID, deleted.Description WHERE (condition)

If you're inserting values into a table which has an INT IDENTITY primary key field, with the OUTPUT clause, you can get the inserted new ID right away:

INSERT INTO MyTable(Field1, Field2) OUTPUT inserted.ID VALUES (Value1, Value2)

And if you're updating, it can be extremely useful to know what changed - in this case, inserted represents the new values (after the UPDATE), while deleted refers to the old values before the UPDATE:

UPDATE (table) SET field1 = value1, field2 = value2 OUTPUT inserted.ID, deleted.field1, inserted.field1 WHERE (condition)

If a lot of info will be returned, the output of OUTPUT can also be redirected to a temporary table or a table variable ( OUTPUT INTO @myInfoTable ).

Extremely useful - and very little known!

Marc