Home » Microsoft » MB6-886 » Which construct should you use?
You are a database administrator on an instance of SQL Server 2008. You need to perform complex processing on data from the OrderHistory table in your database. You need the ability to scroll backward and forward through the rows in the table and to perform specific actions on rows.
You always need to have access to the most up-to-date data.
Which construct should you use?
A. a dynamic cursor
B. a SELECT…INTO statement
C. a SELECT statement that includes the OUTPUT clause
D. a partitioned view
Correct Answer: A
Explanation/Reference:
You should use a dynamic cursor. Cursors can be used to operate on underlying data on a row-by-row basis. A dynamic cursor allows scrolling forward and backward, and all data changes to the underlying table are visible. Cursors are sometimes unavoidable, but should be replaced with set-based operations or other methods when possible because they can often degrade performance.
You should not use a SELECT…INTO statement. The SELECT…INTO statement selects data from a table and inserts the data into a newly created table. This could not be used to implement the required functionality.
You cannot use a SELECT statement that includes the OUTPUT clause because OUTPUT clause can be used only in INSERT, UPDATE, DELETE, or MERGE statements.When performing DML operations, you can use the OUTPUT clause to obtain and display information about the rows affected by the DML operation. 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.
You should not use a partitioned view. Partitioned views are used when you have similar data stored in multiple tables and want to create a view to allow access to all of the data as if it were stored in a single table. Partitioned views are implemented by creating a view that queries several tables and combines the results using the UNION ALL operator. A partitioned view can improve performance and increase availability, but would not be applicable in this scenario.
