[dba-SQLServer] Do I need a cover index?

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
>
>



More information about the dba-SQLServer mailing list