Microsoft Developing SQL Databases - 070-762 Exam Practice Test

You have several real-time applications that constantly update data in a database. The applications run more than 400 transactions per second that insert and update new metrics from sensors.
A new web dashboard is released to present the data from the sensors. Engineers report that the applications take longer than expected to commit updates.
You need to change the dashboard queries to improve concurrency and to support reading uncommitted data.
What should you do?

You have a database named Database1.
Users report that they experience deadlock issues. You run the sp_readerlog stored procedure. You view the output from the Process List section as shown in the Process List exhibit. (Click the Process List tab.)

You view the contents of the Resource List section as shown in the Resource List exhibit. (Click the Resource List tab.)

You view deadlock information as shown in the Deadlock List exhibit. (Click the Deadlock List tab.)

You need to identify the causes of the deadlock.
For each of the following statements, select Yes if the statement is true. Otherwise, select No.

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets the stated goals.
You have a table that has a clustered index and a nonclustered index. The indexes use different columns from the table. You have a query named Query1 that uses the nonclustered index.
Users report that Query1 takes a long time to report results. You run Query1 and review the following statistics for an index seek operation:

You need to resolve the performance issue.
Solution: You defragment both indexes.
Does the solution meet the goal?

You have multiple queries that use time to complete.
You need to identify the cause by using detailed information about the Transact_SQL in the queries. The Transact_SQL statements must not run as part of the analysis.
Which Transact_SQL statements should you run?

You plan to create a stored procedure that uses a table parameter as an input parameter. The table value parameter may hold between 1 and 10,000 rows when you run the stored procedure.
The stored procedure will use the rows within the table value parameter to filter the rows that will be returned by the SELECT statement.
You need to create the stored procedure and ensure that it runs as quickly as possible.
How should you complete the procedure? To answer, select the appropriate Transact-SQL segments in the answer area.
NOTE: Each correct answer selection is worth one point.

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 is independent of the other questions in the series. Information and details provided in a question apply only to that question.
You are developing and application to track customer sales.
You need to create an object that meets the following requirements:
- Run managed code packaged in an assembly that was created in the Microsoft.NET Framework and uploaded in Microsoft SQL Server.
- Run written a transaction and roll back if a future occurs.
- Run when a table is created or modified.
What should you create?

Note: The 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 is independent of the other question in the series. Information and details provided in a question apply only to that question.
You have a database named DB1. The database does not use a memory-optimized filegroup. The database contains a table named Table1. The table must support the following workloads:

You need to add the most efficient index to support the new OLTP workload, while not deteriorating the existing Reporting query performance.
What should you do?

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.
The Leads table must include the columns described in the following table.

The data types chosen must consume the least amount of storage possible.
You need to select the appropriate data types for the Leads table.
In the table below, identify the data type that must be used for each table column.
NOTE: Make only one selection in each column.

You manage a Microsoft Azure SQL Database that has the Standard tier plan.
The database size has increased and users experience slow performance.
You need to identify usage for the following resources:
* CPU utilization
* Disk storage
* Memory utilization
* Disk I/O
......................................

You have two database tables. Table1 is a partitioned table and Table 2 is a non-partitioned table.
Users report that queries take a long time to complete. You monitor queries by using Microsoft SQL Server Profiler. You observe lock escalation for Table1 and Table 2.
You need to allow escalation of Table1 locks to the partition level and prevent all lock escalation for Table2.
Which Transact-SQL statement should you run for each table? To answer, drag the appropriate Transact-SQL statements to the correct tables. Each command may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

You have the following stored procedure that is called by other stored procedures and applications:

You need to modify the stored procedure to meet the following requirements:
* Always return a value to the caller.
* Return 0 if @Status is NULL.
* Callers must be able to use @Status as a variable.
Which two actions should you perform? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.