What is the difference between Cluster and Non cluster Index? - Site Owners Forums

Shivangi Panwar · 04-05-2016, 10:09 PM

A clustered index reorders the way records in the table are physically stored. There can be only one clustered index per table. It makes data retrieval faster.

A non clustered index does not alter the way it was stored but creates a completely separate object within the table. As a result insert and update command will be faster.

indianlawwatch · 04-06-2016, 05:40 AM

Nice Thread thanks for Sharing shivangi

brknny · 04-19-2016, 05:16 AM

A clustered index actually describes the order in which records are physically stored on the disk, hence the reason you can only have one. A Non-Clustered Index defines a logical order that does not match the physical order on disk.

Alizine · 05-20-2016, 01:51 AM

Clustered Index

Only one per table
Faster to read than non clustered as data is physically stored in index order

Non Clustered Index

Can be used many times per table
Quicker for insert and update operations than a clustered index

Both types of index will improve performance when select data with fields that use the index but will slow down update and insert operations.

Because of the slower insert and update clustered indexes should be set on a field that is normally incremental ie Id or Timestamp.

SQL Server will normally only use an index if its selectivity is above 95%.

04-05-2016, 10:09 PM	#1
Shivangi Panwar Registered User Join Date: Nov 2015 Posts: 127	What is the difference between Cluster and Non cluster Index? A clustered index reorders the way records in the table are physically stored. There can be only one clustered index per table. It makes data retrieval faster. A non clustered index does not alter the way it was stored but creates a completely separate object within the table. As a result insert and update command will be faster. __________________ To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts. To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts. To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts. To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts. To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

04-06-2016, 05:40 AM	#2
indianlawwatch Registered User Join Date: Feb 2016 Posts: 44	Nice Thread thanks for Sharing shivangi __________________ To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts. To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts. To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts. To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts. To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts. To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

04-19-2016, 05:16 AM	#3
brknny Registered User Join Date: Feb 2016 Posts: 110	A clustered index actually describes the order in which records are physically stored on the disk, hence the reason you can only have one. A Non-Clustered Index defines a logical order that does not match the physical order on disk. __________________ To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts. To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts. To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts. To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts. To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

05-20-2016, 01:51 AM	#4
Alizine Registered User Join Date: May 2016 Posts: 14	Clustered Index Only one per table Faster to read than non clustered as data is physically stored in index order Non Clustered Index Can be used many times per table Quicker for insert and update operations than a clustered index Both types of index will improve performance when select data with fields that use the index but will slow down update and insert operations. Because of the slower insert and update clustered indexes should be set on a field that is normally incremental ie Id or Timestamp. SQL Server will normally only use an index if its selectivity is above 95%.

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)