Back To Basics: Clustered vs NonClustered Indexes; what’s the difference?

SQL Server has two basics kinds of indexes. They are clustered and nonclustered indexes. There are some fundamental differences to the two which are key to understanding before you can master index tuning.

First the things that they have in common.

Both clustered and nonclustered indexes can be made up of more than one column. The columns are put in the index in the order you specify them in the CREATE INDEX statement (or the order they are shown in the UI). They are also sorted in this order as well. Indexes are first sorted on the first column in the index, then any duplicates of the first column and sorted by the second column, etc. You can have up to 16 columns specified as indexed columns.

Neither clustered or nonclustered indexes will guarantee the sort order of the data when it is being returned to you. If the order of the data matters to you, you should always sort the data with the ORDER BY clause in your select statement.

Both clustered indexes, and nonclustered indexes take up additional disk space. The amount of space that they require will depend on the columns in the index, and the number of rows in the table. The clustered index will also grow as you add columns to the table (keep reading, it’ll make sense later on).

Adding indexes (both clustered and nonclusterd) will increase the amount of time that your INSERT, UPDATE and DELETE statement take, as the data has to be updated in the table as well as in each index. If you have filtered indexes in SQL Server 2008 and the records you are updating are not included in all your indexes, SQL Server should only have to update the values in the indexes which the records are stored within.

Columns of the TEXT, NTEXT and IMAGE data types can not be indexed using normal indexes. Columns of these data types can only be indexed with Full Text indexes.

If you wish to rebuild your indexes online (without locking the table) and have Enterprise edition do not index TEXT, NTEXT, IMAGE, VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX) data types as including columns with these data types will require that you rebuild the index offline.

The total size of the key columns can not exceed 900 bytes.  Don’t forget that uni-code characters take up two bytes per character which will reduce the number of characters which your index can hold.

Clustered Indexes

SQL Server only supports a single clustered index per each database table.

Clustered indexes do not support included columns. This is because clustered indexes contain all the columns which aren’t in the index as included columns already. There is no way to override this. Columns with the data type of TEXT, NTEXT and IMAGE are not included within the clustered index. As with the normal table these values are stored out of bounds and only the pointer to the physical location on disk is stored within the index.

Clustered indexes must be placed within the same file group as the physical table which they are created against.

***Update***

Let me point out that SQL Server won’t throw an error if you try and put the clustered index on another file group.  SQL Server will let you do this, however the table will be moved to this file group as well.  The Clustered index and the physical table must be in the same file group.

*************

Nonclustered Indexes

Nonclustered indexes (also called “indexes”) are the normal indexes that are created on your tables. SQL Server supports 999 nonclustered indexes per table.

Each nonclustered index can contain upto 1023 included columns. Columns of the TEXT, NTEXT and IMAGE data types can not be included either.

There are more things that could be put in here, but this will get you a good start. If you think of anything that I’ve missed please feel free to post them below.

Denny