[dba-SQLServer] Indexing

artful at rogers.com artful at rogers.com
Fri Feb 16 11:56:59 CST 2007


Doubtless there is. In the meantime, here are a few guidelines:

An index is almost identical to a table. It contains a minimum of two columns, one containing the Primary Key (PK) of every row, and the other(s) containing the keys expressed in your index expression.

Every Foreign Key (FK) should be indexed.
Every PK will be indexed automatically. Your choice is whether to cluster the keys or not.
Data-modeling tools such as ERwin and PowerDesigner and DeZign often err in their creation of indexes, because they are unable to perceive the value of "covering" indexes. A covering index is an index on multiple columns, and is most useful when you always retrieve data in a certain pattern. For example, suppose two tables, Orders and OrderDetails. You would typically not interrogate this table by OrderDetailID. You would interrogate it by OrderID + OrderDetailID, most frequently, and occasionally by ProductID. But even in the last case, why would you do this except to discover who purchased a fan belt for a Bronco within the past two years? In that case, create a covering index consisting of ProductID + OrderID, which index could semi-immediately point you to Orders.CustomerID, thus identifying very quickly those customers who purchased Product 12345.

Arthur Fuller
Technical Writer, Data Modeler, SQL Sensei
Artful Databases Organization
www.artfulsoftware.com




----- Original Message ----
From: "Elizabeth.J.Doering at wellsfargo.com" <Elizabeth.J.Doering at wellsfargo.com>
To: dba-sqlserver at databaseadvisors.com
Sent: Friday, February 16, 2007 12:05:27 PM
Subject: [dba-SQLServer] Indexing


Dear List,

Is there a short treatise on indexing that any of you experts would
recommend?  


Thanks,

Liz






Liz  Doering
612.667.2447 

"This message may contain confidential and/or privileged information. If
you are not the addressee or authorized to receive this for the
addressee, you must not use, copy, disclose, or take any action based on
this message or any information herein. If you have received this
message in error, please advise the sender immediately by reply e-mail
and delete this message. Thank you for your cooperation"


_______________________________________________
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