SQL Server Indexing Design

whether primary keys should be clustered, when to use filtered indexes and what to consider when using the Fill Factor. Many different aspects of indexing, like having an index development lifecycle, to the order in which columns should be placed in your indexes. Having a good set of indexes on your SQL Server database is critical to performance. Efficient indexes don’t happen by accident; they are designed to be efficient.

Primary Key:

What is a primary key? A primary key is a value that uniquely identifies a record. This key value can be made up of one or more columns. In order to enforce a primary key constraint the SQL Server database engine creates a unique index for the primary key. When creating the primary key, SQL Server will create the primary key as a clustered index, by default, if the table does not already have a clustered index on it and you do not specify it to be created as a non-clustered index.

Should all primary keys be a clustered index? As with many questions when it comes to database design, “it depends”. As described in my “Yes and No’ts of Database Indexing” article I said clustered indexes should be frequently used for retrieving data and should be highly selective and the index value should be as short as possible. A primary key by definition meets the highly selective criteria. However, a primary key might not be frequently used to retrieve data or be short in length.

Filtered Index:

Filtered index is a new concept that was introduced in SQL Server 2008. A filtered index is a special non-clustered index that is defined on a well-known subset of column values. In other words, a filtered index is an index that is created only for a specific set of column values. For an example say you have a table that has a list of employees that live in different countries, which is identified by the Country column. If you created a normal non-clustered index on the Country, the index would contained all the different County values for all Employees, whereas, with a filtered index, you could create an index where the values in the index would only contain a subset of defined country values like, Sweden, Norway and India.

Filtered indexes are useful when you have queries that only need to look at a small set of records. Filtered indexes use less disks space because they are only associated with a small set of the records within a table.

That query would have to do less I/O to process through the filtered index to find employees that live in Sweden, than it would have to do if it processed through a normal non-clustered index that contain employees for all countries.

Here is the syntax for creating my country filtered index:

CREATE NONCLUSTERED INDEX FilteredCountries     ON HR.Employees (Country)     WHERE Country IN (‘Sweden’,’Norway’,’India’)

As you can see, it is like creating a normal non-clustered index, but includes a WHERE clause to filter the column values contained in the filtered index.

Fill Factor:

When creating indexes you need to take into account how the key values in an index will change over time. Will there be new values added in the middle of the sorted list of index values, or only at the end? Will values be removed over time when records are deleted? Your answers to these kinds of questions related to your index values, will determine how you should set your index Fill Factor.

What is the Fill Factor? The index Fill Factor is a value, expressed as a percentage, for how full each index page should be populated when the index is built. You need to set the Fill Factor appropriately based on how your index values will change over time. Setting a Fill Factor appropriately keeps SQL Server from having to create page splits when trying to insert new values into the middle of an index.

What should you choose for a Fill Factor? If you were not expecting to add any values to your table over time then a Fill Factor of 100 would be appropriate and the index pages would be filled completely when the index was populated. If your table has a fair number of updates and inserts of new values happening all the time then you might consider setting a Fill Factor between 50 and 80% to allow more values to be placed in existing pages without causing page splits. I used to set a value 80 and it’s depend on index value and page split.

Indexing By Design:

Efficient indexes, that optimize your application performance do not normally happen by accident, but are designed to be efficient. Knowing how your application is querying, loading, and updating your data are critical to designing a good indexing structure.


Any comments will be learning point.