Exam 70-461 topic 1 question 20 discussion

Actual exam question from Microsoft's 70-461

Question #: 20
Topic #: 1

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()))

Show Suggested Answer

Suggested Answer: D 🗳️
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.
Reference:
http://technet.microsoft.com/en-us/library/ms181034.aspx

by dennix at March 29, 2020, 11:06 a.m.

Comments

Submit Cancel

dennix

5 years, 3 months ago

"where convert" is a manipulation of the original column and will result in SQL not being able to use the index, and therefore this is not the most efficient answer.

upvoted 1 times

Sugar

5 years, 2 months ago

the requirement is the most efficient result. it did not mention about forcing to use index

upvoted 1 times

...