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:
– 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.

Check out Best Alternative to InfoPath -> Try Now

free sharepoint training

SharePoint Online FREE Training

JOIN a FREE SharePoint Video Course (3 Part Video Series)

envelope
envelope

About Bijay Kumar

I am Bijay from Odisha, India. Currently working in my own venture TSInfo Technologies in Bangalore, India. I am Microsoft Office Servers and Services (SharePoint) MVP (5 times). I works in SharePoint 2016/2013/2010, SharePoint Online Office 365 etc. Check out My MVP Profile.. I also run popular SharePoint web site SharePointSky.com

View all posts by Bijay Kumar →