You are designing a table that will store transactions for an online retailer. You anticipate that the online retailer will have an average of 500,000 transactions per day. You need to design a solution for data aging and archiving. The solution must minimize the amount of time it takes to return query results for active data. What should you implement?

You are designing a database that will be used for reporting purposes. You need to minimize the data storage requirements and improve the application response time. What should you recommend?

You are a database developer. You plan to design a database solution by using SQL Server 2008. You plan to design a complex multi-statement stored procedure in the following manner.

CREATE PROCEDURE Sales.GetCustomerActivity
@StartDate datetime
AS
SELECT order_id, order_date, customer_id
FROM Sales.Orders
WHERE order_date >= @StartDate
…

On testing, you discover that the stored procedure occasionally takes a longer than expected time to execute. You discover that this degradation is caused by the first statement in the stored procedure. You need to ensure that the stored procedure is consistently executed in the minimum possible time. What should you do?

You have a table that contains 5 million rows. The table has a clustered index. You plan to add an additional index on a column that contains 80 percent null values. The column is used extensively in WHERE clauses. You need to implement an index strategy for the column. The solution must minimize the amount of storage space required for the index. Which type of index should you use?

You have a stored procedure that uses a cursor. The stored procedure updates several related tables. You discover that the stored procedure runs slowly and uses a significant amount of resources on the database server. You need to recommend changes to the stored procedure to meet the following requirements:
* Minimize execution time
* Minimize development effort
* Minimize server resource requirements
What should you recommend?

Users frequently update millions of rows in a table at a time. Users report that it takes a long time to update the rows. You need to recommend a solution to reduce the time it takes to update the rows. The solution must be developed in the minimum amount of time. What should you do?

You are a database developer. You plan to design a database solution by using SQL Server 2008. A database contains a table named Policies. The table contains information about 100 million insurance policies. A complex stored procedure executes daily to calculate the risk amount of each policy and stores the information in the table. When the stored procedure is executed, users experience poor performance and query time-out errors. The queries used in the stored procedure are optimized for performance. You need to ensure that the disruption to users is minimal while the stored procedure is being executed. What should you do?

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?