June 23, 2008 at 9:05 am
Hi, I am working on an existing application and to my shock none of the tables in the application have clustered indexes. They do however, have nonclustered. All of them. Some several nonclustered indexes. At first I thought this was insane, and then I started to think that maybe the loading of these tables might actually go quicker without the clustered indexes as there wouldn't have to be any re-ordering going on when the new records are inserted. I don't know how the bookmarking on a nonclustered index goes though.
What do you guys think of this?
June 23, 2008 at 9:33 am
Ideally, you would have a clustered index that is either in the order data will be inserted (to speed up inserts and reduce fragmentation) or using columns you very frequently search on (speeding up queries but possibly requiring index defragmentation regularly). It will depend on the type of work being done and the performance you need to help the most.
Not having a clustered index only seems useful to me if you have data being inserted at a very high rate, you cannot predict the order it will be inserted, and because of other restrictions you cannot add some kind of identity or ordered column and you cannot reindex regularly enough. That's a lot of conditions, but they can all be met in some situations.
June 23, 2008 at 9:39 am
Keep in mind that heaps (tables with no clustered index) cannot be defragmented. So they can be very useful for fast load and things that are fairly heavy on inserts. That being said - if your activity is heavy on updates/deletes, a heap will likely end up VERY fragmented.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 23, 2008 at 9:49 am
Thanks, as usual you guys are awesome.
June 23, 2008 at 9:52 am
It's a general rule that you should have a clustered index on every table (there are exceptions and exceptions to the exceptions). For detailed discussion of why this is so, see Inside SQL Server by Kalen Delaney. The key thing is the cluster defines the storage of the table and then acts as the pointer in the indexes. No cluster means (I think I remember this correctly) that the indexes have a uniqueifier and a pointer to the data, making the index less efficient, except when it's a covering index. I'd add clustered indexes if it were my data.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply