New Updated Microsoft MCSE 70-464 Real Exam Questions and Answers Download 111-120

Posted on by admin

Ensurepass

QUESTION 111

You execute the following code:

 

clip_image001

 

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

 

clip_image002

 

You need to create a covering index on UserInfo.

 

Which code segment should you execute?

 

clip_image003

 

A.

Option A

B.

Option B

C.

Option C

D.

Option D

 

Correct Answer: C

 

 

QUESTION 112

You have an index for a table in a SQL Azure database. The database is used for Online Transaction Processing (OLTP). You discover that the index consumes more physical disk space than necessary. You need to minimize the amount of disk space that the index consumes. What should you set from the index options?

 

A.

STATISTICS_NORECOMPUTE = OFF

B.

FILLFACTOR = 80

C.

FILLFACTOR = 0

D.

STATISTICS_NORECOMPUTE = ON

 

Correct Answer: C

Explanation:

http://msdn.microsoft.com/en-us/library/ms177459.aspx http://msdn.microsoft.com/en-us/library/ms188783.aspx

 

 

QUESTION 113

You run the following code:

 

clip_image004

 

You need to ensure that the root node of the XML data stored in the Details column is <Order_Details>.

 

What should you implement?

 

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

 

A.

A user-defined data type

B.

An XML index

C.

A Data Definition Language (DDL) trigger

D.

A data manipulation language (DML) trigger

E.

An XML schema collection

 

Correct Answer: E

Explanation:

http://msdn.microsoft.com/en-us/library/ms187856.aspx

 

 

QUESTION 114

You are creating a table named Orders. You need to ensure that every time a new row is added to the Orders table, a user-defined function is called to validate the row before the row is added to the table. What should you use? More than one answer choice may achieve the goal. Select the BEST answer.

 

A.

A data manipulation language (DML) trigger

B.

A DEFAULT constraint

C.

A Data Definition Language (DDL) trigger

D.

A CHECK constraint

E.

A FOREIGN KEY constraint

 

Correct Answer: D

Explanation:

http://www.techrepublic.com/blog/programming-and-development/comparing-sql- serverconstraints-and-dmltriggers/402

http://msdn.microsoft.com/en-us/library/ms178110.aspx

 

 

QUESTION 115

You have a SQL Azure database.

 

You execute the following code:

 

clip_image005

 

The Sales.Customers table will contain 100,000 rows. You expect the FaxNumber column to contain a null value for 70 percent of the rows.

 

You need to create an index to support Sales.CustomersByFaxNumber. The solution must minimize the disk storage requirements.

 

Which code segment should you execute?

 

A.

CREATE INDEX IX_Customers ON Cust
omers (FaxNumber) WHERE FaxNumber IS NOT NULL

B.

CREATE INDEX IX_Customers ON Customers (FaxNumber) WITH FILLFACTOR=0

C.

CREATE INDEX IX_Customers ON Customers (CustomerName) INCLUDE (FaxNumber)

D.

CREATE INDEX IX_Customers ON Customers (FaxNumber)

E.

CREATE INDEX IX_Customers ON Customers (FaxNumber) WHERE FaxNumber IS NULL

 

Correct Answer: A

 

 

QUESTION 116

You plan to create a new column in a table. The column must meet the following requirements:

 

clip_image007Be able to store images that are larger than 1 MB each.

clip_image007[1]Be able to access the images from Microsoft .NET Framework applications.

 

You need to recommend which data type must be used in the column.

 

Which data type should you recommend?

 

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

 

A.

nvarchar

B.

varbinary

C.

image

D.

FileStream

 

Correct Answer: D

 

 

QUESTION 117

You plan to modify a procedure that contains hundreds of lines of code.

 

The modification must support the following guidelines:

 

clip_image007[2]Use only tables that are not persistent in the database.

clip_image007[3]Minimize the amount of time required to execute and recompile procedures.

 

You need to identify which type of table must be used to support the planned modification.

 

Which type of table should you identify?

 

A.

A system table

B.

A partitioned table

C.

A table variable

D.

A temporary table

 

Correct Answer: C

 

 

QUESTION 118

DRAG DROP

You have a SQL Server 2012 database named DB1. DB1 contains four filegroups named FG1, FG2, FG3, and FG4. You execute the following code:

 

clip_image008

 

Two million rows are added to dbo.Sales.

 

You need to move the data from the first partition to a new table named SalesHistory and, starting on December 31, 2012, repartition dbo.Sales to support new sales data for three months.

 

Which code segment 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_image010

 

Correct Answer:

clip_image012

 

 

QUESTION 119

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_image007[4]Identify unused indexes.

clip_image007[5]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_image014

 

Correct Answer:

clip_image016

 

 

QUESTION 120

You have a SQL Azure database.

 

You execute the following script:

 

clip_image017

 

You add 1 million rows to Table1. Approximately 85 percent of all the rows have a null value for Column2.

 

You plan to deploy an application that will search Column2.

 

You need to create an index on Table1 to support the planned deployment. The solution must minimize the storage requirements.

 

Which code segment should you execute?

 

A.

CREATE INDEX IX_Table1 ON Table1 (Column2)

WITH FILLFACTOR-0

B.

CREATE INDEX IX_Table1 OK Table1 (Column1)

INCLUDE (Column2)

C.

CREATE INDEX IX_Table1 ON Table1 (Column2)

WHERE Column2 IS NULL

D.

CREATE INDEX IX_Table1 ON Table1 (Column2)

WHERE Column2 IS NOT NULL

 

Correct Answer: D

Explanation:

http://msdn.microsoft.com/en-us/library/ms188783.aspx http://msdn.microsoft.com/en-us/library/cc280372.aspx

 

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

Try Microsoft MCSE 70-464 Free Demo

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.