Free Microsoft 070-762 Exam Dumps Questions & Answers

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

Correct Answer:

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 the series.
You have a database named Sales that contains the following database tables. Customer, Order, and Products.
The Products table and the order table shown in the following diagram.

The Customer table includes a column that stores the date 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 begin to modify the table design to adhere to third normal form.
Which column should you remove for each table? To answer? drag the appropriate column names to the correct locations. Each column name 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.

Correct Answer:

Explanation

In the Products table the SupplierName is dependent on the SupplierID, not on the ProductID.
In the Orders table the ProductName is dependent on the ProductID, not on the OrderID.
Note:
A table is in third normal form when the following conditions are met:
* It is in second normal form.
* All non-primary fields are dependent on the primary key.
Second normal form states that it should meet all the rules for First 1Normnal Form and there must be no partial dependencies of any of the columns on the primary key.
First normal form (1NF) sets the very basic rules for an organized database:
* Define the data items required, because they become the columns in a table. Place related data items in a table.
* Ensure that there are no repeating groups of data.
* Ensure that there is a primary key.
References: https://www.tutorialspoint.com/sql/third-normal-form.htm

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

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

Background
You have a database named HR1 that includes a table named Employee.
You have several read-only, historical reports that contain regularly changing totals. The reports use multiple queries to estimate payroll expenses. The queries run concurrently. Users report that the payroll estimate reports do not always run. You must monitor the database to identify issues that prevent the reports from running.
You plan to deploy the application to a database server that supports other applications. You must minimize the amount of storage that the database requires.
Employee Table
You use the following Transact-SQL statements to create, configure, and populate the Employee table:

Application
You have an application that updates the Employees table. The application calls the following stored procedures simultaneously and asynchronously:
- UspA: This stored procedure updates only the EmployeeStatus column.
- UspB: This stored procedure updates only the EmployeePayRate column.
The application uses views to control access to data. Views must meet the following requirements:
- Allow user access to all columns in the tables that the view accesses.
- Restrict updates to only the rows that the view returns.
Exhibit

You are analyzing the performance of the database environment. You discover that locks that are held for a long period of time as the reports are generated.
You need to generate the reports more quickly. The database must not use additional resources.
What should you do?

• A.Modify the report queries to use the UNION statement to combine the results of two or more queries.
• B.Update the transaction level of the report query session to READPAST.
• C.Set the READ_COMMITTED_SNAPSHOT database option to ON.
• D.Update the transaction level of the report query session to READ UNCOMMITTED.

Note: This question is part of a series of questions that use the same or similar answer choices. As answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series.
Information and details provided in a question apply only to that question.
You have a Microsoft SQL Server database named DB1 that contains the following tables:

Users frequently run the following query:

Users report that the query takes a long time to return results.
You need to minimize the amount of time requires for the query to return data.
What should you do?

• A.Drop existing indexes on tbli and then create a clustered columnstore index. Create a nonclustered columnstore index on tbli. Make no changes to TBL2.
• B.Create a clustered index on TBL1. Create a nonclustered index on TBL2 and add the most frequently queried columns as included columns.
• C.Create an indexed view that combines columns from TBL1 and TBL2.
• D.Create unique constraints on both TBL1 and TBL2. Create a partitioned view that combines columns from TBL1 and TBL2.
• E.Create clustered indexes on TBL1 and TBL2.
• F.Create CHECK constraints on both TBL1 and TBL2.. Create a partitioned view that combines columns fromTBL1 and TBL2.
• G.Drop existing indexes on TBL1 and then create a clustered columnstore index. Create a nonclustered columnstore index on TBL1. Create a nonclustered index on TBL2.
• H.Create a nonclustered index on TBL2 only.