index on datetime

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
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
"SandpointGuy" <SandpointGuy@.discussions.microsoft.com> wrote in message
news:186D952F-DB39-45F1-8ED3-70A7BA2162C7@.microsoft.com...
> 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
|||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
"Tom Cooper" <tomcooper@.comcast.no.spam.please.net> wrote in message
news:%23DIHnGShIHA.5368@.TK2MSFTNGP04.phx.gbl...
> 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
> "SandpointGuy" <SandpointGuy@.discussions.microsoft.com> wrote in message
> news:186D952F-DB39-45F1-8ED3-70A7BA2162C7@.microsoft.com...
>