Online Access Free 70-762 Exam Questions

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 need to create a stored procedure that updates the Customer, CustomerInfo, OrderHeader, and OrderDetails tables in order.
You need to ensure that the stored procedure:
- Runs within a single transaction.
- Commits updates to the Customer and CustomerInfo tables regardless of the status of updates to the OrderHeader and OrderDetail tables.
- Commits changes to all four tables when updates to all four tables are successful.
Solution: You create a stored procedure that includes the following Transact-SQL segment:

Does the solution meet the goal?

• A.No
• B.Yes

You are a database developer for a company that delivers produce and other refrigerated goods to grocery stores. You capture the food storage temperature for delivery vehicles by using Internet of Things (IoT) devices. You store the temperature data in a database table named VehicleTemperatures. The ChillerSensorNumber column stores the identifier for the IoT devices.
You need to create an indexed view that meets the following requirements:
* Persists the data on disk to reduce the amount of I/O
* Provides the number of ChillerSensorNumber items
* Creates only a set of summary rows
How should you complete the view definition? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment may be used once, more than once, or not at all. You may need to drag the split bat between panes or scroll to view content.

Correct Answer:

Explanation

Box1: WITH SCHEMABINDING
Persists the data on disk to reduce the amount of I/O.
Box 2: COUNT_BIG(ChillerSensorNumber)
Provides the number of ChillerSensorNumber items
The function COUNT_BIG returns the number of items found in a group. COUNT_BIG operates like the COUNT function. These functions differ only in the data types of their return values. COUNT_BIG always returns a bigint data type value. COUNT always returns an int data type value.
Box 3: GROUP BY ChillerSensorNumber
Creates only a set of summary rows
References:
https://docs.microsoft.com/en-us/sql/t-sql/functions/count-big-transact-sql

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 an application to track customer sales.
You need to create a database object that meets the following requirements:
* Launch when table data is modified.
* Evaluate the state a table before and after a data modification and take action based on the difference.
* Prevent malicious or incorrect table data operations.
* Prevent changes that violate referential integrity by cancelling the attempted data modification.
* Run managed code packaged in an assembly that is created in the Microsoft.NET Framework and located into Microsoft SQL Server.
What should you create?

• A.table-valued function
• B.user-defined procedure
• C.DDL trigger
• D.extended procedure
• E.CLR procedure
• F.scalar-valued function

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?

• A.Create a nonclustered filtered index on the table.
• B.Create a hash index on the table.
• C.Create a clustered index on the table.
• D.Create a clusteredcolumnstore index on the table.
• E.Create a nonclustered columnstore index on the table.
• F.Create a nonclustered index on the table.

You have a nonpartitioned table that has a single dimension. The table is named dim. Products.Projections.
The table is queried frequently by several line-of-business applications. The data is updated frequently throughout the day by two processes.
Users report that when they query data from dim.Products.Projections, the responses are slower than expected.
The issue occurs when a large number of rows are being updated.
You need to prevent the updates from slowing down the queries.
What should you do?

• A.Set SET ALLOW_SNAPSHOT_ISOLATION to ON.
• B.Use the max worker threads Option.
• C.Execute the dbcc updateusage statement.
• D.Use a table-valued parameter.
• E.Use the nolock option.