Home » Microsoft » MB6-886 » What should you do?
You are a database developer on an instance of SQL Server 2008 for a large retail sales organization. Your SalesOrderDetail table contains millions of rows with details of all sales orders.
Each day, you need to run a query that joins the SalesOrderDetail table with the Product table, performs a complex aggregation for each row of SalesOrderDetail, and writes the results to the SalesStats table.
You want to ensure the most current data is used in the aggregations, but that the query does not adversely affect sales representatives that are taking orders from customers.
What should you do?
A. Process the query within a transaction and set the transaction’s isolation level to REPEATABLE READ.
B. Create a stored procedure that implements transactions to process rows in the SalesOrderDetail table in small batches.
C. Create a query that builds a temporary table and run your query against the temporary table.
D. Create a CLR user-defined aggregate.
Correct Answer: B
Explanation/Reference:
Using a transaction, you would be able to read the most recent data to perform your aggregations. However, if you initiated the transaction and performed all the aggregations at one time, other users’ access to the table would be adversely affected. Splitting the work into small batches would allow you to perform periodic commits and not lock the table for the entire duration of the query.
You should not process the query within a transaction and set the transaction’s isolation level to REPEATABLE READ. The REPEATABLE READ isolation level prevents statements from reading data that has been modified by other transactions but not yet committed. It also prevents other transactions from modifying data that has been read by the current transaction until the current transaction completes. This would adversely affect users taking orders because they could not modify any data that was read by your transaction until your transaction completed.
You should not create a query that builds a temporary table and run your query against the temporary table because this approach would not allow you to perform aggregations on the most up-to-date data.
You should not create a CLR user-defined aggregate. CLR user-defined aggregates are created in a .NET Framework language. They allow you to create custom aggregate functions that users can call like other built-in aggregate functions, such as SUM, MAX, or COUNT. Using a CLR user-defined aggregate would not ensure that other users were not affected when your query executed.
