Microsoft 70-464 Exam Practice Test Instant Access

Question 1

You need to modify the usp_DetectSuspiciousActivity stored procedure.

Which two actions should you perform? Each correct answer presents part of the solution. Choose two.

AOption A

BOption B

COption C

DOption D

EOption E

FOption F

Answer : D, E

Note:

* Move micropayments to dbo.POSException table by using a stored procedure named ups_DetectSuspiciousActivity.

Question 2

You need to implement security for the restore and audit process. What should you do?

AGrant the COFFECORP\Auditors group ALTER ANY CONNECTION and SELECT ALL USER SECURABLES permissions. Grant the COFFECORP\StoreAgent group ALTER ANY CONNECTION and IMPERSONATE ANY LOGIN permissions.

BGrant the COFFECORP\Auditors group CONNECT ANY DATABASE and IMPERSONATE ANY LOGIN permissions. Grant the COFFECORP\StoreAgent group CONNECT ANY DATABASE and SELECT ALL USER SECURABLES permissions.

CGrant the COFFECORP\Auditors group ALTER ANY CONNECTION and IMPERSONATE ANY LOGIN permissions. Grant the COFFECORP\StoreAgent group ALTER ANY CONNECTION and SELECT ALL USER SECURABLES permissions.

DGrant the COFFECORP\Auditors group CONNECT ANY DATABASE and SELECT ALL USER SECURABLES permissions. Grant the COFFECORP\StoreAgent group CONNECT ANY DATABASE and IMPERSONATE ANY LOGIN permissions.

Answer : A

Question 3

You need to monitor the health of your tables and indexes in order to implement the required index maintenance strategy.

What should you do?

AQuery system DMVs to monitor avg_chain_length and max_chain_length. Create alerts to notify you when these values converge.

BCreate a SQL Agent alert when the File Table: Avg time per file I/O request value is increasing.

CQuery system DMVs to monitor total_bucket_count. Create alerts to notify you when this value increases.

DQuery system DMVs to monitor total_bucket_count. Create alerts to notify you when this value decreases.

Answer : A

From scenario:

* You need to anticipate when POSTransaction table will need index maintenance.

* The index maintenance strategy for the UserActivity table must provide the optimal structure for both maintainability and query performance.

Question 4

You need to ensure that a new execution plan is used by usp_GetOrdersByProduct each time the stored procedure runs.

What should you do?

AExecute sp_help usp_GetOrdersByProduct\

BAdd WITH (FORCESEEK) to line 69 in usp.GetOrdersByProduct.

CAdd WITH RECOMPILE to line 64 in usp.GetOrdersByProduct.

DExecute sp_recompile usp.GetOrdersByProduct'.

Answer : B

Question 5

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

Question 6

You need to ensure that a new execution plan is used by usp_GetOrdersByProduct each time the stored procedure runs.

What should you do?

AExecute sp_help 'usp_GetOrdersByProduct'.

BExecute sp_recompile 'usp_GetOrdersByProduct'.

CAdd WITH RECOMPILE to line 03 in usp_GetOrdersByProduct.

DAdd WITH (FORCESEEK) to line 07 in usp_GetOrdersByProduct.

Answer : C

Ref: http://msdn.microsoft.com/en-us/librAry/ms190439(v=sql.90).aspx

Question 7

You need to resolve the performance issues of the usp_getOpenings stored procedure.

Which three actions should you perform? Each correct answer presents part of the solution.

ADelete lines 05 through 08

BReplace lines 12, 13, and 14 with the Transact-SQL segment:
WHERE (CONTAINS(o.Description, 'ISABOUT(' +@keyword+' weight (.5))'))

CCreate a full text index on the Description column

DReplace lines 12, 13, and 14 with the Transact_SQL segment:
WHERE (CONTAINS(o.Description, @keyword))

EReplace lines 12, 13, and 14 with the Transact SQL Segment:
WHERE (Contains(o.Description, 'FORMSOF(INFLECTIONAL, '+@keyword+')'))

Answer : A, C, E

Scenario: You also discover that the usp_GetOpenings stored procedure takes a long time to run and that the non-clustered index on the Description column is not being used.

The FORMSOF term performs matches using other linguistic forms of the word. The following is the FORMSOF term syntax:

FORMSOF (<generation_type>,<match_words>)

The generation type specifies how Microsoft Windows Search chooses the alternative word forms. The INFLECTIONAL value chooses alternative inflection forms for the match words. If the word is a verb, alternative tenses are used. If the word is a noun, the singular, plural, and possessive forms are used to detect matches.

References:

https://docs.microsoft.com/en-us/windows/win32/search/-search-sql-formsof

Microsoft 70-464 Developing Microsoft SQL Server Databases Exam Practice Test