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()))
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
D is the best answer because it is SARGABLE. By using a search argument in the WHERE clause, SQL will use the index on the PurchaseTime column.
I think it’s a DATETIME datatype issue. GETDATE() returns a date+time result, so PurchaseTime = GETDATE() in the WHERE clause will show only the Purchases made in THIS instant of time, so B must be discarded as the correct answer. We can say something similar for answers A and C, so the only suitable answer is D, which is the only one that puts a range in the WHERE clause. I think that here ‘efficiency’ is not the point, it’s obtaining the correct result set….
b
A