Month(x) vs Between(x and y)

[Steve Kass]

With few exceptions, if a column is in an expression in a
search condition, no index on that column will be useable
by the query processor.

In this case, Month() could hinder performance, by making
it impossible to use an index on ADateField to execute the
query.

The alternative is not quite correct, though. You at least need quotes:
.... between '20030801' and '20030830'. If you want the query to
be identical to the Month() one, you also need to include ADateField
values after midnight on the 30th and any time on the 31st. You
might have business rules to make it certain that no value of ADateField
has a time part, or none is a weekend day, but it's safest, and just
as efficient to write

where ADateField >= '20030801' and ADateField < '20030901'

The alternative also will not return any results in years before or
after 2003, but the original will.

Steve Kass
Drew University

London Developer wrote:

>Hello,
>
>I wrote this query that had the line:
>
> Month(ADateField)=8
>
>and was told it is 'inefficient' and should be written:
>
>and DateEntered between (20030801 and 20030830)
>
>as it uses indexes and is more reliable e.t.c
>
>I countered this saying I thought the month function was easier to read and
>can still use indexes and would also be efficient enough.
>
>Am I far wrong?
>
>
>
>

[Kalen Delaney]

You're right that the Month() function is much more readable, but it is true
that using a column inside any function means that an index on the column
cannot be used. The index is organized by the date value itself, not by some
function of the date value. It is much too cost prohibitive to apply the
function to every key value while searching the index, so the index is not
even considered.

--
HTH
----------------
Kalen Delaney
SQL Server MVP
[url]www.SolidQualityLearning.com[/url]


"London Developer" <dev@nowhere.com> wrote in message
news:OBmBQj#PDHA.1216@TK2MSFTNGP11.phx.gbl...

> Hello,
>
> I wrote this query that had the line:
>
> Month(ADateField)=8
>
> and was told it is 'inefficient' and should be written:
>
> and DateEntered between (20030801 and 20030830)
>
> as it uses indexes and is more reliable e.t.c
>
> I countered this saying I thought the month function was easier to read

and

> can still use indexes and would also be efficient enough.
>
> Am I far wrong?
>
>