Post reply

Indexing Question

  • February 14, 2006 at 11:52 am

    #89420

    Hello,

    I have a table that will be queried and sorted quite frequently based on the Date field, CreateDate.  Should this field be indexed in order to maximize performance?  There are other fields such are RecID, etc, that are indexed, but I have not typically indexed Date fields before.

    SQL 2000.  The table will be queried from ASP pages as well as Crystal Reports.

    I appreciate the advice!

     

    JB

  • February 14, 2006 at 11:58 am

    #620798

    >>Should this field be indexed in order to maximize performance? 

    Answer is: "it depends".

    Factors to consider:

    - What is the data distribution in the table

    - Will the index be unique or non, clustered or non-clustered

    - Are the queries typically highly selective (small % of total table rows)

     

  • February 14, 2006 at 2:02 pm

    #620823

    Thank you for the post.  Certainly more things I need to consider.  What I can answer now is that it will be non-clustered, non-unique.  The table itself will be very small, roughly 8 fields.  The number of records will ultimately be in the hundreds of thousands, potentially reaching millions.

     

    Sorry for the vague info.  I do appreciate the help!

  • February 15, 2006 at 5:29 am

    #620973

    Most probably it would be to your advantage to index this column. There are even circumstances when it is a good idea to put a clustered (though possibly non-unique) index on such column... for example, if this column will never change (as can be expected with CreateDate) and at the same time you know that large part of queries will use CreateDate in the conditions (especially if it will be non-equal condition, like Between, >, &lt. Of course, you have to consider your needs and it is very well possible that some other clusetered index would work a lot better for you.

    In short - based on the few known facts I think yes, the column should be indexed, but it is just my opinion; you'll have to consider it yourself, since you know a lot more about the situation.

  • February 15, 2006 at 9:41 am

    #621091

    Thank you!  I know the situation but I certainly have some learning to do about indexing and the actual development aspects to SQL.

    I appreciate your help and time!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply