Online Access Free 070-464 Exam Questions

You have a database named database1.
Database developers report that there are many deadlocks.
You need to implement a solution to monitor the deadlocks. The solution must meet the following requirements:
* Support real-time monitoring.
* Be enabled and disabled easily.
* Support querying of the monitored data.
What should you implement?
More than one answer choice may achieve the goal. Select the BEST answer.

• A.Log errors by using trace flag 1204
• B.Log errors by using trace flag 1222
• C.An Extended Events session
• D.A SQL Server Profiler template

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

Your network contains a server named SQL1 that has SQL Server 2012 installed. SQL1 contains a database name DB1 and a table named Customers.
You add an additional server named SQL2 that runs SQL Server 2012.
You need to create a distributed partitioned view. The solution must minimize the amount of network traffic.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

• A.Remove the Customers table from SQL1.
• B.Add the Customers table to SQL2.
• C.Create the view on SQL2.
• D.Add SQL2 as a Distributor.
• E.Add SQL2 as a linked server.
• F.Create the view on SQL1.

You have a database that contains a table named Department. Department contains the names and locations of each department.
You have a table-valued function named ProjectList() that returns a list of all the projects assigned to a department. The name of the department is passed as an argument to the ProjectList() function.
You need to create a query that returns a list of all the department names and the project names. The solution must return only departments that are associated to projects.
What should you add to the query?

• A.CROSS JOIN
• B.CROSS APPLY
• C.OUTER APPLY
• D.OUTER JOIN

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

You have the following query:

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

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

Correct Answer:

Explanation:
Note:
Covering index: A type of index that includes all the columns that are needed to process a particular query. For example, your query might retrieve the FirstName and LastName columns from a table, based on a value in the ContactID column. You can create a covering index that includes all three columns.