Differences between Clustered Index and Non Clustered Indexes // Clustered Vs Non Clustered Indexes

  

Character	Clustered Index	Non Clustered Index
1.       Max num to a Table	Only 1 max per table	Max  249 for 2005 Version Max  999 for 2008 Version
2.       Sort operation	table will be physically – sorted (on the actual disk) – by the values inside the Clustered-Indexed column	Non-clustered index has no effect on which the order of the rows will be stored.
3.       Data Storage	it actually stores row level data in the leaf nodes of the index itself	Non clustered indexes store both a value and a pointer to the actual row that holds that value
4.       Usage	Using a clustered index is an advantage when groups of data that can be clustered are frequently accessed by some queries. This speeds up retrieval because the data lives close to each other on disk. Also, if data is accessed in the same order as the clustered index, the retrieval will be much faster because the physical data stored on disk is sorted in the same order as the index.	Not Possible here.
5.       DisAdvantage	For DML operations the data sorting for each record will effects performance. If any change is made to a value of an indexed column, the subsequent possibility of re-sorting rows to maintain order is a definite performance hit.	No You can do DML operations as normal speed, because here the data is stored as last record, no sorting is happening here.
6.       Advantage	Much faster while retrieving the data from a table	Fast compare normal data retrieving process(compare to Clustered , Non Clustered index is slow)
7.       UNIQUE Ness	Clustered Index doesn’t enforce uniqueness on its filed. But if you include UNIQUE key word while creation time, then Clustered Index becomes UNIQUE CLUSTERED INDEX  and must contain Unique values only. Example: CREATE CLUSTERED INDEX bob ON T_Name( ID ) is not the same as CREATE UNIQUE CLUSTERED INDEX bob ON T_Name( ID )	Here we can create but that’s not a good idea to create UNIQUE NonClustered Index on a field of a table(bcz query takes too much time on Unique non clustered index scan)
8.       Object Space	Clustered Index won’t take extra space for creating Clustered Index on a field of a table(table level it self we can find Clustered Index)	For Non Clustered Indexes a separate Object will be created outside of the table with "NonClustered-field, Reference “ named fileds. So that query has to look 2 table objects while working with Non-Clustered Index, that is what it takes some extra time to retrieve data compare to Clustered Index
9.

Note:

SQL Server creates a unique clustered index when you define thePRIMARY KEY constraint on a table.

Pictures of Clustered & Non Clustered Indexes:

the above one is Non Clustered Index Pic

The above one is Clustered Index Pic

Clustered indexes VS non-clustered indexes

	CLUSTERED	NON-CLUSTERED
PROS	·         Fast to return large range of data ·         Fast for presorted results	·         Wide keys do not reflect on other indexes ·         Frequently updated key columns do not reflect on other indexes ·         Can be assigned on different FileGroup ·         Many non-clustered indexes per table ·         Smaller size than clustered indexes due to column subsets
CONS	·         Frequently updated key columns reflect on non-clustered indexes ·         Wide keys increase the size of the non-clustered indexes ·         Only one clustered index per table	·         Generally slower than clustered indexes due to bookmark lookup (except for covering indexes). ·         Not recommended for returning large data sets (except for covering indexes).