July 17, 2012 at 11:21 am
Nice question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 17, 2012 at 4:58 pm
Pretty close split between 15 and 16 for answers.
July 18, 2012 at 9:00 am
Sean Lange (7/17/2012)
Learned something today from a good question, although it was fairly easy to deduce the correct answer. Given the binary nature of computers the max number of columns without an xml index would likely be 16 or 32. At the very least it would be an even number. Then given that there was a question it strongly indicates that the xml index must take at least one of the available columns away. From the possible answers there was only 1 odd number. I of course chose the "oddball" answer, being the only logical choice and I was correct. My initial thought honestly was that if I got it wrong I didn't really mind too much because I would never make a clustered index cover that many columns. 😀
Yes. This raises an interesting question.
Can anyone come up with a plausible scenario where it would make sense to create a clustered index with, say, 10 or more columns?
No fair coming up with a poorly-normalized database or similar shenanigans.
I'm talking a real-world, well-designed table which actually needs 10+ columns in its clustered index.
July 18, 2012 at 9:56 am
Nice question, thanks!
July 25, 2012 at 8:16 pm
Nice straightforward question.
Maybe http://msdn.microsoft.com/en-us/library/bb934097.aspx is a better reference for this than the one given. The information there is much closer to the top of the page there.
Tom
July 25, 2012 at 9:08 pm
sknox (7/18/2012)
Can anyone come up with a plausible scenario where it would make sense to create a clustered index with, say, 10 or more columns?No fair coming up with a poorly-normalized database or similar shenanigans.
I'm talking a real-world, well-designed table which actually needs 10+ columns in its clustered index.
Something to remember is that if there is an XML index on the table then the clustering key must be the primary key (see the BoL page CREATE XML INDEX, which says "A clustered index must exist on the primary key of the user table"). So if I have a long primary key I'm forced to have a long clustering key. I wouldn't generally choose a long clustering key, so if I had a long primary key I would not use it for clustering, but if I have an XML index on the table I don't have that getout - maybe have to use a surrogate for the natural primary key as the primary key, which could be a nuisance. But maybe even for a natural primary key, more than 10 columns seems a bit long, so perhaps it's not too much of a problem
The place where having an XML index will be a real pain (even if the primary key is short) is where it is desirable to have a natural primary key, and that will not be monotonic in time so using it as cluster key will result in a lot of page splits. Distinctly a pain, since having an XML index means the primary key has to be the cluster key. It pushes towards use of a surrogate as primary key even in cases where performace considerations make it sensible (in the absence of this consequence of having an XML index) to use the natural key. Fortunately my intense dislike of XML (caused by the outrageous hype and resultant overuse, not by anything in XML itself) means that I'm unlikely ever to be stuck with that sort of problem.
Tom
October 22, 2012 at 11:03 pm
Thanks...learnt sumthing new today 🙂
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply