Query performance diffrence between equals and between

Question

I need to know why the correct answer is D ? what is the diffrence between D and A.

Your database contains a table named SalesOrders. The table includes a DATETIME column named OrderTime that stores the date and time each order is placed. There is a non-clustered index on the OrderTime column. The business team wants a report that displays the total number of orders placed 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 SalesOrders WHERE OrderTime = CONVERT(DATE, GETDATE()) 
B. SELECT COUNT(*) FROM SalesOrders WHERE OrderTime = GETDATE() 
C. SELECT COUNT(*) FROM SalesOrders WHERE CONVERT(VARCHAR, OrderTime, 112) = CONVERT(VARCHAR, GETDATE(), 112))
D. SELECT COUNT(*) FROM SalesOrders WHERE OrderTime >= CONVERT(DATE, GETDATE()) AND OrderTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))

Correct Answer: D

Answer 1

Since the column OrderTime contains the whole time stamp option A would only return orders places exactly on midnight. There is no attempt to extract the date from the stamp.

D doesn’t change the column and can use an index since it’s regular comparisons. C would most likely also work but it includes a CONVERT for each row tested so no index used and every row has to be checked.