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