Clustered index on a small work file.

[joejack0330]

Hi, just need a little more understanding of using a clustered index on a table that we use basically as a work table then delete all records at end of process but not the table so we only load maybe a 1000 at most and only 6 fields. The key to file is 3 fields that get loaded so no identity field. When it was orginally setup, it was setup with a non-clustered index on only the primary key and worked ok or we thought where it took a little while but not too bad but lately it's been taking 4-5 times longer than it use to sometimes. We played with various things but finally noticed the key was a non-clustered index so we changed to a clustered and things are not running 4-5 times faster than it ever did! So, my question is when working with a small work table that records get clear out then reloaded maybe 50 times a day, is it better to use a clustered index? Thanks, Joe

 

[SQLWilts]

If you are only loading that number of records, then you can do an index rebuild straight after the populate.
A clustered index is the physical order of the table, so if you rebuilt that after re-loading the table, then you would be re-loading it.

Make sure your statistics are kept up to date, and a good use of a clustered index might help.

By the way, do use the execution plans (estimated and actual) to check out whats happening under the covers - you may find that the engine is ignoring the index anyway and doing a table scan - it depends on how much (%age wise) of the table the query returning.