Thanks for the confirmation, Keith. Just wanted to be sure that this wasn't a(nother) case of me not being aware of some setting/property/config file somewhere, but this leads me to a question about your P.S. statement...
In the beforetime, when I was relatively server agnostic, "a friend" was under the impression that all PKs should be clustered. Then, I filled my head with book knowledge, reading that clustered indexes should "always" be set up relative to either the most used sort order or the most likely insert order for "high volume" tables, which is not always by our PKs (sp-generated surrogate keys). So, it seemed that in some cases our PK indexes should not always be clustered if we wanted peak-ish performance.
Now, having typed that mouthful, I'm not sure it applies to this particular table (a hired gun "guru" implemented the clustered index change in our test system as part of index tuning and I'm going to take it up with him, as I think the column he added to make the composite key is superfluous for our business rules) but I'm just curious as to where I should find some guidelines for this sort of index setup. In reality, away from the gentle confines of academia, is it considered a particularly poor idea to have PKs unclustered? And if so, why?
I thank you in advance for your infinite patience and wisdom in regards to this matter.
---J
>Jason,
>
>This is actually a bug in the SQL ADO.NET provider. ADO.NET relies on the GetSchemaTable method to discover keys and constraints. If you want to see where the confusion comes from, just look at the help for this method :)
>
>P.S. Shame on you for clustering something other than your PK! Time to move those clustered keys to their own tables. Remember rule #3 - Eliminate columns not dependent on the key (and move them to a separate table).
>
>Either that or shoot the DBA.
>
>JUST JOKING!
>
>- Keith