Why You Need To exam 70 461 sample questions?

Cause all that matters here is passing the Microsoft exam 70 461 sample questions exam. Cause all that you need is a high score of 70 461 dumps Querying Microsoft SQL Server 2012 exam. The only one thing you need to do is downloading Examcollection microsoft 70 461 exam study guides now. We will not let you down with our money-back guarantee.

Q17. You use a contained database named ContosoDb within a domain. You need to create a user who can log on to the ContosoDb database. You also need to ensure that you can port the database to different database servers within the domain without additional user account configurations. 

Which type of user should you create? 

A. SQL user without login 

B. SQL user with a custom SID 

C. SQL user with login 

D. Domain user 

Answer: A 

Q18. 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: 

Q19. Your database contains a table named Purchases. The table includes a DATETIME column named PurchaseTime that stores the date and time each purchase is made. There is a non-clustered index on the PurchaseTime column. 

The business team wants a report that displays the total number of purchases made on the current day. 

You need to write a query that will return the correct results in the most efficient manner. 

Which Transact-SQL query should you use? 

A. SELECT COUNT(*) 

FROM Purchases 

WHERE PurchaseTime = CONVERT(DATE, GETDATE()) 

B. SELECT COUNT(*) 

FROM Purchases 

WHERE PurchaseTime = GETDATE() 

C. SELECT COUNT(*) 

FROM Purchases 

WHERE CONVERT(VARCHAR, PurchaseTime, 112) = CONVERT(VARCHAR, 

GETDATE(), 112) 

D. SELECT COUNT(*) 

FROM Purchases 

WHERE PurchaseTime >= CONVERT(DATE, GETDATE()) 

AND PurchaseTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE())) 

Answer: D 

Explanation: 

Two answers will return the correct results (the "WHERE CONVERT..." and "WHERE ... AND ... " answers). The correct answer for Microsoft would be the answer that is most "efficient". Anybody have a clue as to which is most efficient? In the execution plan, the one that I've selected as the correct answer is the query with the shortest duration. Also, the query answer with "WHERE CONVERT..." threw warnings in the execution plan...something about affecting CardinalityEstimate and SeekPlan. 

I also found this article, which leads me to believe that I have the correct Answer: http://technet.microsoft.com/en-us/library/ms181034.aspx 

Q20. DRAG DROP 

You develop a database application for a university. You need to create a view that will be indexed that meets the following requirements: 

. Displays the details of only students from Canada. 

. Allows insertion of details of only students from Canada. 

Which four 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: 

Q21. You administer a Microsoft SQL Server database that supports a banking transaction management application. 

You need to retrieve a list of account holders who live in cities that do not have a branch location. 

Which Transact-SQL query or queries should you use? (Each correct answer presents a complete solution. Choose all that apply.) 

A. SELECT AccountHolderID FROM AccountHolder WHERE CityID NOT IN (SELECT CityID FROM BranchMaster) 

B. SELECT AccountHolderID FROM AccountHolder WHERE CityID <> ALL (SELECT CityID FROM BranchMaster) 

C. SELECT AccountHolderlD FROM AccountHolder WHERE CityID <> SOME (SELECT CityID FROM BranchMaster) 

D. SELECT AccountHolderID FROM AccountHolder WHERE CityID <> ANY (SELECT CityID FROM BranchMaster) 

Answer: A,B 

Explanation: 

Verified the answers as correct. 

Reference: http://msdn.microsoft.com/en-us/library/ms188047.aspx 

Reference: http://msdn.microsoft.com/en-us/library/ms177682.aspx 

Reference: http://msdn.microsoft.com/en-us/library/ms173545.aspx 

Q22. You are writing a set of queries against a FILESTREAM-enabled database. 

You create a stored procedure that will update multiple tables within a transaction. 

You need to ensure that if the stored procedure raises a runtime error, the entire 

transaction is terminated and rolled back. 

Which Transact-SQL statement should you include at the beginning of the stored 

procedure? 

A. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 

B. SET XACT_ABORT OFF 

C. SET TRANSACTION ISOLATION LEVEL SNAPSHOT 

D. SET IMPLICIT_TRANSACTIONS ON 

E. SET XACT_ABORT ON 

F. SET IMPLICIT TRANSACTIONS OFF 

Answer: E 

Q23. You have a Microsoft SQL Server 2012 database that contains tables named Customers and Orders. 

The tables are related by a column named CustomerID. 

You need to create a query that meets the following requirements: 

. Returns the CustomerName for all customers and the OrderDate for any orders that they have placed. . Results must include customers who have not placed any orders. 

Which Transact-SQL query should you use? 

A. SELECT CustomerName, OrderDate FROM Customers RIGHT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID 

B. SELECT CustomerName, CrderDate FROM Customers JOIN Orders ON Customers.CustomerID = Orders.CustomerID 

C. SELECT CustomerName, OrderDate FROM Customers CROSS JOIN Orders ON Customers.CustomerID = Orders.CustomerID 

D. SELECT CustomerName, OrderDate FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID 

Answer: D 

Q24. You develop a database for a travel application. You need to design tables and other database objects. 

You create the Airline_Schedules table. 

You need to store the departure and arrival dates and times of flights along with time zone 

information. 

What should you do? 

A. Use the CAST function. 

B. Use the DATE data type. 

C. Use the FORMAT function. 

D. Use an appropriate collation. 

E. Use a user-defined table type. 

F. Use the VARBINARY data type. 

G. Use the DATETIME data type. 

H. Use the DATETIME2 data type. 

I. Use the DATETIMEOFFSET data type. 

J. Use the TODATETIMEOFFSET function. 

Answer: I