Microsoft Transition Your MCITP: Database Administrator 2008 or MCITP: Database Developer 2008 to MCSE: Data Platform - 70-459 Exam Questions

QUESTION NO: 1
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?

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

QUESTION NO: 3
An administrator provides a digital certificate named ServerCert.
You need to implement Transparent Data Encryption (TDE) on ProductsDB.
Which code segment should you use?

QUESTION NO: 4
-----
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?

QUESTION NO: 5
DRAG DROP
You are the senior database administrator at Contoso, Ltd. You manage a SQL Server 2014 Instance, with multiple databases used for reporting.
You have recently hired a junior database administrator. You want this person to be able to view the database structures on the server, but you do not want him or her to be able to make changes or see the data in the tables.
The new hire's login credentials are as follows:
Login name: JFree
Password: Jx672$qse
You want the new hire to be required to change his password on his next login.
The code that is produced should execute no matter the initial database context in which it is started.
You need to write the code required to give the new hire only the desired access, using the smallest number of steps. Develop the solution by selecting and arranging the required code blocks in the correct order. You may not need all of the code blocks.

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

QUESTION NO: 7
DRAG DROP
You plan to deploy SQL Server 2012.
Your company identifies the following monitoring requirements for the database: - An e-mail message must be sent if the SQL Server Authentication mode changes.
- An e-mail message must be sent if CPU utilization exceeds 90 percent. You need to identify which feature meets each monitoring requirement. Which features should you identify?
To answer, drag the appropriate feature to the correct monitoring requirement in the answer area.

QUESTION NO: 8
You are creating a table to support an application that will cache data outside of SQL
Server.
The application will detect whether cached values were changed before it updates the
values.
You need to create the table, and then verify that you can insert a row into the table. Which code segment should you use?

QUESTION NO: 9
You need to modify the function in CountryFromID.sql to ensure that the country name is returned instead of the country ID.
Which line of code should you modify in CountryFromID.sql?