index on datetime

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

SandpointGuy - 13 Mar 2008 15:59 GMT

If I have an index on a datetime, will a where clause with year(<datatime
column>) use the index? Or should I create a seperate index for it?
Thank you

Reply to this Message

Tom Cooper - 13 Mar 2008 16:48 GMT

No it won't use the index. But you can change your query so that it will
use the index. Instead of

Select ...
Where Year(MyDateTime) = 2008

do

Select ...
Where MyDateTime >= '20080101' And MyDateTime < '20090101'

and that will use the index.

Tom

> If I have an index on a datetime, will a where clause with year(<datatime
> column>) use the index? Or should I create a seperate index for it?
> Thank you

Reply to this Message

Tom Cooper - 13 Mar 2008 17:17 GMT

Actually, I should clarify. If you do the query as

Select ...
Where MyDateTime >= '20080101' And MyDateTime < '20090101'

then SQL will use the index whenever SQL believes using the index will be
faster. Depending on the amount and distribution of your data and exactly
what your query is, SQL may decide that it is faster to scan the table or
use another index. In those cases, of course, SQL will not use the index.

And, if your query is "covered" by this index, either query will use the
index. But the form using the Year() function will scan the entire index,
and the other will only scan the part of the index that contain the 2008
entries.

That's the long and messy answer to your question. The short answer is that
the form of the query using the Year() function is never better at using the
index and is often worse.

Tom

> No it won't use the index. But you can change your query so that it will
> use the index. Instead of
[quoted text clipped - 15 lines]
>> column>) use the index? Or should I create a seperate index for it?
>> Thank you

Reply to this Message