Powered by Blogger.

Saturday, February 15, 2014

Difference between clustered index and non clustered index in sql server 2008

In this article we will discuss about what is the difference between clustered index and non clustered index in sql server 2008.

You can also check my last articles on Asp.Net statemanagement interview questions, Difference between primary key and unique key in SQL Server and Change browser in visual studio 2012.

Before going to find out the difference between these two, it is very much important to know what is Indesx is.

- Index is a database object, which can be created on one or more columns.
- Index will improve the performance of data retrieval.

There are 2 types of Index: Clustered indexes and Non-clustered indexes.

Clustered indexes:
- A clustered index is a special type of index that reorders the way records in the table are physically stored.

- A table can contain only one clustered index.

- This is faster to read than non clustered index since data is physically stored in index order.

- When a primary key is created a clustered index is automatically created as well.

Non-clustered indexes:
- There can be multiple non-clustered indexes possible, it can be up to 249 nonclustered indexes are possible for each table or indexed view.

- Foreign keys should be non-clustered.

- It does not re-order the actual table data.

- It is good practice to dd non-clustered indexes for queries that return smaller result sets.