When to index a table or not?

• Todd A Richardson

  SSC Rookie

  Points: 33

  More actions

  January 30, 2005 at 12:26 pm

  #163523

  I have setup a DTS job that performs the following steps once a week.

  • Truncate a User Table called Sales
  • Import 750,000 new sales records from a semi-colon delimited text file.
  • Execute an update query that adds a SalesID field to each record. (this is a concatenation of several columns for each record and may not be unique)

  This whole process takes about 2 minutes.

  Here is my question: all querys and views against this Sales table use the SalesId field to identify a result set. Therefore my thought is that I need Clustered index on the SalesID field in the sales table.

  What is the right way to handle this:

  1. Leave the table as is and do not add an index to the SalesID field. (All queries would rely on file scan of the table)
  2. Add a permenat Index to the SalesID field. Which will probably cause the truncate and Import to run more slowly.
  3. Do option 2 but drop the index before truncating the table and add the Index back to the SalesId field as the last step in the DTS job.

  Any idea what would provide the best performance? If I missed any options please let me know, thank you for any help!

• Antares686

  SSC Guru

  Points: 125444

  More actions

  January 31, 2005 at 6:04 am

  #539712

  I would go with option 3. But to really know the impact you will need to test it to be sure it provides you with what you want.