Q1. You have two databases named DB1 and DB2 that are located on the same server.
You plan to create a stored procedure named SProc1 in DB1.
SProc1 will query a table named Table2 in DB2.
You need to recommend a solution to ensure that SProc1 can access Table2 without granting users direct access to Table2.
What should you include in the recommendation?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Contained databases
B. Application roles
C. Cross-database ownership chaining
D. Digital certificates
Answer: B
Q2. You need to provide referential integrity between the Sessions table and Speakers table. Which code segment should you add at line 47 of Tables.sql?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: B
Q3. You are designing a monitoring application for a new SQL Server 2014 instance.
You need to recommend a solution to generate a report that displays the 10 most frequent wait types that occur for the instance.
What should you include in the recommendation?
More than one answer choice may achieve the goal. Select the BEST answer.
A. The SQL Server error log
B. The sys.dm_os_wait_stats dynamic management view
C. The DBCC SQLPERF(WAITSTATS) command
D. SQL Server Profiler
Answer: B
Q4. You need to prepare the database to use the .NET Framework ProcessProducts component.
Which code segments should you execute? (Each correct answer presents part of the solution. Choose all that apply.)
A. Option A
B. Option B
C. Option C
D. Option D
E. Option E
F. Option F
G. Option G
Answer: ACDE
Q5. You need to recommend a solution that addresses the backup issue. The solution must minimize the amount of development effort.
What should you include in the recommendation?
A. Indexed views
B. Filegroups
C. Table partitioning
D. Indexes
Answer: B
Q6. You need to recommend a solution that reduces the time it takes to import the supplier data.
What should you include in the recommendation?
A. Enable instant file initialization.
B. Reorganize the indexes.
C. Disable Resource Governor.
D. Enable Auto Update Statistics.
Answer: C
Q7. 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
Answer: A
Q8. You need to recommend an isolation level for usp_UpdateOrderDetails.
Which isolation level should recommend?
A. Read committed
B. Repeatable read
C. Read uncommitted
D. Serializable
Answer: B
Q9. You need to recommend a solution that addresses the installation issues.
What should you include in the recommendation?
A. Windows logins
B. Server roles
C. Contained users
D. Database roles
Answer: C
Q10. 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. FILLFACTOR = 0
B. STATISTICS_NORECOMPUTE = OFF
C. STATISTICS_NORECOMPUTE = ON
D. FILLFACTOR = 80
Answer: D