none
How to create index for date range columns

    Question

  • Hi All,

    I have table A that has a StartDate and an EndDate as two DATETIME columns.  I have another table B that has a CreatedDate DATETIME column.  I need to efficiently query these two tables like so:

    CreatedDate BETWEEN StartDate AND EndDate
    - OR -
    CreatedDate >= StartDate AND CreatedDate <= EndDate
    Currently the CreatedDate column is the primary key in Table B with a clustered index, however I haven't created the index for Table A yet and was wondering how I should approach this.  Table A has roughly 1.2 million rows.  Should I created one index with both StartDate and EndDate together, or should I have separate indexes for each?

    Thank You
    -Sam


    Sunday, August 30, 2009 9:08 AM

Answers

  • Hi Sam,
    I would suggest if your space can afford
    Go for 2 separate indexes...

    1) As SQL Server internally going to do separate searches for 2 conditions.
    2) And I assume startdate has no relationship with enddate which means there is no static difference between Startdate & Enddate.

    So go for 2 separate indexes...

    Let me know if you have any further questions.

    Please Vote & "Mark As Answer" if this post is helpful to you.

    Cheers
    Bikash Dash
    MCDBA/MCITP



    • Proposed as answer by Bikash Dash Sunday, August 30, 2009 11:08 AM
    • Unproposed as answer by SamCosta Sunday, August 30, 2009 6:24 PM
    • Marked as answer by SamCosta Sunday, August 30, 2009 9:50 PM
    Sunday, August 30, 2009 9:33 AM

All replies

  • Hi Sam,
    I would suggest if your space can afford
    Go for 2 separate indexes...

    1) As SQL Server internally going to do separate searches for 2 conditions.
    2) And I assume startdate has no relationship with enddate which means there is no static difference between Startdate & Enddate.

    So go for 2 separate indexes...

    Let me know if you have any further questions.

    Please Vote & "Mark As Answer" if this post is helpful to you.

    Cheers
    Bikash Dash
    MCDBA/MCITP



    • Proposed as answer by Bikash Dash Sunday, August 30, 2009 11:08 AM
    • Unproposed as answer by SamCosta Sunday, August 30, 2009 6:24 PM
    • Marked as answer by SamCosta Sunday, August 30, 2009 9:50 PM
    Sunday, August 30, 2009 9:33 AM
  • For got to mention the second part...

    If there is any static difference between Startdate & Enddate then go for 1 composite index...


    Please Vote & "Mark As Answer" if this post is helpful to you.

    Cheers
    Bikash Dash
    MCDBA/MCITP

    Sunday, August 30, 2009 9:35 AM
  • Sam,

    You have to add more information here. On what columns are you joining table a and table b? You need to share the complete schema of table A along with the primary key, indexes etc...

    How unique are the values in startdate and enddate? There are too many factors and its not easy to share any valuable info without knowing further details.


    | Sankar Reddy | http://sankarreddy.spaces.live.com/ |
    Sunday, August 30, 2009 9:46 AM
    Moderator
  • Bikash and Sankar, thank you for your responses.  I appreciate your help!

    Sankar, the table schemas are below:

    PhoneLoginAssignment As A
    ---------------------------------
    LoginID (FK to PhoneLogin table)
    EmpID (FK to Employee table)
    StartDate
    EndDate

    PhoneStats As B
    ---------------------------------
    CreatedDate (PK) Indexed Composite Key
    LoginID (PK, FK) Indexed Composite Key
    AcdCalls
    AcdTime
    AcwTime
    HoldTime
    StaffTime
    AvailTime

    Join:
    ------
    B.CreatedDate BETWEEN A.StartDate AND A.EndDate
    B.LoginID = A.LoginID

    Our telephone login ID's are recycled when one of our call center representatives are terminated.

    Sunday, August 30, 2009 6:23 PM
  • Yes Sam,

    The reply i had given above is correct...

    Go for 2 separate indexes...

    Please Vote & "Mark As Answer" if this post is helpful to you.

    Cheers
    Bikash Dash
    MCDBA/MCITP

    Sunday, August 30, 2009 6:59 PM
  • Sam,

    The reason I asked for schema including indexes on both tables is it makes a lot of difference on what columns you are joining between the the tables and what columns you are retrieving from the select statement.

    I see that you haven't mentioned any clustered index/primary key on PhoneLoginAssignment table. That means this table is a HEAP and heaps suffer from bookmark lookups unless the data is covered in the covering index or using a include clause. Even if you create 2 separate indexes on StartDate and Enddate I am afraid you will NOT see much improvement as you *may* be hitting a bookmark lookup unless you have a covered index based on the columns you select.

    Also the order of the columns in a composite key is very important here. You can do some testing by looking at the execution plan if the indexes are helping or not and you also need to look at STATISTICS IO to understand the amount of disk IO generated for this query. 


    | Sankar Reddy | http://sankarreddy.spaces.live.com/ |
    Monday, August 31, 2009 12:32 AM
    Moderator