jwcolby
jwcolby at colbyconsulting.com
Thu Oct 1 14:40:07 CDT 2009
Robert, That is exactly the situation. These tables are entirely static, no insertions or deletions ever take place. Once the initial processing, there aren't even any updates. Read-only. John W. Colby www.ColbyConsulting.com Robert Stewart wrote: > 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 > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >