Your database contains a table named Purchases. Thetable 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 madeon
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
The most efficient is D because the filter in the where clause it is written as a SARG (search argument). If you include the indexed column as part of an expression or as part of a function argument the query optimizer wont be able to use the index defined over the column, and this most likely lead to an inefficient execution plan.
I just don’t quite understand why you chose “D” as the correct answer. “A” seems the most efficient query to me. The filter in the WHERE clause for “A” is written as a SARG as well. So that shouldn’t make “D” more efficient than “A”. “D” seems less efficient to me(at least at the surface) since it used 2 functions(CONVERT() and DATEADD()) instead of one(which is the case for “A”).
“A” is not the correct answer becuase it’s mentioned in the question that PurchaseTime column has both date and time while convert(date,Getdate()) returns date only.
Corect is D.
http://stackoverflow.com/questions/32196745/which-transact-sql-query-is-most-efficient
I don’t think this has to do to with query efficiency. I think it has to do with the fact that purchasetime is a datetime and could be 2016-2-3 4:00:00 and you either have to convert it to 2016-2-3 00:00:00 to compare to today or you have to do a yesterday < purchasetime < today type of a test which requires no conversion and thus immediate index usage. Answer A probably has issues with the time ruining the comparison since purchase time is datetime and getdate is converted to date.
D