New Updated Microsoft MCSE 70-464 Real Exam Questions and Answers Download 161-170

Ensurepass

QUESTION 161

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

 

The stored procedure must meet the following requirements:

 

clip_image002Favor physical memory when physical memory is available.

clip_image002[1]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?

 

A.

CREATE TABLE ##Table…

B.

CREATE TABLE Table…

C.

CREATE VIEW Table…

D.

CREATE PARTITION SCHEME Table…

E.

DECLARE TABLE @ Table…

 

Correct Answer: A

Explanation:

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 162

DRAG DROP

Your network contains a server named Server1 that runs SQL Server 2012. Server1 contains an instance named Instance1. Instance1 contains a database named ContentDatabase.

 

ContentDatabase uses transaction log backups.

The recovery model of ContentDatabase is set to FULL.

 

You need to shrink the ContentDatabase_Log log file to 10 MB. The solution must ensure that you can continue to back up the transaction log.

 

Which three code segments should you execute?

 

To answer, move the appropriate code segments from the list of code segments to the answer area and arrange them in the correct order.

 

clip_image004

 

Correct Answer:

clip_image006

 

QUESTION 163

You have a Microsoft SQL Azure database that contains a table named Employees.

 

clip_image007

 

You create a non-clustered index named EmployeeName on the name column.

 

clip_image008

 

You write the following query to retrieve all of the employees that have a name that starts with the letters JOH:

 

You discover that the query performs a table scan.

 

You need to ensure that the query uses EmployeeName.

 

What should you do?

 

A.

Recreate EmployeeName as a unique index

B.

Recreate EmployeeName as a clustered index

C.

Replace LEFT(name,3) = ‘JOH’ by using name like ‘JOH%’

D.

Replace LEFT(name,3) = ‘JOH’ by using substring(name, 1, 3) = ‘JOH’

 

Correct Answer: C

 

 

QUESTION 164

You have a SQL Server 2012 database that contains a table named Users. The Users table contains usernames and passwords. You need to ensure that all new records have a password. Which code segment should you use? More than one answer choice may achieve the goal. Select the BEST answer.

 

clip_image009

 

A.

Option A

B.

Option B

C.

Option C

D.

Option D

 

Correct Answer: D

 

 

QUESTION 165

You have a SQL Server 2012 environment that contains two servers. The servers are configured as shown in the following table.

 

clip_image010

 

After the failover is complete, a user receives the following error message when connecting to DB1 on Server2:

 

“Msg 916, Level 14, State 1, Line 1

 

The server principal “Account1” is not able to access the database “DB1″ under the current security context.”

 

You verify that there is a server login for Account1 on Server2.

 

You need to ensure that Account1 can connect to DB1.

 

What should you do?

 

More than one answer choice may achieve the goal. Select the BEST answer.

 

A.

Update the SID for Account1 on DB1.

B.

Add Account1 to the db_datareader role.

C.

Create a new database user on DB1.

D.

Implement Windows authentication.

 

Correct Answer: B

 

 

QUESTION 166

DRAG DROP

You have two existing tables, one named COUNTRY and the other named STATES.

 

The tables are defined as follows:

 

clip_image011

 

You need to set up a rule that every STATE.Country_Abbr must match an existing record in the COUNTRY table.

 

Develop the solution by selecting and arranging the required code blocks in the correct order.

 

You may not need all of the code blocks.

 

clip_image013

 

Correct Answer:

clip_image015

 

 

QUESTION 167

DRAG DROP

You plan to deploy two stored procedures name USP_1 and USP_2 that read data from a database.

 

Your company identifies the following requirements for each stored procedure:

 

clip_image002[2]USP_1 cannot allow dirty reads.

clip_image002[3]USP_2 must place range locks on the data to ensure read consistency.

 

You need to identify which isolation level you must set for each stored procedure. The solution must minimize the number of locks.

 

Which isolation level should you identify?

 

To answer, drag the appropriate isolation level to the correct stored procedure in the answer area. (Answer choices may be used once, more than once, or not at all.)

 

clip_image016

 

Correct Answer:

clip_image017

 

 

QUESTION 168

You use SQL Server 2014 to maintain the data used by applications at your company.

You need to run two separate SQL statements.

 

You must guarantee that the following three things happen:

 

1. Either BOTH statements succeed or BOTH statements fail as a batch.

2. If an error occurs on the first statement, SQL should not attempt to run the second statement.

3. Error information should be returned to the client.

 

What should you do?

 

clip_image018

 

A.

Option A

B.

Option B

C.

Option C

D.

Option D

 

Correct Answer: A

Explanation:

* SET XACT_ABORT

When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back. When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing.

 

 

QUESTION 169

You have a database that is accessed by 300 concurrent users.

 

You need to log all of the queries that become deadlocked. The solution must meet the following requirements:

 

clip_image002[4]Provide a representation of the deadlock in XML format.

clip_image002[5]Minimize the impact on the server.

 

What should you create?

 

A.

A SQL Server Profiler trace

B.

A script that enables trace flags

C.

A SQL Server Agent job that retrieves information from the sys.dm_tran_active_transactions dynamic management views

D.

A SQL Server Agent job that retrieves information from the sys.dm_tran_session_transactions dynamic management views

 

Correct Answer: A

Explanation:

Analyze Deadlocks with SQL Server Profiler Use SQL Server Profiler to identify the cause of a deadlock. A deadlock occurs when there is a cyclic dependency between two or more threads, or processes, for some set of resources within SQL Server. Using SQL Server Profiler, you can create a trace that records, replays, and displays deadlock events for analysis. To trace deadlock events, add the Deadlock graph event class to a trace. This event class populates the TextData data column in the trace with XML data about the process and objects that are involved in the deadlock. SQL Server Profiler can extract the XML document to a deadlock XML (.xdl) file which you can view later in SQL Server Management Studio.

 

 

QUESTION 170

You have the following query on a disk-based table:

 

clip_image019

 

You discover that the query takes a long time to complete.

 

The execution plan is shown in the Execution Plan exhibit. (Click the Exhibit button.)

 

clip_image020

 

The index usage is show in the Index Usage exhibit. (Click the Exhibit button.)

 

clip_image022

 

You need to reduce the amount of time it takes to complete the query. You must achieve this goal as quickly as possible. What should you do?

 

A.

Reorganize the index.

B.

Update statistics.

C.

Create an index on LastName.

D.

Rebuild the index.

 

Correct Answer: C

 

Instant Access to Download Latest Complete Collection of Microsoft MCSE 70-464 Real Exam

Try Microsoft MCSE 70-464 Free Demo