You are a database developer on an instance of SQL Server 2008. Your Prod database contains a table named Prospect that is defined as follows:
You are creating Transact-SQL code to remove obsolete prospects from the Prospect table. If the prospect is deleted from the Prospect table, the prospect’s information should be displayed.
What should you do?
A. Use a cursor to iterate through the Prospect table and perform the required deletes, and then display the deleted rows.
B. Use a DELETE statement that includes an OUTPUT clause.
C. Query the Prospect table and perform the deletes within a WHILE loop. j
D. Query the Prospect table using a SELECT…INTO statement to populate a temporary table, and process the temporary table to perform the deletions
Correct Answer: B
Explanation/Reference:
Performing DML operations, you can use the OUTPUT clause to obtain and display information about affected rows. The OUTPUT clause can display this information to the user, insert the data into another permanent or temporary table or table variable using an INTO clause, or pass the data to a nested DML statement for processing.
Within the OUTPUT clause, you specify the column values to be retrieved by using the column names with the INSERTED and DELETED prefixes. Columns included in the OUTPUT clause must use one of the prefixes or an error is generated. For a DELETE statement, only the DELETED prefix is valid, and returns the column value that was deleted. An asterisk (*) indicates all columns in the table. In this scenario, you might use the following DELETE statement to delete prospects and display the deleted rows:
DELETE FROM Prospect OUTPUT DELETED.* WHERE Rating = 0;
All of the other options are incorrect. You can use the OUTPUT clause to accomplish the desired result using a set-based operation, rather than using a row-based approach or a temporary table. Using a set-based operation will provide better performance and minimize the complexity of the code.
