You need to recommend a solution to synchronize Database2 to App1_Db1. What should you recommend?
a) Change data capture
b) Snapshot replication
c) Master Data Services
d) Transactional replication
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
You need to recommend a solution to minimize the amount of time it takes to execute USP_1. With what should you recommend replacing Table1?
a) A view
b) A temporary table
c) A table variable
d) A function
Your network contains an Active Directory domain that has two groups named Group1 and Group2. The domain contains two SQL Server instances named SQLDev and SQLProd. Each SQL Server instance has access to various storage media.
The SQL Server instances have a database that contains a table named Table1. Table1 contains a column named Column1. The value for Column1 can be either Value1 or Value2. You need to recommend a solution to ensure that users in Group1 can retrieve only rows from Column1 that contain the value of Value1.
What should you recommend?
a) A dynamic management view
b) Filegroups
c) Snapshot isolation
d) User-defined views
You need to recommend a solution to minimize the amount of time it takes to execute USP_5. What should you include in the recommendation?
a) Enable cross-database chaining.
b) Use a server role to group all logins.
c) Use the EXECUTE AS clause in USP_5.
d) Copy USP.5 to each database.
You plan to create a database. The database will be used by a Microsoft .NET application for a special event that will last for two days. During the event, data must be highly available.
After the event, the database will be deleted. You need to recommend a solution to implement the database while minimizing costs. The solution must not affect any existing applications.
What should you recommend?
More than one answer choice may achieve the goal. Select the BEST answer.
a) SQL Server 2014 Enterprise
b) SQL Server 2014 Standard
c) SQL Azure
d) SQL Server 2014 Express with Advanced Services
You need to recommend an isolation level for usp_UpdateOrderDetails. Which isolation level should you recommend?
a) Read committed
b) Repeatable read
c) Read uncommitted
d) Serializable
During performance testing, you discover that database INSERT operations against the Inventory table are slow. You need to recommend a solution to reduce the amount of time it takes to complete the INSERT operations.
What should you recommend?
a) Partition the nonclustered index.
b) Partition the Inventory table.snapshot replication.
c) Create a column store index.Master Data Services.
d) Drop the clustered index.change data capture.
You need to recommend changes to the ERP application to resolve the search issue. The solution must minimize the impact on other queries generated from the ERP application.
What should you recommend changing?
a) The collation of the Products table
b) The index on the ProductName column
c) The collation of the ProductName column
d) The data type of the ProductName column
You need to recommend a solution to minimize the amount of time it takes to execute USP_2. What should you recommend?
a) A database snapshot
b) A table variable
c) A temporary table
d) Snapshot isolation
Here are the sample questions which will help you be familiar with Designing Database Solutions for Microsoft SQL Server (70-465) exam style and structure. We encourage you to try our Demo Designing Database Solutions for Microsoft SQL Server Certification Practice Exam to measure your understanding of exam structure in an environment which simulates the MCSE - Data Platform / Data Management and Analytics Certification test environment.