Return data from an UPDATE and DELETE -- OUTPUT clause

It is possible to view the changes you've made using the OUTPUT clause while you update a table. Lets test that. I created a copy of Person.Person table from AdventureWorks to test this with top 100 rows.

--Lets delete something 
USE AdventureWorks;
GO

BEGIN TRAN
DELETE TOP (10)  dba_PersonCopy 
OUTPUT deleted.* 

--ROLLBACK

You'll see The deleted Rows.

--Lets Do an update and see what was updated and what was it updated to

UPDATE dba_PersonCopy 
 SET PersonType = 'NT'
 OUTPUT deleted.Persontype AS OldPersonType, inserted.PersonType AS UpdatePersonType
WHERE rowguid = '92C4279F-1207-48A3-8448-4636514EB7E2'

More info at BOL