Querying Microsoft SQL Server (70-461) Certification Exam Sample Questions and answers

Question: 01. You use Microsoft SQL Server 2012 to write code for a transaction that contains several statements. There is high contention between readers and writers on several tables used by your transaction. You need to minimize the use of the tempdb space. You also need to prevent reading queries from blocking writing queries. Which isolation level should you use? a) SERIALIZABLE b) SNAPSHOT c) READ COMMITTED SNAPSHOT d) REPEATABLE READ Answer: c) READ COMMITTED SNAPSHOT Question: 02. How many clustered indexes can you create on a table? a) 999 b) 1 c) 16 d) 900 Answer: b) 1 Question: 03. What is the limit for the number of steps in statistic histograms? a) 10 steps per histogram b) 200 histograms per column c) 200 pages per histogram d) 200 steps per histogram Answer: d) 200 steps per histogram Question: 04. 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 nonclustered 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) SELECT COUNT(*) FROM Purchases WHERE PurchaseTime >= CONVERT(DATE, GETDATE()) AND PurchaseTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE())) Question: 05. You use Microsoft SQL Server 2012 to develop a database application. You need to create an object that meets the following requirements: - Takes an input parameter - Returns a table of values - Can be referenced within a view Which object should you use? a) inline table-valued function b) user-defined data type c) stored procedure d) scalar-valued function Answer: a) inline table-valued function How to Prepare for Querying Microsoft SQL Server Certification Exam? Click Here... https://70-461-prepara