Microsoft 70-762 Exam Questions Instant Access

Question 1

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 replace the stored procedure with a user-defined function.

Does the solution meet the goal?

AYes

BNo

Answer : B

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

Question 2

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 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 have a table and a stored procedure that were created by running the following Transact-SQL statements:

The Employee table is persisted on disk. You add 2,000 records to the Employee table.

You need to create an index that meets the following requirements:

- Optimizes the performance of the stored procedure.

- Covers all the columns required from the Employee table.

- Uses FirstName and LastName as included columns.

- Minimizes index storage size and index key size.

What should you do?

ACreate a clustered index on the table.

BCreate a nonclustered index on the table.

CCreate a nonclustered filtered index on the table.

DCreate a clustered columnstore index on the table.

ECreate a nonclustered columnstore index on the table.

FCreate a hash index on the table.

Answer : C

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

Question 3

Your company runs end-of-the-month accounting reports. While the reports run. other financial records are updated in the database.

Users report that the reports take longer than expected to run.

You need to reduce the amount of time it takes for the reports to run. The reports must show committed data only.

What should you do?

AUse the NOLOCK option.

BExecute the BDCC UPDATEUSAGE statement.

CUse the max worker threads Option.

DUse a table-valued parameter.

ESet SET ALLOW_SNAPSHOI_ISOLATION to OH.

FSet SET XACT ABORT to ON.

GExecute the alter table ti set (Lock_ESCALATION = AUTO); statement.

HUse the output parameters.

Answer : E

Snapshot isolation enhances concurrency for OLTP applications.

Once snapshot isolation is enabled, updated row versions for each transaction are maintained in tempdb. A unique transaction sequence number identifies each transaction, and these unique numbers are recorded for each row version. The transaction works with the most recent row versions having a sequence number before the sequence number of the transaction. Newer row versions created after the transaction has begun are ignored by the transaction.

References: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server

Question 4

Database users report that SELECT statements take a long time to return results. You run the following Transact-SQL statement:

You need to create one nonclustered covering index that contains all of the columns in the above table. You must minimize index key size.

Which Transact-SQL statement should you run?

ACREATE NONCLUSTERED INDEX IX_User ON Users (CountryCode, UserStatus, UserName);

BCREATE NONCLUSTERED INDEX IX_User ON Users (CountryCode, UserStatus) INCLUDE (UserName);

CCREATE NONCLUSTERED INDEX IX_User ON Users (CountryCode, UserName);

DCREATE NONCLUSTERED INDEX IX_User ON Users (UserStatus, CountryCode) INCLUDE (UserName);
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/create-indexes-with-included-columns

Answer : B

Question 5

You run the following Transact-SQL following statement:

Customer records may be inserted individually or in bulk from an application.

You observe that the application attempts to insert duplicate records.

You must ensure that duplicate records are not inserted and bulk insert operations continue without notifications.

Which Transact-SQL statement should you run?

ACREATE UNIQUE NONCLUSTERED INDEX IX_Customer_Code ON Customer (Code) WITH (ONLINE = OFF)

BCREATE UNIQUE INDEX IX_CUSTOMER_Code O Customer (Code) WITH (IGNORE_DUP_KEY = ON)

CCREATE UNIQUE INDEX IX Customer Code ON Customer (Code) WITH (IGNORE DUP KEY =OFF)

DCREATE UNIQUE NONCLUSTERED INDEX IX_Customer_Code ON Customer (Code)

ECREATE UNIQUE NONCLUSTERED INDEX IX_Customer_Code ON Customer (Code) WITH (ONLINE = ON)

Answer : B

IGNORE_DUP_KEY = { ON | OFF } specifies the error response when an insert operation attempts to insert duplicate key values into a unique index. The IGNORE_DUP_KEY option applies only to insert operations after the index is created or rebuilt. The option has no effect when executing CREATE INDEX, ALTER INDEX, or UPDATE. The default is OFF.

Question 6

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?

AYes

BNo

Answer : B

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

Question 7

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?

AYes

BNo

Answer : B

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

Microsoft Developing SQL Databases 70-762 Exam Questions