You are a database developer. You plan to design a database solution by using SQL Server 2008. Account managers in your company store order data in a database. Your company requires a list of customers for each account manager. The list must be sorted in the descending order of the order amount. You create a query that generates the list at the end of each month. You need to ensure that the query executes as quickly as possible. What should you do?

You are a database developer. You plan to design a database solution by using SQL Server 2008. The database application has a table named Transactions that contains millions of rows. The table has multiple columns that include transaction_id and transaction_date. There is a clustered index on the transaction_id column. There is a nonclustered index on the transaction_date column. You discover that the following query takes a long time to execute:

SELECT transaction_id, transaction_date, transaction_notes
FROM transactions
WHERE transaction_type_id = ‘FXO’
AND transaction_date between @start_date and @end_date

The summary of the execution plan is as shown in the following code segment:

|– Filter(WHERE:([transaction_type_id]=’FXO’)
|– Nested Loops(Inner Join)
|– Index Seek(OBJECT:([transactions]. [nc_transactions_transaction_date])
|– Clustered Index Seek(OBJECT:([transactions]. [PK_transactions_transaction_id])

You need to ensure that the query retrieves data in minimum possible time. What should you do?

You are a database developer. You plan to design a database solution by using SQL Server 2008. The database includes a table that contains the following product inventory information:
* Department
* Class
* Item
* Quantity
You plan to write a query that produces the sum of quantity data broken into the following groups:
* Department
* Department and Class
* Department and Item
* Department, Class, and Item
You need to write the query by using the minimum possible number of Transact-SQL statements. What should you recommend?

You are a database developer. You plan to design a database solution by using SQL Server 2008. A stored procedure uses the INSERT, UPDATE, and DELETE statements separately to load data into a table. You need to rewrite the stored procedure to use a single statement to load the data. What should you do?

You need to design a method for storing large XML-formatted data. The design must meet the following requirements:
* Minimize the page I/O
* Minimize the response time for data manipulation language (DML) queries
What should you do?

You have a table that has an XML column named XMLOrderHeader. You need to design a stored procedure that extracts the order header values and stores them in a table. The solution must meet the following requirements:
* Extract many values
* Minimize the development effort
What should the solution include?

You plan to implement a Web-based application that will save XML data to a column in a table. You need to design a query that ensures that before saving the XML data to the table, the data contains valid elements. The solution must be developed by using the minimum amount of effort. What should you include in the query?

You have a table that has five varchar columns. You are designing an application that requires data in well-formed XML. You need to design a query statement that will produce the data in well-formed XML. What should you use?

Your company has the following development policy for XML data:
* The data must be element-centric
* The data must be well-formed XML
* The data must have a root element
* The data must contain the parent table name
You need to recommend guidelines for generating well-formed XML result sets. What should you recommend?

You are designing a database that contains a data definition language (DDL) trigger. The DDL trigger will provide the maximum amount of data available when any attempt is made to change the database schema. You need to design a table to meet the following requirements:
* Accept the EVENTDATA information that is provided by the trigger
* Support the searching and retrieval of nodes and values
* Minimize development time
Which data type should you use?