Microsoft Transition Your MCITP: Database Administrator 2008 or MCITP: Database Developer 2008 to MCSE: Data Platform Sample Questions:

1. 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.

2. DRAG DROP
You have a SQL Azure database named Database1.
You need to design the schema for a table named table1. Table1 will have less than one million rows. Table1 will contain the following information for each row:

The solution must minimize the amount of space used to store each row. Which data types should you recommend for each column?
To answer, drag the appropriate data type to the correct column in the answer area.

3. DRAG DROP
You need to implement a new version of usp_AddMobileLocation. Develop the solution by selecting and arranging the required code blocks in the correct order. You may not need all of the code blocks.

4. You have a server named Server1 that has 16 processors.
You plan to deploy multiple instances of SQL Server 2012 to Server1.
You need to recommend a method to allocate processors to each instance.
What should you include in the recommendation?
More than one answer choice may achieve the goal. Select the BEST answer.

A) Resource Governor
B) Processor affinity
C) Windows System Resource Manager (WSRM)
D) Max Degree of Parallelism

5. You have a SQL Server 2012 database named Database1. You execute the following code:

You insert 3 million rows into Sales.
You need to reduce the amount of time it takes to execute Proc1.
What should you do?

A) Change the WHERE clause to the following: WHERE OrderDate BETWEEN
CAST(@date1,char(10))
AND CAST(@date2,char(10))
B) Remove the ORDER BY clause from the stored procedure.
C) Run the following:
DROP INDEX IX_Sales_OrderDate;
GO
CREATE INDEX IX_Sales_OrderDate ON Sales(OrderDate);
GO
D) Run the following: ALTER TABLE Sales ALTER COLUMN OrderDate datetime NOT
NULL;

Solutions: