Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2002
    Posts
    16

    Unanswered: <= 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 Captian

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: <= vs between, Table scan and indexes

    Originally 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.

  3. #3
    Join Date
    Jun 2002
    Posts
    16

    Thanks!

    Yes! Clustered index worked!

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

    Regards
    The Captain

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...as long as you don't expect any duplicate datetime values.

    blindman

  5. #5
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Originally posted by blindman
    ...as long as you don't expect any duplicate datetime values.

    blindman
    What are you afraid of?
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  6. #6
    Join Date
    Jun 2002
    Posts
    16

    Clustered Index Scan?

    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

  7. #7
    Join Date
    Mar 2002
    Posts
    34
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •