Online Access Free 070-459 Practice Test

You need to recommend a solution to improve the performance of usp_UpdateInventory. The solution must minimize the amount of development effort.
What should you include in the recommendation?

• A.a common table expression
• B.a cursor
• C.a subquery
• D.a table variable

You are designing a database named DB1.
Changes will be deployed to DB1 every Wednesday night.
You need to recommend a strategy to deploy the changes to DB1. The strategy must meet
the following requirements:
The strategy must not disrupt backup operations.
DB1 must be online while the changes are deployed.
You must be able to undo quickly any changes made to objects.
What should you recommend?
More than one answer choice may achieve the goal. Select the BEST answer.
---

• A.Perform a full database backup before the changes are deployed. If the deployment fails, restore the database to another server and recover the original objects from the restored database.
• B.Create a database snapshot. If the deployment fails, revert the database to the database snapshot.
• C.Create a database snapshot. If the deployment fails, recover the objects from the database snapshot.
• D.Perform a copy-only database backup before the changes are deployed. If the deployment fails, restore the database to another server and recover the original objects from the restored database.

You have a text file that contains an XML Schema Definition (XSD). You have a table named Schema1.Table1.
You have a stored procedure named Schema1.Proc1 that accepts an XML parameter named Param1.
You need to store validated XML data in Schema1.Table1. The solution must ensure that only valid XML data is accepted by Param1.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

• A.Declare Param1 var1 as type XML and associate the variable to the XML schema collection.
• B.Create an XML schema collection in the database from the text file.
• C.Use the modify method to insert the XML schema into each row of the XML column in Table1.
• D.Define an XML column in Table1 by using an XML schema collection.

You have an index for a table in a SQL Azure database. The database is used for Online Transaction Processing (OLTP).
You discover that many page splits occur when records are inserted or updated in the table.
You need to minimize the number of page splits.
What should you set from the index options?

• A.STATISTICS_NORECOMPUTE = OFF
• B.FILLFACTOR = 80
• C.STATISTICS_NORECOMPUTE = ON
• D.FILLFACTOR = 0

DRAG DROP
You administer a SQL Server 2014 instance.
The server is capable of 10000 IO/second (IOPS). During the time period when the second process executes, the disk IO can reach 7000 IOPS, and CPU use can average 30% over the eight processors.
The first process summarizes the day's activity executed by a login of [SummaryReportLogin]. The second process submits transactions executed by a login of [ETLLogin].
A Resource Governor classifier function has been created to return WG_Low for connections from the [ETLLogin] and [SummaryReportLogin].
You need to set up the Resource Group and Workgroup Pools on the instance.
You have the following requirements:
- Both processes must never use more than 50 percent of the CPU at any one time. - The number of active queries that these processes can execute simultaneously should be limited to a maximum of 10. - The SummaryReportLogin process must always achieve the minimum IOPS required to be minimally affected during executing the ETLLogin processes.
Develop the solution by selecting and arranging the required code blocks in the correct order.
You may not need all of the code blocks.

Correct Answer: