<= vs between, Table scan and indexes

Hello all I have a proc that uses a date field as a part of the where clause, for example:

WHERE createdate >= '01/01/1999' AND createdate <= '09/01/2003'

I've also tried:

WHERE createdate BETWEEN '01/01/1999' AND '09/01/2003'

Both of these configurations result in a table scan on a million plus row table.

I tried creating an index on createdate but it did not seem to help:

CREATE
INDEX [AR1CLOSEDI_CreateDate] ON [dbo].[AR1CLOSEDI] ([CREATDATE])

But it makes no difference... Any suggestions?

Regards
The CaptianOriginally posted by CaptainEstock
Hello all I have a proc that uses a date field as a part of the where clause, for example:

WHERE createdate >= '01/01/1999' AND createdate <= '09/01/2003'

I've also tried:

WHERE createdate BETWEEN '01/01/1999' AND '09/01/2003'

Both of these configurations result in a table scan on a million plus row table.

I tried creating an index on createdate but it did not seem to help:

CREATE
INDEX [AR1CLOSEDI_CreateDate] ON [dbo].[AR1CLOSEDI] ([CREATDATE])

But it makes no difference... Any suggestions?

Regards
The Captian

Try clustered index on this field.|||Yes! Clustered index worked!

Runtime dropped from 18 minutes to less than 2
WOOHOO!!

Regards
The Captain|||...as long as you don't expect any duplicate datetime values.

blindman|||Originally posted by blindman
...as long as you don't expect any duplicate datetime values.

blindman

What are you afraid of?|||Okay so I created clustered indexes on the createdate field

Then I ran SQL query analyzer and now I see clustered index scan...

Is that better or worse?

REgards
The Captain|||Originally posted by blindman
...as long as you don't expect any duplicate datetime values.

blindman

You can have duplicate values in a clustered index. SQL Server adds a hidden key to each duplicate value.