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?

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 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?

You are a database developer. You plan to design a database solution by using SQL Server
2008. The database contains a table named Claims. The structure of the Claims table is as
shown in the following table. Only two percent of the claims are open at any point in time.
You discover that queries on claims that have an Open status take a long time to execute.
You need to optimize the performance of the claim-processing queries. What should you
do?

You are a database developer. You plan to design a database solution by using SQL Server
2008. The database will contain information on retail sales transactions of more than 500
stores. The marketing department uses the solution to analyze daily sales patterns for each
store. Users report that the solution takes a long time to retrieve the required data. You
need to ensure that the solution provides results 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 table named Books that contains information about books. Books has the
columns in the following table. You plan to create several queries that will filter on Title and
ISBN. The queries will return values from Title, ISBN, and Description. You need to
recommend an indexing solution to meet the following requirements: ·Minimize the amount
of time required to return the results of the planned queries ·Minimize the number of indexes
What should you recommend?

You are a database developer. You plan to design a database solution by using SQL Server
2008. The database supports a Web site and captures user interactions. These interactions
are stored in the Activity table of the User_Activity database. Data older than six months is
archived to the Activity table of the Archive_Activity database on a different instance of SQL
Server 2008. The structure of the Activity table is as shown in the following table. You plan
to design a solution that allows a single query to generate a report that summarizes user
interactions for the last 12 months. You need to ensure that the solution is implemented.
Which two actions should you perform? (Each correct answer presents part of the solution.
Choose two.)

You are a database developer. You plan to design a database solution by using SQL Server
2008. The database supports a warehousing application and contains data from two
warehouses in a table named Product. The Product table contains a warehouse indicator
field named warehouse_id. The two warehouses are named Warehouse A and Warehouse

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?