John Colby (11/11/2009)
What does "narrow" mean in the "considerations for selecting the clustering key".
It refers to the width of the column, ie the number of bytes it takes. 4 byte integer is narrow, a 500 byte char is quite a bit wider.
What would be useful is to show in the graphic what happens as you add fields to the index. Is the data added to the Clustered Inkex Key Value nodes? For example, if I create a key by Zip5, then add Zip4, then Add Address.
If you do that, you have a composite (multi-column) key consisting of 3 columns. Where I show the single clustered index key column, you would have 3 columns.
Another question, if I have an "Identity" integer PK, and I am going to include that in the clustered key, I assume (now, after reading your article) that I need to specify Unique
If the clustered index is unique, specify that it is unique. Don't make it wider than it needs to be
Given both an Identity PK and a set of fields like Zip5 / Zip4 / Addr, why would I select one or the other as the Clustered key? The PK would be often used for Joins, the address data for sorts.
3 zip codes and an address is definitely not narrow. You can create the cluster on that, just not a very good choice. As I mentioned in the article and stated in the discussion, my take on the cluster is that it is used to organise the table. Use the nonclustered indexes to access the data. Most of the time.
And finally, what does all of this have to do with physical storage on disk and getting at the data. All these articles mention "the heap" vs ... (unknown something).
Heap vs table with a clustered index. I thought I'd covered that in this article.
Does this imply that if stored on "the heap" then fields of a table are scattered around, i.e. the entire row is placed piecemeal on the disk but if there is a clustered index then the fields are stored sequentially on the disk?
No, absolutely not. Rows are always stored in pages (8kb chunks of the disk), not haphazardly all over the data file. Did you read the first part of this series?
Are we talking about in the structure of the database file?
Structure within the data file.
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass