Microsoft 70-464 Exam Questions Instant Access

Question 1

You need to implement a solution that meets the data recovery requirements.

You update each stored procedure to accept a parameter named @transactionID.

What should you add next to the beginning of each stored procedure?

ASAVE TRANSACTION WITH MARK @transactionID

BROLLBACK DISTRIBUTED TRANSACTION @transactionID

CBEGIN TRANSACTION WITH MARK @transactionID

DCOMMIT TRANSACTION @transactionID

Answer : C

Question 2

You plan to modify a stored procedure to use temporary data.

The stored procedure must meet the following requirements:

Favor physical memory when physical memory is available.

Be able to roll back changes to the temporary data.

You need to recommend which object to add to the stored procedure.

Which T-SQL command should you recommend?

ACREATE TABLE ##Table...

BCREATE TABLE Table...

CCREATE VIEW Table...

DCREATE PARTITION SCHEME Table...

EDECLARE TABLE @ Table...

Answer : A

Temporary Tables

You can create local and global temporary tables. Local temporary tables are visible only in the current session, and global temporary tables are visible to all sessions. Temporary tables cannot be partitioned.

Prefix local temporary table names with single number sign (#table_name), and prefix global temporary table names with a double number sign (##table_name)

Question 3

You execute the following code:

You have a stored procedure that includes the following SELECT statement:

You need to create a covering index on UserInfo.

Which code segment should you execute?

AOption A

BOption B

COption C

DOption D

Answer : C

Question 4

You need to implement a solution that addresses the performance issues of the usp_GetOrdersByProduct stored procedure.

Which statement should you execute?

AOption A

BOption B

COption C

DOption D

Answer : C

Question 5

You have a SQL Server 2012 database named Database1. Database1 contains a table named OrderDetails.

For a given sales order, you need to retrieve the OrderID, Quantity, and LineTotal columns for all of the items in the OrderDetails table. The solution must ensure that the results can be joined to other tables.

Which code segment should you execute?

AOption A

BOption B

COption C

DOption D

Answer : A

Question 6

You need to implement a solution that addresses the index monitoring requirements.

What should you do?

ASchedule a SQL Server Agent job that saves data from the dynamic management views to a table in the database.

BCreate a SQL Server Audit that saves data to a log file, and then create a SQL Server Audit Specification that gathers data from the DATABASE_OPERATION group.

CCreate a performance monitor Data Collector Set (DCS) that monitors the SQL Server counters.

DSchedule a SQL Server Profiler trace, and then save the trace data to a table in the database.

Answer : A

Question 7

You need to modify usp.GetOrdersAndItems to ensure that an order is NOT retrieved by usp_GetOrdersAndItems while the order is being updated.

What should you add to usp.GetOrdersAndItems?

AAdd WITH (NOLOCK) to the end of line 47.

BAdd SET TRANSACTION ISOLATION LEVEL READ COMMITTED to line 44.

CAdd SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED to line 44.

DAdd WITH (READPAST) to the end of line 47.

Answer : B

The SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement specifies that no other statement can read data that has been modified but not yet committed by other transactions.

References:

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-2017

Microsoft Developing Microsoft SQL Server Databases 70-464 Exam Questions