Fantastic! Time for another chunk of knowledge :)

So far, we've deleted data from a table without seeing details about the deleted records. Sometimes, though, we would like to know in detail what we're deleting. SQL Server gives us the OUTPUT clause, which displays the values of columns in deleted rows.

DELETE FROM Student
OUTPUT Deleted.*
WHERE LastName IS NULL;

This returns a table with all the removed records—in this case, records for any students without a last name.

The new element in this DELETE statement is the OUTPUT clause, you put it after the table name. In the OUTPUT clause, you can use the prefix Deleted to refer to the rows deleted by this statement. Notice that the Deleted prefix is separated by a dot from the asterisk character (*). The asterisk denotes all columns. Of course, in its place, you could list chosen column names, like this:

DELETE FROM Student
OUTPUT Deleted.LastName, Deleted.FirstName
WHERE LastName IS NULL;

This gives you the chance to see deleted records.