Clustered vs. Non-Clustered Index

The other day someone asked me a question about the difference between clustered and non-clustered indexes in SQL Server and why can you only have one clustered index in a table? It is one of those things that you know but often find it difficult to explain in a non-technical manner, at least that's how I felt. So, what is the difference and why can you only have one clustered index?

Clustered Index: Clustered index sort the data physically in the data table according to the keys in the clustered index. Basically all the data rows are physically sorted according to the clustered index. Since you can only sort data rows one way, there can only be one clustered index. The lowest level of clustered index are the data rows themselves. If you have a primary key defined in the table, then SQL Server automatically creates a clustered index on the primary key.
You don't have to have clustered index and the table without a clustered index stores the data on a heap. Data inserted in this table will return in the same order it was inserted in, unless you apply ORDER BY clause to your query which is SLOW!!!

Non-Clustered Index: A Non-Clustered index on the other hand is stored independently of the data rows' physical order. Think of this as a logical order of the data rows and hence there can be more than one non-clustered index. Non-clustered index's lowest level contains the keys used in the index and are a pointer to the actual data rows containing that key. This pointer is called a row location and its structure depend on whether the table has a clustered index or not. As mentioned earlier, if a table does not have a clustered index, the data rows are stored in a heap and in this case the row locator is simply a pointer to the row. For tables that do have a clustered index, the row locator is the key of the clustered index.

Both clustered and non-clustered indexes can be unique. Primary key by default is a unique index whether it is defined as a clustered or non-clustered index. Unique indexes ensure that no two rows can contain the same keys used in the unique index.

IGNORE_DUP_KEY: When creating a unique index, you can also use "IGNORE_DUP_KEY" hint. When a duplicate key is inserted, if IGNORE_DUP_KEY was specified, SQL will issue a warning and ignore the duplicate row, but if it was not specified, SQL server will issue an error message and roll back the entire insert. IGNORE_DUP_KEY is only allowed when you create a UNIQUE clustered or a non-clustered index.

FILL FACTOR: A Fill Factor defines how densely the index will be packed when it is created. If you are going to have many inserts and updates, you should use a lower fill factor to leave room for new data, which reduces the number of page splits that may occur when data is inserted. The space doesn't change when data is inserted or updated, only when index is created, so it is recommended to rebuild indexes periodically. If the table is rather static, create an index with a high fill-factor.

Indexes generally speed up the retrieval, update and deletion but often slow down inserts because every new record will have to be added to the index. Care should be taken in creating the number of indexes on a table, especially when data is frequently inserted.

While you can create some indexes during database design, often time it is not possible to know the table usage until it is out in the field. Once the database is out in the field and you want to optimize certain heavily used tables, you can do the following

• In SQL Management Studio (SQL 2005 or SQL 2008) go to Tools > SQL Profiler and run the trace for the tables that are heavily used.
• Collect some metrics and save the file.
• Go to Tools > Database Engine Tuning Advisor and load the workload file, select the database and start analysis.
• Review the recommendations and apply them to the appropriate tables.

I have generally found the tuning advisor to be a good tool, but be careful in applying all its recommendations. As always, do this in staging environment first, before applying to your production database.

As always, your comments are welcome and feel free to correct me if I made any mistake!

Creating Indexes using T-SQL

In my previous post, we discussed creating indexes using SQL Management Studio. You can achieve the same results using SQL Scripts.

Using T-SQL

1.  Create a Non-Clustered, Non-Unique Index

/****** Object:  Index [idx_Test]    Script Date: 02/05/2012 12:56:50 ******/
CREATE NONCLUSTERED INDEX [idx_Test] ON [dbo].[DTA_input]
(
[SessionID] ASC,
[GlobalSessionID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS  = ON,
ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

2. Create a Unique Non-Clustered Index

/****** Object:  Index [idx_Test]    Script Date: 02/05/2012 12:59:41 ******/
CREATE UNIQUE NONCLUSTERED INDEX [idx_Test] ON [dbo].[DTA_input]
(
[SessionID] ASC,
[GlobalSessionID] ASC
)WITH (PAD_INDEX  = OFF,
STATISTICS_NORECOMPUTE  = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS  = ON,
ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

HINTS / CLAUSE DEFINITIONS

PAD_INDEX
This option specifies whether you want to leave some space in each node (also referred to as page) for future inserts/updates. This is only useful when you specify a fill factor, because it uses the % specified in fill factor. Default is OFF.

STATISTICS_NORECOMPUTE
This flag determines whether index statistics are automatically recomputed. If you set it to ON, your SQL Query Optmizer may not be able to pick the optimal execution plan for any queries using this table.

SORT_IN_TEMPDB
Specifies that during index build/rebuild, intermediate sort results will be stored in tempDB. If your tempDB is on a different disk(s) than your production DB, it may reduce the time needed to create an index.

IGNORE_DUP_KEY
As I discussed in one of my previous post, when this option is ON and an attempt is made to insert a duplicate key, server issues a warning and ignores the duplicate row. If this option is OFF, server issues an error message and rolls back the entire INSERT. This clause can only be turned on if you have specified UNIQUE clause in your index.

DROP_EXISTING
This clause signals the SQL Server to drop and rebuild the pre-existing index with the same name. When you drop a clustered index, all non-clustered indexes must be rebuilt because they contain pointers to clustered index keys. This clause is extremely useful when dropping a clustered index on the table that also has non-clustered indexes. The non-clustered indexes are rebuilt only once and only if the keys are different.

ONLINE
This clause needs some explanation. When this clause is ON, it means database can be online i.e. being used for other processes while Index is being built or rebuilt. In other words, Index operations do not need exclusive lock. Default is OFF meaning indexing operation requires exclusive lock on the table. This was a nice enhancement in SQL 2005. Prior versions required exclusive lock for index operations. Some of columns such as VARCHAR(MAX) cannot be indexed while online.

ALLOW_ROW_LOCKS
This clause specifies whether data row is locked when performing operations on the indexed keys. When performing OLTP operations, it is a good practice to leave turn this ON.

ALLOW_PAGE_LOCKS
This clause determines whether the entire data page will be locked during index operations.

If both clauses are OFF, SQL Engine will not lock data page or data rows instead entire table will be locked during the operation.

Generally, it is a good idea to leave the defaults alone unless you have a very good reason to change them.

Thank you and as always, your comments are welcome!

Creating Indexes using Management Studio

In my previous two posts, I wrote about the indexes in general and why you should create them. In this blog, I will walk through creating an index using Visual Studio Management.

Note: I have used SQL 2008 Management Studio, but the process is more or less same in SQL 2005.

Using SQL Management Studio

1. Open Management Studio and expand the database > table you want to index.

2. Select the appropriate name. I generally add a prefix to the name "idx_". Also select whether the index is a clustered or non-clustered index. (Remember, only one clustered index is allowed in a table). Check Unique checkbox if you want the index to not allow duplicate keys.

3. Click on "Add" and select the columns you want to index. As we discussed in previous post, CHAR/VARCHAR are not good candidate keys, unless you have to use them. The order is also very important. The key which will have most duplicate data should be ranked first.

4. Go to Options and adjust the settings as needed. Defaults are generally OK here but you may want to adjust fill factor. See my previous post to learn more about fill factor.

That's all there is to it. Click on OK and you are done!

As always, feel free to leave me comments!

Thank you