Microsoft Recertification for MCSE: Data Platform - 070-469 FREE EXAM DUMPS QUESTIONS & ANSWERS

You need to modify usp.GetOrdersAndItems to ensure that an order is NOT retrieved by usp_GetOrdersAndItems while the order is being updated.
What should you add to usp.GetOrdersAndItems?

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?

You have a SQL Server 2014 environment That includes four servers. The servers are configured as shown in the following table.

You plan to configure Policy-Based Management to enforce the following rules:
- On Server1, enable SQL Server password policies and enable the default trace. - On Server3, ensure that the names of user-defined stored procedures begin with the prefix "usp_" and ensure that all databases use a case-sensitive collation.
You need to recommend which server you must configure as a Central Management Server.
Which server should you recommend? (Each correct answer presents a complete solution. Choose all that apply.)

You execute IndexManagement.sql and you receive the following error message: "Msg 512, Level 16, State 1, Line 12
Subquery returned more than 1 value. This is not permitted when the subquery follows =,!
=, <, <= ,>, > = or when the subquery is used as an expression."
You need to ensure that IndexManagement.sql executes properly.
Which WHILE statement should you use at line 18?

You have a Microsoft SQL Azure database. You have the following stored procedure:

You discover that the stored procedure periodically fails to update Person.Contact.
You need to ensure that Person.Contact is always updated when UpdateContact executes. The solution must minimize the amount of time required for the stored procedure to execute and the number of locks held.
What should you do?

DRAG DROP
You run the following code segment:

After you add 10,000 rows to Customers, you discover that the index is fragmented.
You need to defragment the index in the least amount of time.
Which code segment should you execute?
To answer, drag the appropriate value to the correct location in the code segment in the
answer area. (Answer choices may be used once, more than once, or not at all.)

----
You administer a SQL Server 2014 instance.
Users report that the SQL Server has seemed slow today. A large database was being restored for much of the day, which could be causing issues.
You want to write a query of the system views that will report the following:
Number of users that have a connection to the server
Whether a user's connection is active
Whether any connections are blocked
What queries are being executed
Whether the database restore is still executing and, if it is, what percentage of the restore is complete.
Which system objects should you use in your query to best achieve this task?

You need to monitor the health of your tables and indexes in order to implement the required index maintenance strategy.
What should you do?

DRAG DROP
You plan to deploy two stored procedures name USP_1 and USP_2 that read data from a database.
Your company identifies the following requirements for each stored procedure:
- USP_1 cannot allow dirty reads.
- USP_2 must place range locks on the data to ensure read consistency.
You need to identify which isolation level you must set for each stored procedure. The solution must minimize the number of locks.
Which isolation level should you identify?
To answer, drag the appropriate isolation level to the correct stored procedure in the answer area. (Answer choices may be used once, more than once, or not at all.)

You need to modify usp_SelectSpeakersByName to support server-side paging. The solution must minimize the amount of development effort required.
What should you add to usp_SelectSpeakersByName?

---
You have a SQL Server instance on a server named Server1.
You need to recommend a solution to perform the following tasks every week:
Rebuild the indexes by using a new fill factor.
Run a custom T-SQL command.
Back up the databases.
What should you recommend?
More than one answer choice may achieve the goal. Select the BEST answer.

DRAG DROP
You need to recommend a backup process for data warehouse database.
The solution must meet the following requirements:
- Ensure that if a hardware failure occurs, you can bring the database online without
losing more than 24 hours of transactions. - Minimize the amount of administrative effort required to restore any lost data. - Minimize the space used by transaction logs.
What should you include in the recommendation?
To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

Your network contains a server named SQL1 that has SQL Server 2012 installed. SQL1 contains
a database name DB1 and a table named Customers.
You add an additional server named SQL2 that runs SQL Server 2012.
You need to create a distributed partitioned view. The solution must minimize the amount of network traffic.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

DRAG DROP
You plan to deploy a database to SQL Azure.
You are designing two stored procedures named USP_1 and USP_2 that have the
following requirements: - Prevent data read by USP_1 from being modified by other active processes. - Allow USP_2 to perform dirty reads.
You need to recommend the isolation level for the stored procedures.
The solution must maximize concurrency.
Which isolation levels should you recommend?
To answer, drag the appropriate isolation level to the correct stored procedure in the
answer area.

You are designing your maintenance plan.
Which command should you use only during the monthly maintenance window?