Datediff in Where Clause 1

[pankajv]

Can some one advise which of the below queries should be prefered over the other and why?

Select ItemCode from dbo.ItemStoreAd
Where DATEDIFF (D,getdate(),AdEffectiveDate) = 0


Select ItemCode from dbo.ItemStoreAd
Where AdEffectiveDate between '2003-07-15' and '2003-07-15 23:59:00'

 

[sunila7]

Hi,

If u have a index on AdEffectiveDate field then the second query will be better than the first, bcos the second query will use index first will not...

The first query will do a table scan where as the second query will do a index scan.

Hope it helps

Sunil

Sunil

 

[pankajv]

Will there be any difference if there is no index on the column used in the Datediff.

 

[sunila7]

Hi,

I think still the second query will be faster b'cos if there is a function used on the column then query optimiser will not be able to count the EstimatedRows, so the second query shd be faster.

Sunil

Sunil

 

[SQLSister]

Suggest you run both in query ananlyzer and look at the execution plan to see what is happening. Then you will know which is faster and you will start to learn why. This should also run significantly faster with an index on the date field.

 

[pankajv]

I tried running the Query plan for both the queries and they are exactly the same. Not sure, which way to go from here.

Anyway thanks a lot for the help.

Pankaj