How should you extract the information?
You have a table that contains an XML column named XMLDatal. The column contains the
following nodes: • Title • Description • Author • Weight • NumPages You need to design a
stored procedure that will return only the title and description in a tabular result set. The
solution must be developed by using minimum amount of effort. How should you extract the
information?
You need to create a query that calculates the total sales of each OrderlD from a table named Sales.Details
CORRECT TEXT
You need to create a query that calculates the total sales of each OrderlD from a table named
Sales.Details. The table contains two columns named OrderlD and ExtendedAmount.
The solution must meet the following requirements:
Use one-part names to reference columns.
Start the order of the results from OrderlD.
NOT depend on the default schema of a user.
Use an alias of TotalSales for the calculated ExtendedAmount.
Display only the OrderlD column and the calculated TotalSales column.
Provide the correct code in the answer area.
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 large table that has 20 million rows. The table contains the
following columns: • CustomerNumber • CompanyName • ContactFirstName •
ContactLastName The table currently has single-column nonclustered indexes on the
CustomerNumber, CompanyName, and ContactFirstName columns. An application uses
data from this table. The user interface of the application allows the usage of any one filter
from the following list of filters: • CustomerNumber and CompanyName • CompanyName •
ContactLastName • ContactLastName and ContactFirstName In all cases, the listed order
of the columns is the order in which they will appear in the WHERE clause that is
generated. You need to design an indexing strategy for this table, so that the query
optimizer can quickly perform an index seek when searching through the table data. What
should you recommend?
What should you recommend?
You have a server that runs SQL Server 2008 Standard. The server provides data storage
for a thirdparty contact management application. A service-level agreement for the
application does not allow for modifications to be made to the application or to the database
objects used by the application. You need to recommend a strategy to mitigate performance
issues. The strategy must minimize database growth. What should you recommend?
You need to declare a variable of the XML type named invoice
CORRECT TEXT
You have an XML schema collection named Sales.InvoiceSchema.
You need to declare a variable of the XML type named invoice. The solution must ensure that the
invoice is validated by using Sales.InvoiceSchema.
Provide the correct code in the answer area.
You need to create a reporting solution
Your company has four offices. Each office contains a server that runs SQL Server 2008. All
corporate data is stored in a central location. You need to create a reporting solution. The
solution must meet the following requirements: • Minimize hardware costs • Minimize
administrative effort • Minimize the time it takes to generate the reports • Enable users to
run reports locally, even if a WAN link fails What should you do?
You need to create an inline table-valued function named Sales.fn_OrdersByTerritory
CORRECT TEXT
You have a view that was created by using the following code:
You need to create an inline table-valued function named Sales.fn_OrdersByTerritory.
Sales.fn_OrdersByTerritory must meet the following requirements:
Use one-part names to reference columns.
Return the columns in the same order as the order used in OrdersByTerritoryView.
Part of the correct T-SQL statement has been provided in the answer area. Provide the complete
code.
What should you recommend?
You have a server that runs SQL Server 2008 Enterprise. The server contains a 300-GB
database. At the end of every month, several reports are generated from the database.
Users report that it takes a long time to access data from the database while the reports are
being generated. You need to recommend a solution to improve the data access time while
the reports are being generated from the database. The solution must use a minimum
amount of administrative effort. What should you recommend?
You need to create a query that meets the following requirements: References columns by using one-part names o
CORRECT TEXT
You have a database named Sales that contains the tables as shown in the exhibit. (Click the Exhibit
button.)
You need to create a query that meets the following requirements:
References columns by using one-part names only.
Groups aggregates by SalesTerritorylD, and then by ProductlD.
Orders the results in descending order by SalesTerritorylD and then by ProductlD.
Part of the correct T-SQL statement has been provided in the answer area. Provide the complete
code.
You need to design a partition function to segregate records into the defined categories
You are a database developer. You plan to design a database solution by using SQL Server
2008. The database will contain a table named Claims. The Claims table will contain a large
amount of data. You plan to partition the data into following categories: • Open claims •
Claims closed before January 1, 2005 • Claims closed between January 1, 2005 and
December 31, 2007 • Claims closed from January 1, 2008 till date The close_date field in
the Claims table is a date data type and is populated only if the claim has been closed. You
need to design a partition function to segregate records into the defined categories. What
should you do?