We’ve been talking about keys and indexes for the last few days. The keys you choose, and the indexes you create implement the basic business rules of your database when it comes to relationships between tables, and enforcing uniqueness of data. These rules may be defined as table constraints, but the implementation used by the SQL Engine is always based on an index.
For example, you can define a primary key as a table constraint. If you specify it as CLUSTERED, or don’t specify it as NON CLUSTERED, then the primary key will be implemented as a UNIQUE CLUSTERED index. If you specify the primary key constraint as NON CLUSTERED, then it will be implemented as a UNIQUE NON CLUSTERED index. That is the reason you can implement alternate keys for a table, simply by creating a UNIQUE index with the desired columns.
You may also define a foreign key for data in a table relating to the primary key of another table. In this case, there is no index created to enforce the data entered. However, when data is entered into a table, the values must exist in the data of the table containing the primary key.
Depending on usage, you will likely create a NON CLUSTERED index on the child table with the column(s) related to the foreign key table. The reason for this index is that you will often write queries from the perspective of the primary key table, and then lookup matching child records. A good example of this would be Customers and Purchase Orders. A Purchase Order would have a foreign key to the Customer table. So, when you select a Customer, you would get all the Purchase Orders having a foreign key value matching the Primary Key of the Customer table record.
Without respect to the implementation of the indexes, CLUSTERED or NON CLUSTERED, the health of the index has the greatest impact on performance over time. Indexes, by nature of the change in data, constantly fragment. If the data for an index exceeds the current available space on a page, then the data in the page must be split (this is a very simple description of a complicated process). What this means is that data must be located elsewhere, and that the next available space may not be adjacent to the current page being split, resulting in data fragmentation. Adding, Deleting, and Updating records result in constant re-alignment of indexes, and fragmentation increases.
As you may see, indexes are the life blood of database performance. When they are fragmented, incorrect or even worse, un-used, your performance will suffer. Tomorrow we’ll talk about keeping those indexes tuned up.
Cheers,
Ben