Free Microsoft 70-762 Exam Dumps Questions & Answers

You have multiple stored procedures inside a transaction.
You need to ensure that all the data modified by the transaction is rolled back if a stored procedure causes a deadlock or times out.
What should you do?

• A.  Use the max worker threads Option.
• B.  Set SET ALLOW SNAPSHOT ISOLATION to ON,
• C.  Use the output parameters.
• D.  Use a table-valued parameter.
• E.  Execute the DBCC UPDATEUSAGE statement.
• F.  Set SET XACT ABORT to ON.
• G.  Execute the alter table T1 set (lock escalation = auto); statement.
• H.  Use the NOLOCK option.

You have a table named Person.Address that includes the following columns:
* AddressID
* AddressLine1
* AddressLine2
* City
* StateProvinceID
* PostakCode
* RowGuid
* ModifiedDate
You need to create a nonclustered index on PostalCode named IX_Address_PostalCode that uses the following included columns:
* AddressLine1
* AddressLine2
* City
* StateProvinceID
How should you complete the Transact-SQL statement? To answer, select the appropriate Transact_SQL segments in the answer are.

Correct Answer:

Explanation

Box 1: INDEX
Box 2: ON
Box 3: INCLUDE
INCLUDE (column [ ,... n ] ) specifies the non-key columns to be added to the leaf level of the nonclustered index. The nonclustered index can be unique or non-unique.
References:
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql?view=sql-server-2017

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some questions sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a database that is 130 GB and contains 500 million rows of data.
Granular transactions and mass batch data imports change the database frequently throughout the day.
Microsoft SQL Server Reporting Services (SSRS) uses the database to generate various reports by using several filters.
You discover that some reports time out before they complete.
You need to reduce the likelihood that the reports will time out.
Solution: You partition the largest tables.
Does this meet the goal?

• A.  No
• B.  Yes

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

Users must only be able to modify data in the Employee table by using the vwEmployee view. You must prevent users from viewing the view definition in catalog views.
You need to identify the view attribute to use when creating vwEmployee.
In the table below, identify the attributes that you must use.
NOTE: Make only one selection in each column.

Correct Answer:

Explanation

References: https://msdn.microsoft.com/en-us/library/ms187956.aspx

You have a Microsoft Azure SQL Database. You enable Query Store for the database and configure the store to use the following settings:
SIZE_BASE_CLEANUP_MODE= OFF
STATE_QUERY_THRESHOLD_DAYS =60
MAX_STORAGE_SIZE= 100
QURRY_CAPTURE _MODE =ALL
You use Azure performance insight to receive queries. You observer that new queries are not displayed after
15 days and that the QUEY Store us set to read-only mode.
If the Query Store runs low on data pace, the store must prioritize queries from the regularly or queries that consume significant resources.
You set the Query Store to read_write mode and determine the performance of queries from the past 60 days.
Which three statements should you perform? Each correct step presents part of the solution.
NOTE: Each selection is worth one point.

• A.  Set the value of the CLEANUO_POLICY setting to (STALE_QUERY_THRESHOLD=75)
• B.  Increase the value for the MAX_STORAGE_size _MB setting.
• C.  In the Azure portal navigate to Query performance insight. Use the Custom tab to select a period of 2 months.
• D.  Set the value of the QUERY_CAPTUE_MODE setting to AUTO.
• E.  Set the value of the SIZE_BASE_CLEANUP_MODE settings to AUTO.