| SQL Server 2005 allows indexes to be created with included non-key columns (in non-clustered indexes). This development has encouraged me to start rethinking indexes (not automatically differentiating between clustered and non clustered indexes). e.g "table" contains one or greater key columns and one or greater non-key columns (columns covered by the index allowing the query processor to derive the values from the index rather than having to check another index or table). At minimum a table contains an index, If the the index contains key columns it's a clustered index, if there are no key columns it's defined as a heap. It's useful to think of this index as a "table" , as it acts as a container for for all the data. Therefore an index contains: 1)none or greater key columns , 2)none or greater non-key columns , 3)a "row locator" for evry row linking the index row with the row in the table. The row locator is one of the following 3: 1)the clustered key value if there is a unique clustered index on the table,2) the clustered key value plus an internal unique identifier, if there is a nonunique clustered index on the table, 3)if there's no clustered index,an internal row identifier for a row in the heap An index explored by an ORDER BY or a WHERE on specific value is made useful by containing the columns in the ORDER BY or WHERE. An efficient query process is a balancing act between narrow indexes facilitating efficient searching and indexes that can cover the relevant joins, ORDER BY etc that allow the query to return relevant results.The key point to develop a mindset that makes you think about a "table" and related indexes in a similar way - i.e containers for non-key columns, key columns and row locators. |
