Q1. You have a SQL Server 2012 environment that contains two servers. The servers are configured as shown in the following table. 

After the failover is complete, a user receives the following error message when connecting to DB1 on Server2: 

"Msg 916, Level 14, State 1, Line 1 The server principal "Account1" is not able to access the database "DB1" under the current security context." 

You verify that there is a server login for Account1 on Server2. 

You need to ensure that Account1 can connect to DB1. 

What should you do? 

More than one answer choice may achieve the goal. Select the BEST answer. 

A. Update the SID for Account1 on DB1. 

B. Add Account1 to the db_datareader role. 

C. Create a new database user on DB1. 

D. Implement Windows authentication. 

View Answer

Q2. You have a database that contains a table named Department. Department contains the names and locations of each department. 

You have a table-valued function named ProjectList() that returns a list of all the projects assigned to a department. The name of the department is passed as an argument to the ProjectList() function. 

You need to create a query that returns a list of all the department names and the project names. The solution must return only departments that are associated to projects. 

What should you add to the query? 

A. OUTER APPLY 

B. OUTER JOIN 

C. CROSS JOIN 

D. CROSS APPLY 

View Answer

Q3. You need to implement a solution that meets the site requirements. 

What should you implement? 

A. A non-indexed view on Server1 

B. A non-indexed view on Server2 

C. A distributed view on Server1 

D. A distributed view on Server2 

View Answer

Q4. You need to recommend a solution to ensure that SQL1 supports the auditing requirements of usp_UpdateEmployeeName. 

What should you include in the recommendation? 

A. Change data capture 

B. Change tracking 

C. Transactional replication 

D. The Distributed Transaction Coordinator (DTC) 

View Answer

Q5. You execute the following code: The execution plan for the query is shown in the exhibit. (Click the Exhibit button.) 

You need to prevent the key lookup. What should you modify? 

More than one answer choice may achieve the goal. Select the BEST answer. 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

View Answer

Q6. You have a SQL Server 2012 database named Database1. Database1 contains a table named OrderDetails. 

For a given sales order, you need to retrieve the OrderID, Quantity, and LineTotal columns for all of the items in the OrderDetails table. The solution must ensure that the results can be joined to other tables. 

Which code segment should you execute? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

View Answer

Q7. You plan to design an application that temporarily stores data in a SQL Azure database. 

You need to identify which types of database objects can be used to store data for the application. The solution must ensure that the application can make changes to the schema of a temporary object during a session. 

Which type of objects should you identify? 

A. Common table expressions (CTEs) 

B. Temporary stored procedures 

C. Temporary tables 

D. Table variables 

View Answer

Q8. Your company has a main office in London and a branch office in New York. 

Your network contains a server named Server5 that has SQL Server 2012 installed. Server5 contains a database name ContentDB and a table named ContentTable. 

You add an additional server named Server9 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.) 

A. Create the view on Server5. 

B. Add Server9 as a linked server. 

C. Create the view on Server9. 

D. Add the Customers table to Server9. 

E. Add Server9 as a Distributor. 

F. Remove the Customers table from Server5. 

View Answer

Q9. Topic 8) 

You plan to deploy two stored procedures named SP1 and SP2 that read data from the database. 

Your company identifies the following requirements for each stored procedure: 

. SP1 must allow dirty reads. 

. SP2 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.) 

View Answer

Q10. Topic 8) 

Your network contains a SQL Server 2012 instance named SQL1. SQL1 contains a database named DB1. DB1 contains three tables. 

The tables are configured as shown in the following table. 

You plan to create indexes for the tables. 

You need to identify which type of index must be created for each table. The solution must minimize the amount of time required to return information from the tables. 

Which type of index should you create for each table? To answer, drag the appropriate index type to the correct table in the answer area. 

View Answer