Interesting Facts About Database Indexes

Interesting Facts About Database Indexes

• Create Clustered Indexes on the tables’ Natural Keys. Natural Keys, are the fields that best identify the row’s data. For example, the Primary Key used for a table might be a Unique Identifier or a Big Int, but I might access data through a combination of columns. For a car these columns could be Year, Make, Model and VIN. When you create the Clustered Index, order the columns by their selectivity and use this same order when you query for the information.
• Create Non-Clustered Indexes on your Primary Keys
• Create Non-Clustered Indexes for all Foreign Keys
• Create Non-Clustered Indexes for columns that are used in WHERE, ORDER BY, MERGE, JOIN and other clauses that require matching data.
• Create Filtered INDEXES to create highly selective sets of keys for columns that may not have a good selectivity otherwise.
• Use Covering INDEXEs to reduce the number of bookmark lookups required to gather data that is not present in the other INDEXES.
• Covering INDEXES can be used to physically maintain the data in the same order as is required by the queries’ result sets reducing the need for SORT operations.
• Covering INDEXES have an increased maintenance cost, therefore you must see if performance gain justifies the extra maintenance cost.
• If a CLUSTERED INDEX is present on the table, then NONCLUSTERED INDEXES will use its key instead of the table ROW ID.
• To reduce the size consumed by the NONCLUSTERED INDEXES it’s imperative that the CLUSTERED INDEX KEY is kept as narrow as possible.
• Physical reorganization of the CLUSTERED INDEX does not physically reorder NONCLUSTERED INDEXES.
• SQL Database can JOIN and INTERSECT INDEXES in order to satisfy a query without having to read data directly from the table.
• Favor many narrow NONCLUSTERED INDEXES that can be combined or used independently over wide INDEXES that can be hard to maintain.
• NONCLUSTERED INDEXES can reduce blocking by having SQL Database read from NONCLUSTERED INDEX data pages instead of the actual tables.