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 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 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. The database will contain a table that has a parent-child relationship to itself. Each
child might also be a parent. This might exist up to 10 levels deep. You need to retrieve all
levels by using a single Transact-SQL query. What should you do?

You have a table named Table1. A sample of the data in Table1 is shown in the following
table. There is a defined nonclustered index on the SalesOrderNumber column. The
following query executes against the table. WHERE ‘SO3’ = LEFT(SalesOrderNumber,3)
You need to minimize the amount page I/O that is generated when the query runs. What
should you do?

You are a database developer. You plan to design a database solution by using SQL Server
2008. The database has a table named Sales. The Sales table contains 10 million rows.
You discover that the following query takes a long time to execute. SELECT s.sale_id, …
FROM Sales AS s JOIN Country AS c ON s.Country_id = c.Country_id AND
c.Country_name = ‘USA’ A summary of the execution plan is as shown in the following code
segment. |–Hash Match(Inner Join, HASH:([s].[Country_id]) = ([c].[Country_id]) |–Clustered
Index Scan(OBJECT:([Country].[PK_Country_Country_id] AS [c]) |–Clustered Index
Scan(OBJECT:([Sales].[PK_Sales_Sale_id] AS [s])) 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 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. 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. A database contains a table named Person. The structure of the table is as shown in
the following exhibit. (Click the Exhibit button.) The table has the following indexes: ·A
unique clustered index on the PersonID column named IX_Person_PersonID ·A
nonclustered index on the FirstName and LastName columns named
IX_Person_FirstName_LastName ·A nonclustered index on the PersonType column named
IX_Person_PersonType that has FirstName and LastName as included columns The table
contains approximately 700,000 records. The approximate number of records for each
PersonType is 3,000. You execute the following query. SELECT P.FirstName, P.LastName
FROM Person P WHERE P.PersonType = ‘DR’ You plan to analyze the performance of the
query by using an execution plan. You need to ascertain that the indexes are used
optimally. What should you do?

You are a database developer. You plan to design a database solution by using SQL Server
2008. A database will contain 10 tables that are used to generate reports. Data in the tables
ranges from 50,000 to 100,000 records. During a query execution that joins four tables, you
discover the following problems: ·The size of the tempdb database grows considerably.
·The query execution time is excessive. You need to identify the most likely cause for the
problems by analyzing the execution plan. 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?