| Author |
Topic |
|
Zoroaster
Aged Yak Warrior
702 Posts |
 Posted - 2007-10-17 : 18:24:57
|
I am looking for some expert opinions on when is it not appropriate to use a clustered index on a table?
Future guru in the making. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-17 : 18:26:35
|
| Staging tables typically don't have any indexes on them.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-10-17 : 18:31:04
|
Any other cases? I am seeing quite a number of databases without clustered indexes on tables that seem they should have them. For example, I have a table with about a million rows of data with a header number column that is incremented by the application that does the inserts, the table has 3 non-clustered index with no clustered index. The users are complaining of slowness querying the data and I have identified a number of problems already but I am also considering adding a clustered index, any reason not to?
Future guru in the making. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-17 : 18:37:30
|
| I've always been taught that every table should have a clustered index on it, otherwise it is a heap. The only exception is staging tables.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-18 : 00:01:04
|
| "The users are complaining of slowness querying the data and I have identified a number of problems already but I am also considering adding a clustered index, any reason not to?"The clustered index is going to enable you to reorganise the data in that table. the slowness may simply be because of fragmentation and so on accumulated & exasperated over time.I presume you will change an existing index to be clustered, rather than create a new one?!Kristen |
|
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-10-18 : 00:14:12
|
quote:
I presume you will change an existing index to be clustered, rather than create a new one?!Kristen
Yep, that is my plan.
Future guru in the making. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-10-18 : 02:22:16
|
When the column is UNIQUEIDENTIFIER, it is probably a bad idea to have clustered index.But in SQL Server 2005 with the new SEQUENTIALNEWID() function the impact is not that hard.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-18 : 03:12:49
|
| If the CLUSTERED index is an IDENTITY (well, any index that is on an IDENTITY column) set the FILLFACTOR to 100%Kristen |
|
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-10-18 : 08:44:48
|
quote:
Originally posted by Kristen
If the CLUSTERED index is an IDENTITY (well, any index that is on an IDENTITY column) set the FILLFACTOR to 100%Kristen
Interesting, does that apply if it is a number sequence that the application increments but not necessarily a SQL Server Identity column?
Future guru in the making. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-10-18 : 09:33:19
|
| it appyls to all values that that are guaranteed to be unique, ever increasing and non changeable._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
workflow
Starting Member
2 Posts |
Posted - 2007-11-16 : 07:48:39
|
quote:
Originally posted by spirit1
it appyls to all values that that are guaranteed to be unique, ever increasing and non changeable.
Is that totally true? For a non-clustered index I can see it would make sense as the index is just on the numeric value and so it just needs space for that. But for a clustered index the final page of the index is also the data page - so if the size of the data in the row increases this will increase the size required on the page and if it is full to 100% it will need a page split.I can envisage two instances where the row size might grow:1. if there is a varchar field on the row - e.g. if it was varchar(50) if this started with say 'ABC' in it and was later updated to 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' surely the row will grow by 23 bytes (or does the filling allow for max rowsize ???2. If you later add another column to the table - I can't see how this would not then need splits and if the table is large this could be very inefficient - and also because SQL is likely to split the page say 50/50 it could end up with a very inefficient use of the data - this is probably just something that you would need to take care with (i.e. do an index rebuild after changing the schema) |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-11-16 : 08:04:36
|
| emm.. i'm not sure we're talking about the same thing.one thing is a good CI candidate value, another is the fill factor.if you have to frequently add columns to a table this means you have bad design.and if you have 100% fill factor it means that if you do change table schema it would be best to reindex the CI, since you're bound to get page splits._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
workflow
Starting Member
2 Posts |
Posted - 2007-11-16 : 14:27:49
|
| Sorry - I thought you were replying to the earlier quote"If the CLUSTERED index is an IDENTITY (well, any index that is on an IDENTITY column) set the FILLFACTOR to 100%"I am happy about the idea of IDENTITY for clustered index (and use it most of the time) - it was just the FILLFACTOR 100% that I was not convinced aboutAs for adding columns - I can't agree that adding is due to bad design but do accept that an index rebuild makes sense.But what about the change in varchar data - am I correct that this would split the page? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-11-16 : 14:35:02
|
> As for adding columns - I can't agree that adding is due to bad design but do accept that an index rebuild makes sense.i said frequently  > But what about the change in varchar data - am I correct that this would split the page?that would split the page yes. but that is why index maintainance is for._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
|
Clustered Index usage