Provisioning SQL Databases — Question 16

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?

Answer options

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

Correct answer: D

Explanation

The correct answer, D, efficiently counts all purchases made today by checking if PurchaseTime is greater than or equal to the start of the current date. Option A would only count purchases made at exactly midnight, while B checks for an exact match with the current date and time, which is unlikely to occur. Option C converts the datetime to a string format, which is less efficient than using a direct comparison with the date.