Your database contains a table named Purchases. The table includes a DATETIME column named PurchaseTi

me 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 r

eturn 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, GETD

ATE()))

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 e

fficient? In the execution plan, the one that Ive 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 a

nd SeekPlan.

http://technet.microsoft.com/en-us/library/ms181034.aspx