Free Microsoft 070-461 Exam Dumps Questions & Answers

You develop a Microsoft SQL Server 2012 database that contains a heap named OrdersHistorical.
You write the following Transact-SQL query:
* INSERT INTO OrdersHistorical
* SELECT * FROM CompletedOrders
You need to optimize transaction logging and locking for the statement. Which table hint should you use?

• A.HOLDLOCK
• B.ROWLOCK
• C.TABLOCK
• D.UPDLOCK
• E.XLOCK

You have three tables that contain data for vendors, customers, and agents. You create a view that is used to look up telephone numbers for these companies.
The view has the following definition:

You need to ensure that users can update only the phone numbers by using this view.
What should you do?

• A.Drop the view. Re-create the view by using the SCHEMABINDING clause, and then create an index on the view.
• B.Alter the view. Use the EXPAND VIEWS query hint along with each SELECT statement.
• C.Create an AFTER UPDATE trigger on the view.
• D.Create an INSTEAD OF UPDATE trigger on the view.

A database named AdventureWorks contains two tables named Production.Product and Sales.SalesOrderDetail. The tables contain data on the available products and a detailed order history.
The Production.Product table contains the following two columns:
The Sales.SalesOrderDetail table contains the following three columns:
You need to create a query listing all of the products that were never ordered.
Which statements should you execute?

• A.Option A
• B.Option D
• C.Option C
• D.Option B

You need to build a table structure for a stored procedure to support data entry from a website form. The website form must meet the following requirements:

Which two actions should you perform? Each correct answer presents part of the solution.

• A.Add the SYSDATETIMEOFFSET function to the stored procedure.
• B.Add the DATEPART function to the stored procedure.
• C.Add the ISDATE function to the stored procedure.
• D.Add the DEFAULT constraint to the table structure.
• E.Add the CHECK constraint to the table structure.

You have a database that contains the tables as shown below:

You have a stored procedure named Procedure1. Procedure1 retrieves all order ids after a specific date. The rows for Procedure1 are not sorted. Procedure1 has a single parameter named Parameter1. Parameter1 uses the varchar type and is configured to pass the specific date to Procedure1. A database administrator discovers that OrderDate is not being compared correctly to Parameter1 after the data type of the column is changed to datetime. You need to update the SELECT statement to meet the following requirements:
The code must NOT use aliases.
The code must NOT use object delimiters.
The objects called in Procedure1 must be able to be resolved by all users.
OrderDate must be compared to Parameter1 after the data type of Parameter1 is changed to datetime.
Which SELECT statement should you use?
To answer, type the correct code in the answer area.

• A.SELECT Orders.OrderID
  FROM Orders
  WHERE Orders.OrderDate>CONVERT(datetime,@Parameter1)
• B.SELECT Orders.OrderID
  FROM Orders
  WHERE Orders.OrderDate>CONVERTER(datetime,@Parameter1)