New Updated Microsoft MCSE 70-464 Real Exam Questions and Answers Download 131-140

Ensurepass

QUESTION 131

You have a SQL Server 2012 instance. You plan to create an application that uses spatial data. You need to create an object that will support the representation of the surface area of all the oceans. Which code segment should you use?

 

clip_image001

 

A.

Option A

B.

Option B

C.

Option C

D.

Option D

 

Correct Answer: A

 

 

QUESTION 132

You use SQL Server 2012 to store data used by an e-commerce application. You develop a stored procedure named sp1. Sp1 is used to read the price of all the products sold on the e-commerce site. You need to ensure that sp1 can read data even while another transaction is modifying the price of a product. Sp1 must only read committed data. Which transaction isolation level should you use in sp1?

 

A.

Serializable

B.

Snapshot

C.

Repeatable read

D.

Read committed

 

Correct Answer: B

 

 

QUESTION 133

DRAG DROP

You have a database named database1. Each table in database1 has one index per column.

 

Users often report that creating items takes a long time.

 

You need to perform the following maintenance tasks:

 

clip_image003Identify unused indexes.

clip_image003[1]Identify indexes that need to be defragmented.

 

What should you use?

 

To answer, drag the appropriate function to the correct management task in the answer area. (Answer choices may be used once, more than once, or not at all.)

 

clip_image005

 

Correct Answer:

clip_image007

 

 

QUESTION 134

DRAG DROP

You have a database that contains three tables. The tables are configured as shown in the following table.

 

clip_image008

 

You have the following query:

 

clip_image009

 

The execution plan for the query is shown in the exhibit. (Click the Exhibit button.)

 

clip_image011

 

You need to create one index to minimize the amount of time it takes to execute the query.

 

What should you do?

 

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

 

clip_image012

 

Correct Answer:

clip_image013

 

 

QUESTION 135

You have a SQL Server 2012 instance that hosts a single-user database. The database does not contain user-created stored procedures or user-created functions. You need to minimize the amount of memory used for query plan caching. Which advanced server option should you modify?

 

A.

Scan for Startup Procs

B.

Enable Contained Databases

C.

Optimize for Ad hoc Workloads

D.

Allow Triggers to Fire Others

 

Correct Answer: C

 

 

QUESTION 136

You execute the following code.

 

clip_image014

 

After populating the Employees table with 10,000 rows, you execute the following query:

 

clip_image015

 

You need to reduce the amount of time it takes to execute the query.

 

What should you do?

 

A.

Partition the table and use the JobTitle column for the partition scheme.

B.

Change SUBSTRING(JobTitle,l,l) = ‘C to JobTitle LIKE ‘c%’

C.

Change SUBSTRING (JobTitle, 1,1] = ‘c’ to LEFT(JobTitle ,1) = ‘c’.

D.

Replace IX_Employees with a clustered index.

 

Correct Answer: B

 

 

QUESTION 137

You have a server that has SQL Server 2012 installed. You need to identify which parallel execution plans are running in serial. Which tool should you use?

 

A.

Performance Monitor

B.

Database Engine Tuning Advisor

C.

Data Profile Viewer

D.

Extended Events

 

Correct Answer: D

Explanation:

http://msdn.microsoft.com/en-us/library/bb677278.aspx http://msdn.microsoft.com/en-us/library/bb630282.aspx http://www.sql-server-performance.com/2006/query-execution-plan-analysis/ http://www.simple-talk.com/sql/learn-sql-server/understanding-and-using-parallelism-in- sqlserver/

http://www.sqlservercentral.com/articles/SQL+Server+2012/At+last%2c+execution+plans+ show+true+thread+reservations./92458/

http://sqlblog.com/blogs/paul_white/archive/2011/12/23/forcing-a-parallel-query- executionplan.aspx

http://sqlblog.com/blogs/paul_white/archive/2012/05/02/parallel-row-goals-gone-rogue.aspx http://msdn.microsoft.com/en-us/library/bb895310.aspx http://msdn.microsoft.com/en-us/library/bb895313.aspx http://msdn.microsoft.com/en-us/library/hh231122.aspx

 

 

QUESTION 138

You have a SQL Server 2012 database named DB1. You have a backup device named Device1.

You discover that the log file for the database is full. You need to ensure that DB1 can complete transactions. The solution must not affect the chain of log sequence numbers (LSNs). Which code segment should you execute?

 

A.

BACKUP LOG DB1 TO Device1 WITH COPY_ONLY

B.

BACKUP LOG DB1 TO Device1 WITH NORECOVERY

C.

BACKUP LOG DB1 TO Device1 WITH TRUNCATE_ONLY

D.

BACKUP LOG DB1 TO Device1

 

Correct Answer: D

Explanation:

http://msdn.microsoft.com/en-us/library/ms186865.aspx http://msdn.microsoft.com/en-us/library/ms179478.aspx http://msdn.microsoft.com/en-us/library/ms190925.aspx

 

 

QUESTION 139

DRAG DROP

You plan to create a new table that will contain a column named Salary. Salary will contain highly sensitive data.

 

Salary must meet the following requirements:

 

clip_image003[2]Contain numeric data.

clip_image003[3]Contain only encrypted data that remains encrypted in memory.

 

You need to identify which encryption type and data type must be used for Salary.

 

Which encryption type and data type should you identity?

 

To answer, drag the appropriate encryption type and data type to the correct identifier in the answer area.

 

clip_image016

 

Correct Answer:

clip_image017

 

 

QUESTION 140

You have a Microsoft SQL Azure database named DBAzure1.

 

You create a table in DBAzure1 by using the following script:

 

clip_image018

 

You need to recommend a solution to ensure that each combination of CustomerContact and CustomerDetails is not duplicated.

 

What should you recommend creating?

 

A.

A CHECK constraint

B.

A filtered index

C.

A columnstore index

D.

A UNIQUE constraint

 

Correct Answer: D

 

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

Try Microsoft MCSE 70-464 Free Demo