[Free] 2019(Nov) EnsurePass Microsoft 70-464 Dumps with VCE and PDF 41-50

Get Full Version of the Exam
http://www.EnsurePass.com/70-464.html

Question No.117

You plan to modify a procedure that contains hundreds of lines of code. The modification must support the following guidelines:

image

Use only tables that are not persistent in the database.

image

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?

  1. A system table

  2. A partitioned table

  3. A table variable

  4. A temporary table

Correct Answer: C

Question No.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:

image

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.

image

Correct Answer:

image

Question No.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:

image

Identify unused indexes.

image

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.)

image

Correct Answer:

image

Question No.120

You have a SQL Azure database. You execute the following script:

image

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?

  1. CREATE INDEX IX_Table1 ON Table1 (Column2)

    WITH FILLFACTOR-0

  2. CREATE INDEX IX_Table1 OK Table1 (Column1) INCLUDE (Column2)

  3. CREATE INDEX IX_Table1 ON Table1 (Column2) WHERE Column2 IS NULL

  4. 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

Question No.121

You are creating a table named Orders. You need to ensure that every time a new row is added to the Orders table, a table that is used for auditing is updated. What should you use? More than one answer choice may achieve the goal. Select the BEST answer.

  1. A Data Definition Language (DDL) trigger

  2. A DEFAULT constraint

  3. A CHECK constraint

  4. A FOREIGN KEY constraint

  5. A data manipulation language (DML) trigger

Correct Answer: E

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 No.122

You have a text file that contains an XML Schema Definition (XSD). You have a table named Schema1.Table1. You have a stored procedure named Schema1.Proc1 that accepts an XML parameter named Param1. You need to store validated XML data in Schema1.Table1. The solution must ensure that only valid XML data is accepted by Param1. What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

  1. Define an XML column in Table1 by using an XML schema collection.

  2. Create an XML schema collection in the database from the text file.

  3. Declare Param1 var1 as type XML and associate the variable to the XML schema collection.

  4. use the modify method to insert the XML schema into each row of the XML column in Table1.

Correct Answer: ABD

Explanation:

http://msdn.microsoft.com/en-us/library/bb510420.aspx http://msdn.microsoft.com/en-us/library/ms187856.aspx http://msdn.microsoft.com/en-us/library/ms176009.aspx http://msdn.microsoft.com/en-us/library/hh403385.aspx http://msdn.microsoft.com/en-us/library/ms184277.aspx

Question No.123

You have an index for a table in a SQL Azure database. The database is used for Online

Transaction Processing (OLTP). You discover that many page splits occur when records are inserted or updated in the table. You need to minimize the number of page splits. What should you set from the index options?

  1. FILLFACTOR = 0

  2. STATISTICS_NORECOMPUTE = OFF

  3. STATISTICS_NORECOMPUTE = ON

  4. FILLFACTOR = 80

Correct Answer: D

Explanation:

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

Question No.124

You have a SQL Server 2012 database named Database1. You execute the following code:

image

You insert 3 million rows into Sales.

You need to reduce the amount of time it takes to execute Proc1. What should you do?

image

  1. Option A

  2. Option B

  3. Option C

  4. Option D

Correct Answer: A

Question No.125

DRAG DROP

You execute the following code:

image

You discover that the Customers table was created in the dbo schema.

You need to create a code segment to move the table to another schema named Schema2. What should you create?

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

image

Correct Answer:

image

Question No.126

DRAG DROP

You plan to deploy two stored procedures named SP1 and SP2 that read data from the database. Your company identifies the following requirements for each stored procedure:

image

SP1 must allow dirty reads.

image

SP2 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.)

image

Correct Answer:

image

Get Full Version of the Exam
70-464 Dumps
70-464 VCE and PDF