70-461 Exam Questions - Online Test

70-461 Premium VCE File

Learn More 100% Pass Guarantee - Dumps Verified - Instant Download
150 Lectures, 20 Hours

we provide Best Quality Microsoft 70 461 certification free download which are the best for clearing sql 70 461 test, and to get certified by Microsoft Querying Microsoft SQL Server 2012. The 70 461 pdf Questions & Answers covers all the knowledge points of the real 70 461 querying microsoft sql server 2012 exam. Crack your Microsoft exam 70 461 pdf Exam with latest dumps, guaranteed!

Q21. You generate a daily report according to the following query: 

You need to improve the performance of the query. 

What should you do? 

A. Drop the UDF and rewrite the report query as follows: 

WITH cte(CustomerID, LastOrderDate) AS ( 

SELECT CustomerID, MAX(OrderDate) AS [LastOrderDate] 

FROM Sales.SalesOrder 

GROUP BY CustomerID 

) 

SELECT c.CustomerName 

FROM cte 

INNER JOIN Sales.Customer c 

ON cte.CustomerID = c.CustomerID 

WHERE cte.LastOrderDate < DATEADD(DAY, -90, GETDATE()) 

B. Drop the UDF and rewrite the report query as follows: 

SELECT c.CustomerName 

FROM Sales.Customer c 

WHERE NOT EXISTS ( 

SELECT s.OrderDate 

FROM Sales.SalesOrder 

WHERE s.OrderDate > DATEADD(DAY, -90, GETDATE()) 

AND s.CustomerID = c.CustomerID) 

C. Drop the UDF and rewrite the report query as follows: 

SELECT DISTINCT c.CustomerName 

FROM Sales.Customer c 

INNER JOIN Sales.SalesOrder s 

ON c.CustomerID = s.CustomerID 

WHERE s.OrderDate < DATEADD(DAY, -90, GETDATE()) 

D. Rewrite the report query as follows: 

SELECT c.CustomerName 

FROM Sales.Customer c 

WHERE NOT EXISTS (SELECT OrderDate FROM 

Sales.ufnGetRecentOrders(c.CustomerID, 90)) 

Rewrite the UDF as follows: 

CREATE FUNCTION Sales.ufnGetRecentOrders(@CustomerID int, @MaxAge datetime) 

RETURNS TABLE AS RETURN ( 

SELECT OrderDate 

FROM Sales.SalesOrder 

WHERE s.CustomerID = @CustomerID 

AND s.OrderDate > DATEADD(DAY, -@MaxAge, GETDATE()) 

Answer: A 

Q22. CORRECT TEXT 

You need to create a view named uv_CustomerFullNames. The view must prevent the underlying structure of the customer table from being changed. 

Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code. 

Answer: 

Q23. CORRECT TEXT 

You have a view that was created by using the following code: 

You need to create an inline table-valued function named Sales.fn_OrdersByTerritory. Sales.fn_OrdersByTerritory must meet the following requirements: 

. Use one-part names to reference columns. 

. Return the columns in the same order as the order used in OrdersByTerritoryView. 

Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code. 

Answer: 

Q24. CORRECT TEXT 

You need to create a table named OrderDetails on a new server. OrderDetails must meet the following requirements: 

. Contain a new column named LineltemTotal that stores the product of ListPrice 

and Quantity for each row. 

. The calculation for a line item total must not be run every time the table is queried. 

. The code must NOT use any object delimiters. 

The solution must ensure that LineItemTotal is stored as the last column in the table. 

Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code. 

Answer: 

Q25. You administer several Microsoft SQL Server 2012 database servers. Merge replication has been configured for an application that is distributed across offices throughout a wide area network (WAN). Many of the tables involved in replication use the XML and varchar (max) data types. Occasionally, merge replication fails due to timeout errors. You need to reduce the occurrence of these timeout errors. What should you do? 

A. Set the Merge agent on the problem subscribers to use the slow link agent profile. 

B. Create a snapshot publication, and reconfigure the problem subscribers to use the snapshot publication. 

C. Change the Merge agent on the problem subscribers to run continuously. 

D. Set the Remote Connection Timeout on the Publisher to 0. 

Answer: A 

Q26. DRAG DROP 

You use Microsoft SQL Server 2012 to develop a database application. You create two tables by using the following table definitions. 

Which six Transact-SQL statements should you use? (To answer, move the appropriate SQL statements from the list of statements to the answer area and arrange them in the correct order.) 

Answer: 

Q27. DRAG DROP 

You create the following stored procedure. (Line numbers are included for reference only.) 

You need to ensure that the stored procedure performs the following tasks: 

. If a record exists, update the record. 

. If no record exists, insert a new record. 

Which four Transact-SQL statements should you insert at line 07? (To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.) 

Answer: 

Q28. CORRECT TEXT 

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

You deploy a new server that has SQL Server 2012 installed. You need to create a table named 

Sales.OrderDetails on the new server. Sales.OrderDetails must meet the following requirements: 

. Write the results to a disk. 

. Contain a new column named LineItemTotal that stores the product of ListPrice and Quantity for each row. 

. The code must NOT use any object delimiters. 

The solution must ensure that LineItemTotal is stored as the last column in the table. Which code segment should you use? 

To answer, type the correct code in the answer area. 

Answer: 

Q29. You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.) 

You need to display rows from the Orders table for the Customers row having the CustomerIdvalue set to 1 in the following XML format. 

Which Transact-SQL query should you use? 

A. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML RAW 

B. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers=CustomerId = 1 FOR XML RAW, ELEMENTS 

C. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO 

D. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML AUTO, ELEMENTS 

E. SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML AUTO 

F. SELECT Name, Country, Orderld, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML AUTO, ELEMENTS G. SELECT Name AS '@Name', Country AS '@Country', OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML PATH ('Customers') 

H. SELECT Name AS 'Customers/Name', Country AS 'Customers/Country', OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML PATH ('Customers') 

Answer: C 

Q30. You develop a Microsoft SQL Server 2012 database. You create a view from the Orders and OrderDetails tables by using the following definition. 

You need to improve the performance of the view by persisting data to disk. What should you do? 

A. Create an INSTEAD OF trigger on the view. 

B. Create an AFTER trigger on the view. 

C. Modify the view to use the WITH VIEW_METADATA clause. 

D. Create a clustered index on the view. 

Answer: D