Dueling Indices

[TheSQL]

I'm working with a database wherein someone has created two indexes on exactly the same column. The column is the primary key - the first index is clustered, the second is non-clustered. Both are single-column indices.

I'm trying to think of a valid reason why you would need two indices on the same column (before I just delete the non-clustered index and move on with my life) ;-), but I'm coming up blank. Any ideas?

 

[mrdenny]

If it's SQL 2005 you could have other columns included in the index.

It's possible that a nonclustered index seek could be faster than a clustered index seek. In a clustered index all the values of the row are stored in the clustered index. This would cause more pages to have to be loaded into memory when seeking through the index when using the clustered index. Non-clustered indexes do not have this issue as only the indexed values are stored within the data page of the index so less pages must be loaded.

The same is also true with index scans, but it's more important when scans as the entire index must be loaded from disk into memory.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog