Certification Exam Prep Questions For Designing a Data Solution with Microsoft SQL Server (MS-20465)
QuickStart is now offering assessment questions for Designing a Data Solution with Microsoft SQL Server (MS-20465). Whether you are deciding which exam to sign up for, or simply want to practice the materials necessary to complete certification for this course, we have provided a practice assessment to better aid in certification. 100% of the questions are real questions; from a recent version of the test you will take for Designing a Data Solution with Microsoft SQL Server (MS-20465).
![Arrow](https://di3xp7dfi3cq.cloudfront.net/media/wysiwyg/infortis/ultimo/custom/icons/arrow-point-to-down-3.png)
![](https://di3xp7dfi3cq.cloudfront.net/media/wysiwyg/infortis/ultimo/custom/icons/module_1.png)
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.
-
Correct!
- B. Partition the Inventory table.snapshot replication
-
Incorrect.
- C. Create a column store index.Master Data Services
-
Incorrect.
- D. Drop the clustered index.change data capture
-
Incorrect.
You need to recommend a solution to allow application users to perform tables. The solution must meet the business requirements. What should you recommend?
- A. Create a Policy-Based Management Policy.
-
Incorrect.
- C. Create stored procedures that use EXECUTE AS clauses.
-
Incorrect.
- D. Create functions that use EXECUTE AS clauses.
-
Correct!
You need to recommend a feature to support your backup solution. What should you include in the recommendation?
- A. Transparent Data Encryption (TDE)
-
Correct!
- B. Column-level encryption
-
Incorrect.
- C. An NTFS file permission
-
Incorrect.
- D. A Secure Sockets Layer (SSL)
-
Incorrect.
You need to recommend a solution for Applicationl that meets the security requirements. What should you include in the recommendation?
- A. Signed stored procedures
-
Correct!
- B. Certificate Authentication
-
Incorrect.
- C. Encrypted columns
-
Incorrect.
- D. Secure Socket Layer (SSL)
-
Incorrect.
You need to recommend a disk monitoring solution that meets the business requirements. What should you include in the recommendation?
- A. An audit
-
Incorrect.
- B. A dynamic management view
-
Correct!
- C. A maintenance plan
-
Incorrect.
- D. An SQL ServerAgent alert
-
Incorrect.
You need to recommend a solution to improve the performance of usp.Update|nventory. The solution must minimize the amount of development effort. What should you include in the recommendation?
- A. A table variable
-
Correct!
- B. A common table expression
-
Incorrect.
- C. A subquery
-
Incorrect.
- D. A cursor
-
Incorrect.
You need to recommend a solution for the deployment of SQL Server 2014. The solution must meet the business requirements. What should you include in the recommendation?
- A. Create a new instance of SQL Server 2014 on the server that hosts the SQL Server 2008 instance.
-
Incorrect.
You need to recommend a solution to synchronize Database2 to Appl_Dbl. What should you recommend?
- A. Change data capture
-
Incorrect.
- B. Snapshot replication
-
Incorrect.
- C. Master Data Services
-
Incorrect.
- D. Transactional replication
-
Correct!
You need to recommend a database reporting solution that meets the business requirements. What should you include in the recommendation?
- A. Data collection
-
Correct!
- B. Performance Monitor
-
Incorrect.
- C. A maintenance plan
-
Incorrect.
- D. A dynamic management view
-
Incorrect.
You need to recommend an isolation level for usp_UpdateOrderDetails. Which isolation level should you recommend?
- A. Read committed
-
Incorrect.
- B. Repeatable read
-
Correct!
- C. Read uncommitted
-
Incorrect.
- D. Serializable
-
Incorrect.
You need to recommend a solution that resolves the missing data issue. The solution must minimize the amount of development effort. What should you recommend?
- A. Denormalize the Products table.
-
Incorrect.
- B. Denormalize the OrderDetails table.
-
Incorrect.
- C. Normalize the OrderDetails table.
-
Incorrect.
- D. Normalize the Products table.
-
Correct!
You need to recommend a solution that addresses the index fragmentation and index width issue.
What should you include in the recommendation? (Each correct answer presents part of the solution. Choose all that apply.
- C. Remove the modifiedBy column from the clustered index.
-
Incorrect.
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.
-
Incorrect.
- B. Reorganize the indexes.
-
Incorrect.
- C. Disable Resource Governor.
-
Correct!
- D. Enable Auto Update Statistics.
-
Incorrect.
You need to recommend a solution that meets the data recovery requirement. What should you include in the recommendation?
- A. A differential backup
-
Incorrect.
- B. A transaction log backup
-
Incorrect.
- C. Snapshot isolation
-
Incorrect.
- D. A database snapshot
-
Correct!
You need to recommend a solution that addresses the file storage requirements. What should you include in the recommendation?
- A. FileStream
-
Incorrect.
- B. FileTable
-
Correct!
- C. The varbinary data type
-
Incorrect.
- D. The image data type
-
Incorrect.
You need to recommend a solution that addresses the installation issues. What should you include in the recommendation?
- A. Windows logins
-
Incorrect.
- B. Server roles
-
Incorrect.
- C. Contained users
-
Correct!
- D. Database roles
-
Incorrect.
You need to recommend a solution that addresses the security requirement. What should you recommend?
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
-
Incorrect.
- B. The index on the ProductName column
-
Incorrect.
- C. The collation of the ProductName column
-
Correct!
- D. The data type of the ProductName column
-
Incorrect.
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
-
Incorrect.
- B. Filegroups
-
Correct!
- C. Table partitioning
-
Incorrect.
- D. Indexes
-
Incorrect.
What should you recommend for the updates to Sales.TransactionHistory?
- A. a REPEATABLE READ isolation level
-
Correct!
- B. implicit transactions
-
Incorrect.
- C. query hints
-
Incorrect.
- D. a SNAPSHOT isolation level
-
Incorrect.
- Home
- Practice Exam - Designing a Data Solution with Microsoft SQL
Practice Exam - Designing a Data Solution with Microsoft SQL
More Information:
- Learning Style: On Demand
- Learning Style: Practice Exam
- Difficulty: Beginner
- Course Duration: 1 Hour
- Course Info: Download PDF
- Certificate: See Sample
Contact a Learning Consultant
Need Training for 5 or More People?
Customized to your team's need:
- Annual Subscriptions
- Private Training
- Flexible Pricing
- Enterprise LMS
- Dedicated Customer Success Manager