[2021-New] Microsoft 70-762 Dumps With Update Exam Questions (21-30)

70-762 Dumps

70-762 Exam Questions - Online Test

70-762 Premium VCE File

Learn More 100% Pass Guarantee - Dumps Verified - Instant Download
150 Lectures, 20 Hours

Q1. 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:

Solution: You create a stored procedure that includes the following Transact-SQL segment:

Does the solution meet the goal?

A. Yes

B. No

Answer: B

Explanation:

References: http://stackoverflow.com/questions/11444923/stored-procedure-to-update- multiple-tables

Q2. Note: This question is part of a series of questions that present the same scenario. Each question in this series contains a unique solution. Determine whether the solution meets the stated goals.

Your company has employees in different regions around the world.

You need to create a database table that stores the following employee attendance information:

- Employee ID

- date and time employee checked in to work

- date and time employee checked out of work

Date and time information must be time zone aware and must not store fractional seconds. Solution: You run the following Transact-SQL statement:

Does the solution meet the goal?

A. Yes

B. No

Answer: B

Explanation:

datetime2 defines adate that is combined with a time of day that is based on 24-hour clock. datetime2 can be considered as an extension of the existing datetime type that has a larger date range, a larger default fractional precision, andoptional user-specified precision.

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

Q3. 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:

The application uses views to control access to data. Views must meet the following requirements:

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. Update the transaction level of the report query session to READPAST.

B. Modify the report queries to use the UNION statement to combine the results of two or more queries.

C. Set the READ_COMMITTED_SNAPSHOT database option to ON.

D. Update the transaction level of the report query session to READ UNCOMMITTED.

Answer: C

Explanation:

References: https://technet.microsoft.com/en-us/library/ms173763(v=sql.105).aspx

Q4. DRAG DROP

You are monitoring a Microsoft Azure SQL Database. The database is experiencing high CPU consumption.

You need to determine which query uses the most cumulative CPU.

How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment may be used once, more than one or not at all. You may need to drag the split bar between panes or scroll to view content.

Answer:

Explanation:

Box 1: sys.dm_exec_query_stats

sys.dm_exec_query_stats returns aggregateperformance statistics for cached query plans in SQL Server.

Box 2: highest_cpu_queries.total_worker_time DESC Sort ontotal_worker_time column

Example: The following example returns information about the top five queries ranked by average CPU time.

Thisexample aggregates the queries according to their query hash so that logically equivalentqueries are grouped by their cumulative resource consumption.

USE AdventureWorks2012; GO

SELECT TOP 5 query_stats.query_hash AS "Query Hash", SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",

MIN(query_stats.statement_text) AS "Statement Text" FROM

(SELECT QS.*,

SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,

((CASE statement_end_offset

WHEN -1 THEN DATALENGTH(ST.text)

ELSE QS.statement_end_offset END

- QS.statement_start_offset)/2) + 1) AS statement_text FROM sys.dm_exec_query_stats AS QS

CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle)as ST) as query_stats GROUP BY query_stats.query_hash

ORDER BY 2 DESC;

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

Q5. 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 database that contains a table named Employees. The table stored information about the employees of your company.

You need to implement the following auditing rules for the Employees table:

- Record any changes that are made to the data in the Employees table.

- Customize the data recorded by the audit operations.

Solution: You implement a stored procedure on the Employees table. Does the solution meet the goal?

A. Yes

B. No

Answer: B

Explanation:

We should use table-valued functions, not procedures, to customize the recorded change data.

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

Q6. Note: This question is part of a series of questions that present the same scenario. Each question in this series contains a unique solution. Determine whether the solution meets the stated goals.

The Account table was created using the following Transact-SQL statement:

There are more than 1 billion records in the Account table. The Account Number column uniquely identifies each account. The ProductCode column has 100 different values. The values are evenly distributed in the table. Table statistics are refreshed and up to date.

You frequently run the following Transact-SQL SELECT statements:

You must avoid table scans when you run the queries. You need to create one or more indexes for the table. Solution: You run the following Transact-SQL statement:

Does the solution meet the goal?

A. Yes

B. No

Answer: A

Explanation:

Create a clustered index on theAccountNumber column as it is unique. Create a nonclustered index that includes the ProductCode column.

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

Q7. You are developing an application that connects to a database.

The application runs the following jobs:

The READ_COMMITTED_SNAPSHOT database option is set to OFF, and auto-content is set to ON. Within the stored procedures, no explicit transactions are defined.

If JobB starts before JobA, it can finish in seconds. If JobA starts first, JobB takes a long time to complete.

You need to use Microsoft SQL Server Profiler to determine whether the blocking that you observe in JobB is caused by locks acquired by JobA.

Which trace event class in the Locks event category should you use?

A. LockAcquired

B. LockCancel

C. LockDeadlock

D. LockEscalation

Answer: A

Explanation:

The Lock:Acquiredevent class indicates that acquisition of a lock on a resource, such asa data page, has been achieved.

The Lock:Acquired and Lock:Released event classes can be used to monitor when objects are being locked, the typeof locks taken, and for how long the locks were retained. Locks retained for long periods of time may cause contention issues and should be investigated.

Q8. Note: This question is part of a series of questions that present the same scenario. Each question in this series contains a unique solution. Determine whether the solution meets the stated goals.

You are developing a new application that uses a stored procedure. The stored procedure inserts thousands of records as a single batch into the Employees table.

Users report that the application response time has worsened since the stored procedure was updated. You examine disk-related performance counters for the Microsoft SQL Server instance and observe several high values that include a disk performance issue. You examine wait statistics and observe an unusually high WRITELOG value.

You need to improve the application response time.

Solution: You update the application to use implicit transactions when connecting to the database.

Does the solution meet the goal?

A. Yes

B. No

Answer: B

Explanation:

References: http://sqltouch.blogspot.co.za/2013/05/writelog-waittype-implicit-vs- explicit.html

Q9. You have a database that is experiencing deadlock issues when users run queries. You need to ensure that all deadlocks are recorded in XML format.

What should you do?

A. Create a Microsoft SQL Server Integration Services package that uses sys.dm_tran_locks.

B. Enable trace flag 1224 by using the Database Cpmsistency Checker(BDCC).

C. Enable trace flag 1222 in thestartup options for Microsoft SQL Server.

D. Use the Microsoft SQL Server Profiler Lock:Deadlock event class.

Answer: C

Explanation:

When deadlocks occur, trace flag 1204 and trace flag 1222 return information that is capturedin the SQL Server error log.Trace flag 1204 reports deadlock information formatted by each node involved in the deadlock. Trace flag 1222 formats deadlock information, first by processes and then by resources.

The output format for Trace Flag 1222 only returns information in an XML-like format. References:https://technet.microsoft.com/en-us/library/ms178104(v=sql.105).aspx

Q10. HOTSPOT

Background

You have a database named HR1 that includes a table named Employee.

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:

The application uses views to control access to data. Views must meet the following requirements:

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.

Answer:

Explanation:

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