The purpose of this Sample Question Set is to provide you with information about the Microsoft Developing SQL Databases exam. These sample questions will make you very familiar with both the type and the difficulty level of the questions on the 70-762 certification test. To get familiar with real exam environment, we suggest you try our Sample Microsoft Developing SQL Databases Certification Practice Exam. This sample practice exam gives you the feeling of reality and is a clue to the questions asked in the actual Microsoft Certified Solutions Associate (MCSA) - SQL 2016 Database Development certification exam.
These sample questions are simple and basic questions that represent likeness to the real Microsoft 70-762 exam questions. To assess your readiness and performance with real time scenario based questions, we suggest you prepare with our Premium Microsoft Developing SQL Databases Certification Practice Exam. When you solve real time scenario based questions practically, you come across many difficulties that give you an opportunity to improve.
Microsoft 70-762 Sample Questions:
05. You are designing a solution for a company that operates retail stores. Each store has a database that tracks sales transactions. You create a summary table in the database at the corporate office. You plan to use the table to record the quantity of each product sold at each store on each day. Managers will use this data to identify reorder levels for products.
Every evening stores, must transmit sales data to the corporate office. The data must be inserted into the summary table that includes the StoreID, ProductID, Qtysold, Totprodsales, and Datesold columns.
You need to prevent duplicate rows in the summary table. Each row must uniquely identify the store that sold the product and the total amount sold for that store on a specific date. What should you include in your solution?
a) Create a unique constraint.
b) Create a foreign key constraint to the StoreID column in each of the store tables.
c) Create a rule and bind it to the StoreID column.
d) Create a check constraint.
07. You are creating the following two stored procedures:
- A natively-compiled stored procedure
- An interpreted stored procedure that accesses both disk-based and memory-optimized tables
Both stored procedures run within transactions. You need to ensure that cross-container transactions are possible. Which setting or option should you use?
a) the SET TRANSACTION_READ_COMMITTED isolation level for the connection
b) the SERIALIZABLE table hint on disk-based tables
c) the SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON option for the database
d) the SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=OFF option for the database
08. You have a database that is experiencing deadlock issues when users run queries. You need to ensure that all deadlocks are recorded in XML format. What should you do?
a) Create a Microsoft SQL Server Integration Services package that uses sys.dm_tran_locks.
b) Enable trace flag 1224 by using the Database Cpmsistency Checker(BDCC).
c) Enable trace flag 1222 in the startup options for Microsoft SQL Server.
d) Use the Microsoft SQL Server Profiler Lock: Deadlock event class.
09. You suspect deadlocks on a database. Which two trace flags in the Microsoft SQL Server error log should you locate?
10. You are developing a database reporting solution for a table that contains 900 million rows and is 103 GB. The table is updated thousands of times a day, but data is not deleted.
The SELECT statements vary in the number of columns used and the amount of rows retrieved. You need to reduce the amount of time it takes to retrieve data from the table. The must prevent data duplication.
Which indexing strategy should you use?
a) a clustered columnstore index for the table
b) a hash index for the table
c) a clustered index for the table and nonclustered indexes for nonkey columns
d) a nonclustered index for each column in the table
Answer: a, c
Note: For any error in Microsoft Certified Solutions Associate (MCSA) - SQL 2016 Database Development (70-762) certification exam sample questions, please update us by writing an email on email@example.com.