Mark Breen
marklbreen at gmail.com
Thu Dec 3 15:53:08 CST 2009
Hello John, I would have thought the the most important thing to consider is what columns you will join on and what columns you will filter on. So, if you are only retrieving based on the PKID then I see no need to have any additional index. However, if you are joining on the PKID, but are filtering "Where Field47 = 'Ice Cream and Jelly', then you should have an index on Field 47 also. Regarding Clustered vs non Clustered, I believed those to be highly in a highly trafficed database where records are coming and going all the time. In those cases, the indexes can become fragmented in a similar way that hard disks get fragmented. More importantly, in a high volume, data entry system, I understand that it can dramatically increase performance if you do NOT cluster on the PK. (the following may be ten years out of date). The reason to Cluster on the Non-PK fields as that multiple records for Invoices 99, 100, 101, 102 would all be written to the same page within the db, and if that page is locked for invoice 103, then another operator cannot raise invoice 104 until 103 is completed. This was the logic I was thought in 1997 in clustering on another column such as CustomerId instead of InvoiceId. I really do not know if that is still relevant nowadays. Anyway, in your case, it seems all irrelevant, as you do a mass import and the immediately create your indexes. IOW, your indexes are in perfect condition. You probably only use them two or three times before you abandon that db for the next one. One last question, having just re-read your email, I see that you are talking about / hoping that a clustered index may keep the *fields *together. I would have assumed 99% confidently that the fields must always be kept together (as you say, what ever that might mean), but the clustering of indexes only relates to keeping *records *together, not columns together. So, if that is the case, you do not require a clustered index to keep columns together, ie, the must always travel together. I have no idea how to measure that. Am I totally off beam here, is the problem that I do not know what a cover index is? BTW, one last question, when you create new databases, do you create the db as 1 mb and allow it to grow, or do you initially create it as 47 gb, and then just populate it with what ever arrives each month. Is is faster to do your imports to a db that is already expanded up. If so, do you keep a handy, ready to go, empty 47 GB db lying around? thanks Mark 2009/12/3 jwcolby <jwcolby at colbyconsulting.com> > 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 > >