Which Transact-SQL query should you use?

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?

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

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



Leave a Reply 2

Your email address will not be published. Required fields are marked *


bla

bla

when you use a function on a indexed column then the index wont be used. rule of thumb to avoid if possible.

Yommy O.

Yommy O.

Option D is correct.
For purchases made on the current day:

WHERE PurchaseTime >= CONVERT(DATE, GETDATE())
AND PurchaseTime = ‘today’)
The second part adds one whole day (DATEADD(DAY, 1)…) to today; implying that PurchaseTime < (today + 1 more day).