SQL Server Performance

New Index

Discussion in 'General Developer Questions' started by danny123, May 4, 2009.

  1. danny123 New Member

    Hi there,
    I have a table with 5 columns. ID,CustID ,FormID, DateAdded,DateUpdated.
    Now i have to query the table like below:
    Select Distinct CustID,FormID
    From tblTable
    where CustID = @CustID and ((DateAdded between @SDate and @EDate) or (DateUpdated between @SDate and @EDate))
    So should i just create one non clustered index on all these 4 fields or should i create 2 separate indeces ,one on CustID ,FormID and second of DateAdded,DateUpdated ,CustID
    Please advice!
    Thanks!
  2. Sandy New Member

    Hi Danny,
    As per my understanding you have an ID column which may be an IDENTITY column if I am not wrong. If so then you can create a Primary Index on it which is a good practice.
    Then coming to your 2nd point:
    [quote user="danny123"]So should i just create one non clustered index on all these 4 fields or should i create 2 separate indeces ,one on CustID ,FormID and second of DateAdded,DateUpdated ,CustID[/quote]
    I think you will not get more performance benefit from creating one index on all 4 columns rather than you can create non-clustered index on CustID and DateAdded, DateUpdated separately as per the query in your post.
    Hope it will help you.
    Thanks,
    Sandy.
  3. FrankKalis Moderator

    I doubt that both ideas will be very useful at all. Because of the way the query is expressed with the OR clause I don't think SQL Server will really consider the indices anyway. It might be worth trying out what happens when you rewrite it to a UNION like
    Select CustID,FormID
    From tblTable
    where CustID = @CustID and DateAdded between @SDate and @EDate
    UNION
    Select CustID,FormID
    From tblTable
    where CustID = @CustID and DateUpdated between @SDate and @EDate
    with 2 indices on {CustID, DateAdded, FormID} and {CustID, DateUpdated, FormID}

Share This Page