Asger Blond
ab-mi at post3.tele.dk
Thu Dec 3 14:22:45 CST 2009
If you want a covering index then don't redesign the clustered index to include that other field and don't build a composite non clustered index on that other field plus the PK field. All you have to do is to build a non clustered index on that other field. This single-field index will in fact cover both that field and the PK field. Reason: In SQL Server the leaf level of a non clustered index always includes the key values of the clustered index. Asger -----Oprindelig meddelelse----- Fra: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af jwcolby Sendt: 3. december 2009 17:17 Til: Dba-Sqlserver Emne: [dba-SQLServer] Do I need a cover index? I have a table with a unique PKID (int) and a bunch more fields. I created a clustered index so that (my understanding) the data is sorted by the PK and all the fields are physically stored "together" (whatever that might mean). Now... in actual use I will join that table to other tables using that PK and then select a single field from the table to include in the view. Obviously the PKID is not useful to me other than that it is indexed and used in the join. My question is, do I need to build a "cover index" that includes the PKID and that single field that I always use? Or is the fact that the data is already "stored together" give me access to that other field, and the cover index is not useful? I suspect that I should have the cover index but this is one of many gray areas in my knowledge. TIA for your assistance. -- 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