Free Microsoft 070-464 Exam Dumps Questions & Answers

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

• A.  Execute sp_recompile 'usp_GetOrdersByProduct'.
• B.  Add WITH (FORCESEEK) to line 07 in usp_GetOrdersByProduct.
• C.  Add WITH RECOMPILE to line 03 in usp_GetOrdersByProduct.
• D.  Execute sp_help 'usp_GetOrdersByProduct'.

You run the following code:

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 Data Definition Language (DDL) trigger
• B.  A user-defined data type
• C.  A data manipulation language (DML) trigger
• D.  An XML index
• E.  An XML schema collection

You run the following code segment:

After you add 10,000 rows to Customers, you discover that the index is fragmented.
You need to defragment the index in the least amount of time.
Which code segment should you execute?
To answer, drag the appropriate value to the correct location in the code segment in the answer area. (Answer choices may be used once, more than once, or not at all.)

Correct Answer:

Explanation:
Note:
Locking the table during the process and not recomputing statistics would be the fastest.
* Online = OFF
Table locks are applied for the duration of the index operation. An offline index operation that creates, rebuilds, or drops a clustered, spatial, or XML index, or rebuilds or drops a nonclustered index, acquires a Schema modification (Sch-M) lock on the table. This prevents all user access to the underlying table for the duration of the operation. An offline index operation that creates a nonclustered index acquires a Shared (S) lock on the table. This prevents updates to the underlying table but allows read operations, such as SELECT statements.
* STATISTICS_NORECOMPUTE = ON
Out-of-date statistics are not automatically recomputed.

You use the following statement to create a table.

You have the following queries.

You need to create an index to minimize the execution time of the queries.
How should you complete the statement? To answer, drag the appropriate code elements to the correct locations. Each code element may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

Correct Answer:

Explanation:
Box 1: LastName
Redesign nonclustered indexes with a large index key size so that only columns used for searching and lookups are key columns. Make all other columns that cover the query into nonkey columns. In this way, you will have all columns needed to cover the query, but the index key itself is small and efficient.
Box 2: FirstName
Box 3: DepartmentID
Non-key columns, called included columns, can be added to the leaf level of a nonclustered index to improve query performance by covering the query. That is, all columns referenced in the query are included in the index as either key or non-key columns. This allows the query optimizer to locate all the required information from an index scan; the table or clustered index data is not accessed.
Box 4: OfficeID

You plan to modify a procedure that contains hundreds of lines of code.
The modification must support the following guidelines:
* Use only tables that are not persistent in the database.
* 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 partitioned table
• B.  A system table
• C.  A table variable
• D.  A temporary table