September 30, 2001 at 1:27 pm
Is there ever a good reason not to create a clustered index on a table?
September 30, 2001 at 3:08 pm
Nope. If you don't have a column that is really worth using it on, at least use it on your primarykey.
Andy
October 3, 2001 at 5:48 pm
quote:
Nope. If you don't have a column that is really worth using it on, at least use it on your primarykey.Andy
Do small tables (I have not found the break point yet - maybe under 100 rows)need a clustered index? I have found that the query optimizer sometimes chooses to do a table scan whether there is a index available or not. What's the point of a clustered index if it's not used? Aren't you just adding overhead?
Patrick Birch
Quand on parle du loup, on en voit la queue
October 3, 2001 at 8:27 pm
I imagine there are times that due to the small amount of data, the query planner may opt for a table scan rather than the index. Aside from it just being a good practice, I ran into a problem last year that was related - I had a table that at most would contain 3-4k rows, each row maybe 1000 bytes or so, no clustered key. The contents would get dumped every half hour. Over time we started to see performance degradation - finally traced it to the table consuming many many times the space it should have - was not getting cleaned up after the deletes. Added a clustered index and the problem went away.
Even without that case, I think its just solid practice. If its a small table how much performance hit can it possibly be...versus if its small now but grows later (as data tends to do) having the clustered index should definitely help performance (if chosen well).
Andy
October 4, 2001 at 7:06 am
quote:
I imagine there are times that due to the small amount of data, the query planner may opt for a table scan rather than the index. Aside from it just being a good practice, I ran into a problem last year that was related - I had a table that at most would contain 3-4k rows, each row maybe 1000 bytes or so, no clustered key. The contents would get dumped every half hour. Over time we started to see performance degradation - finally traced it to the table consuming many many times the space it should have - was not getting cleaned up after the deletes. Added a clustered index and the problem went away.Even without that case, I think its just solid practice. If its a small table how much performance hit can it possibly be...versus if its small now but grows later (as data tends to do) having the clustered index should definitely help performance (if chosen well).
Andy
Thanks Andy. I learn something every day.
Patrick
Quand on parle du loup, on en voit la queue
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply