Microsoft 070-762 : Developing SQL Databases

Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.
You have a database named Sales that contains the following database tables: Customer, Order, and Products.
The Products table and the Order table are shown in the following diagram.

The customer table includes a column that stores the data for the last order that the customer placed.
You plan to create a table named Leads. The Leads table is expected to contain approximately 20,000 records.
Storage requirements for the Leads table must be minimized.
You need to modify the database design to meet the following requirements:
* Rows in the Orders table must always have a valid value for the ProductID column.
* Rows in the Products table must not be deleted if they are part of any rows in the Orders table.
* All rows in both tables must be unique.
In the table below, identify the constraint that must be configured for each table.
NOTE: Make only one selection in each column.

You have a database with multiple tables. You must insert data into the tables by using views.
Which of the following two statements best describes an updatable view?
NOTE: Each correct selection is worth one point.

• A. Each view must reference columns from one table.
• B. The view may include GROUP BY or HAVING statements.
• C. The view must not include subqueries.
• D. The view may include a composite field.
• E. The view must not include the primary key of the table.

Note: This question is part of a series of questions that use the same or similar answer choices. An Answer choice may be correct for more than one question in the series. Each question independent of the other questions in this series. Information and details provided in a question apply only to that question.
You are a database developer for a company. The company has a server that has multiple physical disks. The disks are not part of a RAID array. The server hosts three Microsoft SQL Server instances. There are many SQL jobs that run during off-peak hours.
You observe that many deadlocks appear to be happening during specific times of the day.
You need to monitor the SQL environment and capture the information about the processes that are causing the deadlocks.
What should you do?

• A. Create a sys.dm_os_memory_objects query.
• B. Create a sp_configure 'max server memory' query.
• C. Create an Extended Event.
• D. Create a sys.dm_os_wait_stats query.
• E. Create a sys.dm_exec_sessions query.
• F. Create a SQL Profiler trace.
• G. Create a sys.dm_os_waiting_tasks query.
• H. Create a PerformanceMonitor Data Collector Set.

Note: This question is part of a series of questions that use the same or similar answer choices. An Answer choice may be correct for more than one question in the series. Each question independent of the other questions in this series. Information and details provided in a question apply only to that question.
You are a database developer for a company. The company has a server that has multiple physical disks. The disks are not part of a RAID array. The server hosts three Microsoft SQL Server instances. There are many SQL jobs that run during off-peak hours.
You must monitor the SQL Server instances in real time and optimize the server to maximize throughput, response time, and overall SQL performance.
You need to ensure that the performance of each instance is consistent for the same queried and query plans.
What should you do?

• A. Create aSQL Profiler trace.
• B. Create a sys.dm_os_memory_objects query.
• C. Create a sp_configure 'max server memory' query.
• D. Create a Performance Monitor Data Collector Set.
• E. Create an Extended Event.
• F. Create a sys.dm_exec_sessions query.
• G. Create asys.dm_os_wait_stats query.
• H. Create a sys.dm_os_waiting_tasks query.

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 MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON option for the database
• B. the SET TRANSACTION_READ_COMMITTED isolation level for the connection
• C. the SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=OFF option for the database
• D. the SERIALIZABLE table hint on disk-based tables