Wednesday, August 12, 2009

SQL Server Clustered and Non clustered indicies

Indicies are the best performance gain you can get from a table in any database.

For SQL Server indicies can be classified into clustered and non-clustered. There is a facinating article that explains the difference to be found at:
http://www.sql-server-performance.com/articles/per/index_data_structures_p1.aspx

Here's the crux:
For speeding up single records and minimizing index space use non-clustered.
If select performance (rather than insert performance) is paramount... use clustered idices.

Note: A table can have only one clustered index. It is recommended to keep that index same as the primary key :)

So when adding indicies you will generally be adding non clustered indices. Enjoy !