Free Microsoft 070-461 Practice Test & Real Exam Questions

You use a Microsoft SQL Server 2012 database that contains two tables named SalesOrderHeader and SalesOrderDetail. The indexes on the tables are as shown in the exhibit. (Click the Exhibit button.)

You write the following Transact-SQL query:

You discover that the performance of the query is slow. Analysis of the query plan shows table scans where the estimated rows do not match the actual rows for SalesOrderHeader by using an unexpected index on SalesOrderDetail.
You need to improve the performance of the query.
What should you do?

You are working with a table that has an XML column that contains information about books. Each book may have an associated price.
You need to write a query that returns each author on a separate row in XML format.
Which XML method should you use?

You are designing a table for a SQL Server database. The table uses horizontal partitioning.
You have the following requirements:

You need to choose the appropriate data type for the key value.
What should you do?

You use a Microsoft Azure SQL DataBase instance. The instance contains a table named Customers that has columns named Id, Name, and IsPriority.
You need to create a view named VwPriorityCustomers that:
* returns rows from Customer that have a value of True in the IsPriority column, and
* does not allow columns to be altered or dropped in the underlying table.
Which Transact-SQL statement shoul you run?

You need to create a stored procedure to support the following:
* TRY/CATCH error handling
* Transaction management
Which three statements should you include in the stored procedure in sequence? To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.

You have a database that contains the tables as shown in the exhibit. (Click the Exhibit button.)

You have the following query:

You need to recreate the query to meet the following requirements:
* Reference columns by using one-part names only.
* Sort aggregates by SalesTerritoryID, and then by ProductID.
* Order the results in descending order from SalesTerritoryID to ProductID.
* The solution must use the existing SELECT clause and FROM clause.
Which code segment should you use?
To answer, type the correct code in the answer area.

You are maintaining a Microsoft SQL Server database. You run the following query:

You observe performance issues when you run the query. You capture the following query execution plan:

You need to ensure that the query performs returns the results as quickly as possible.
Which action should you perform?

You plan to deploy a stored procedure for a database named TICKETS.
You need to implement error handling for the stored procedure to ensure that the system-defined error messages are returned if an error occurs upon insert.
Part of the correct Transact-SQL has been provided in the answer are below. Enter the code in the answer area that resolves the problem and meets the stated goals or requirements. You can add code within the code that has been provided as well as below it.


Use the 'Check Syntax' button to verify your work. Any syntax or spelling errors will be reported by line and character position.

You administer a database that includes a table named Customers that contains more than 750 rows. You create a new column named PartitionNumber of the int type in the table.
You need to assign a PartitionNumber for each record in the Customers table. You also need to ensure that the PartitionNumber satisfies the following conditions:
* Always starts with 1.
* Starts again from 1 after it reaches 100.
Which Transact-SQL statement should you use?

You are developing a database that will contain price information.
You need to store the prices that include a fixed precision and a scale of six digits.
Which data type should you use?