8 Weeks of Indexes–Structure of an Index

This is the 4th post in my series of 8 Weeks of Indexes

In this installment, I hope to describe what makes up an index, how it is created and some of the more common options.

The Following table will be used to demonstrate my examples.

CREATE TABLE [dbo].[Patients]


    [ID] INT IDENTITY(1,1),

    [LastName] Varchar(50),

    [FirstName] Varchar(50),

    [HomeCity] Varchar(50),

    [HomeState] Varchar(2),

    [EyeColor] Varchar(30),

    [HairColor] Varchar(30),






If you look at the structure of the table above, I already have a Unique Clustered Index built in because of the CONSTRAINT/PRIMARY KEY clause.  So my table physically will be stored in numerical order on the ID column. This type of structure and index are useful for pulling individual records, if you know the ID of the row.

My first example will be of a non-clustered index. You can have up to 249 non-clustered indexes per table in SQL 2005 and SQL 2008 increased that limit to 999.

   1: CREATE NONCLUSTERED INDEX [IX_LastName] ON [dbo].[Patients]

   2: (

   3:     LastName ASC

   4: )

   5: ON [PRIMARY]

This is a simple index on the Patients table so you can query by Last Name. This tells me very plainly that I want to create an index called “IX_LastName” on the Patients table using the column “LastName” in Ascending alphabetical order and store that index on the [PRIMARY] file group. There are different options (MSDN) with creating a non clustered index.  The more common options are: (all quotes are from MSDN)

  • INCLUDE – “specifies the non-key columns to be added to the leaf level of the nonclustered indexes”. Usually these are other columns in your SELECT statements
  • WHERE – the ability to filter the index by a certain criteria to limit the number of rows returned 
  • WITH – this option has many different settings that can be applied to the index: these include the more common options PAD_INDEX, FILLFACTOR, SORT_IN_TEMPDB, DROP_EXISTING and ONLINE (see MSDN article for complete list)

The last command ON is a very cool feature.  This particular feature tells SQL where you want to physically store the index based on filegroups.  On very busy systems, it may be advantageous to store these on a separate drive in a separate file group.

CREATE NONCLUSTERED INDEX [IX_LastName2] ON [dbo].[Patients]


    [LastName] ASC,

    [FirstName] ASC


INCLUDE (     [EyeColor],


WHERE HomeCity='Baton Rouge'


The above code uses 3 common options and 4 common parameters for the WITH options. 

INCLUDE – adds the two columns to the leaf level so the query can easily return those results with index

WHERE – this will effectively return only the rows that are included in this filter.  Only patients in Baton Rouge will be included in the index.

WITH – The following index options applied are as follows:  FILLFACTOR will force SQL to only use 80% of space of the index page.  It is recommended to use PAD_INDEX=ON with FILLFACTOR because this will allow SQL to force let’s say 81% of the index page if 80% is not enough to store one row.  The ONLINE=ON is only available with Enterprise and Developer edition but this reduces the locks on the table to allow the data to be available.  And the last option is SORT_IN_TEMPDB=ON.  This does exactly what it say, it will copy the index to the tempdb, sort it there than write it back to its original location. This feature is VERY helpful if your tempdb is on a separate drive because SQL can READ/WRITE to 2 separate drives at the same time but not the same drive.

Well that is enough for today.  My mind can only take so much.  Next week I will be out of the office and away from computers so there is no chance to get a post out (not like I have been doing them every week anyway).

But I will endeavor to have Post #5 of my 8 Weeks of Indexes out the week of Aug 10th.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s