[dba-SQLServer] Multi-column Clustered index

Did you also read that the table is ordered in the same sequence as 
the clustered index?

Try inserting Ansel Adams into your table with a clustered index like 
you have, and it has to move the millions of records in it down 
alphabetically in order to do it.  Disaster for performance.  If you 
have static data where you are not inserting rows or updating columns 
in the clustered index, and, you place in the clustered index on it 
after you are finished getting the data into the table, it might work 
well.  But never for a non-static data source.

No, you cannot get rid of the table and only have the index.


At 12:00 PM 10/1/2009, you wrote:
>Date: Thu, 01 Oct 2009 11:27:35 -0400
>From: jwcolby <jwcolby at colbyconsulting.com>
>Subject: [dba-SQLServer] Multi-column Clustered index
>To: Dba-Sqlserver <dba-sqlserver at databaseadvisors.com>
>Message-ID: <4AC4CA67.9070909 at colbyconsulting.com>
>Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>
>I am reading an article about clustered indexes which recommend 
>against setting multi-column
>clustered indexes where possible.  My understanding is that if data 
>is in a clustered index then the
>data can be directly obtained from that index rather than having to 
>go to the table itself.  My
>understanding is also that the organization of a clustered index is 
>much more efficient than the
>organization of the rest of the table.
>
>I commonly have tables where I have:
>
>PK, FName, LName, Addr, City, St, Zip5, Zip4, HashAddr, HashFamily, HashPerson
>
>Doesn't it makes sense to just place every single field in this 
>table in a clustered index.  In that
>case every field of every record is available from the index and 
>thus no retrieval of field data
>would need to go to the table itself.
>
>If it does make sense, is it possible to get rid of the actual table 
>and ONLY have the clustered
>index remain?  Is that possible?
>
>--
>John W. Colby
>www.ColbyConsulting.com