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 SaLeaOrders
WHERE OrderTime = CONVERT(DATE, GETDATE ())
B.
SELECT COUNT(*) FROM SalesOrders
WHERE OrderTime – GETDATE()
C.
SELECT COUNT(-) FROM SaLesCrders
WHERE CONCERT(VARCHAR, OrderTime, 112) =
CONVERT (VARCHAR, GETDATE(I, 112)
D.
SELECT CCUNT(*) FROM SalesCrder3
WHERE CrderTime >= CONVERT(DATE, GETDATE())
AND CrderTime < DATEADD(DAY, CONVERT(DATS, 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