Microsoft 070-762 Value Pack (Frequently Bought Together)

You create a database table named FactSales by running the following Transact-SQL statements:

You must optimize the indexes without making changes to the ix_FactSales_EmployeeKey index.
You need to implement a columnstore index for the table.
How should you complete the Transact-SQL code? To answer, select the appropriate Transact-SQL segments in the answer area.
NOTE: Each correct selection is worth one point.

You use Query Store to optimize a query in a database.
The query has two execution plans:
* Plan 2 is shown in the Plan 2 Execution Plan exhibit.
* Plan 10 is shown in the Plan 10 Execution Plan exhibit.
You create an index at 22:24 based on the missing index suggestion in Plan 2.
The average duration statistics for the query is shown in the Tracked Queries exhibit. (Click the Exhibit button.) You need to analyze the operators in the two execution plans.
For each of the following statements, select Yes if the statement is true. Otherwise, select No.
Plan 2 Execution Plan

Plan 10 Execution Plan

Tracked Queries

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:
- Return a value of 0 if data is inserted successfully into the Customers table.
- Return a value of 1 if data is not inserted successfully into the Customers table.
- Support logic that is written by using managed code.
- Support TRY...CATCH error handling.
What should you create?

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

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.

• A. Add a PRINT statement.
• B. Replace NULL values with 0. Add a PRINT statement to return @Status.
• C. Replace NULL values with 0. Add an output parameter to return @Status.
• D. Add a RETURN statement.
• E. Replace NULL values with 0. Add a SELECT statement to return @Status.
• F. Add a SELECT statement to return @Status.
• G. Add an output parameter to return @Status.

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 os independent of the other questions in this series. Information and details provided in a question apply only to that question.
You have a database named DB1. There is no memory-optimized filegroup in the database.
You run the following query:

The following image displays the execution plan the query optimizer generates for this query:

Users frequently run the same query with different values for the local variable @lastName. The table named Person is persisted on disk.
You need to create an index on the Person.Person table that meets the following requirements:
- All users must be able to benefit from the index.
- FirstName must be added to the index as an included column.
What should you do?

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