| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2004-08-25 : 08:29:34
|
| I'm sure this is not a good idea. I'd like to know why exactly.I've got a clustered index (ColA, ColB, ColC, ColD)Column D can be NULL frequently. I could IsNULL it to zero to avoid the NULL.Why is a NULL value bad for a clustered index and why?Sam |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-08-25 : 08:45:58
|
| Actually, I'd be more concerned about the fact you have four columns on your clustered index. You do realize that all the non-clustered indexes on this table contain the clustering key and will be significantly larger with a four column clustered index, right? This will make them significanly larger. The rebuild time will be a pain also.You avoid NULL because it's normally not even included in the index, so it's inefficient to have them.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2004-08-25 : 08:55:20
|
No, I didn't know the part about nonclustered indexes becoming larger if the clustered index is large.So a better design would be to have fewer columns on the clustered index and more columns on the nonclustered indexes.I'm not sure what you mean byquote:
You avoid NULL because it's normally not even included in the index
Can you elaborate on why ColD having 50% NULLs adds inefficency?Sam |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-08-25 : 08:59:46
|
quote:
Can you elaborate on why ColD having 50% NULLs adds inefficency?
I'd have thought that any index with 50% anything isn't particularly great.-------Moo. :) |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-08-25 : 09:00:22
|
| ANDIt doesn't seem that your cluster is unique....or is it?And if it is, why would you have column 4 in any caseas a general rule of thumb a clustered unique index is the PK of a table...Sam...why don't you post the DDL?Also, don't you remeber the index intersection discussions?Now that was a good threadBrett8-) |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2004-08-25 : 13:18:01
|
| The 4 columns give me what's needed so the reporting query results in a clustered index seek. It's better performing than an index scan.What Index Intersection discussion?Sam |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-08-25 : 14:36:01
|
quote:
Originally posted by mr_mist
quote:
Can you elaborate on why ColD having 50% NULLs adds inefficency?
I'd have thought that any index with 50% anything isn't particularly great.-------Moo. :)
Absolutley...low cardinality and all...But I get a sense that even though it's not used for look up, it's good because it's covered...Sam can you post the sql statement?Brett8-) |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2004-08-25 : 14:47:41
|
You wanna see my query? You wanna see my query? I'll show ya my query !  There's just no trust here anymore  I'd like to save this for tomorrow to do justice to the thread. Right now I'm tracking down some ferrets in my code.I have one question: If I alter the order of columns in a clustered index, seems SQL should reorder the table. Thought I saw a thread somewhere that said no way...Sam |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2004-08-25 : 14:48:52
|
| Anyone got a pointer to the "Index Intersection" thread Brett mentioned?Brett, I'm assuming you need more time to recover from the Margaritas... |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2004-08-25 : 15:03:54
|
| All I can say is: "Oh that Index Intersection thread" ( geese monese )I'll read through it again later and see if it makes any more sense now than it did then. |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2004-08-25 : 15:05:37
|
| I'd still like to know if SQL reorders data rows whenever the CLUSTERED INDEX column order is altered. If it does, it seems awfully fast to me. If it doesn't, then why not? |
|
|
|
|
Clustered Index with NULLs?