clustered index and non clustered index in sql

Amudhan Kuppan

8y877SQL

anyone clear my doubt please

what is the difference between clustered index and non clustered index

clustered index means it reorders the way of column whatever iam give right?

and non clustered index means

please tell me i dont know

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Uttam Chaturvedi8y ago
Accepted answer
In short when we create a primary key on some column it is treated as a clustered index and determine the physical order of the rows.

We can have only one clustered index per table

whereas Non-Clustered is like a index of book. Data is stored in one place and index is stored in another place and index have the pointer to the storage location.

We can have more than one clustered index in a table.

Please follow below link for more details:

https://stackoverflow.com/questions/1251636/what-do-clustered-and-non-clustered-index-actually-mean?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa
+2
Amudhan Kuppan8y ago
thank you all
0
Shahriar Nikkhah8y ago
Hi Amudhan
I am sure that Uttam and Rajkiran have provided you the perfect answer that you need.

I might be better to see the field(s) and data type that you are sellecting and why?
we migh be able to give feed back.

anyways you ahve your answer
+1
Rajkiran Swain8y ago
Clustered Index
- A Table can have ONLY 1 Clustered Index.
- A Clustered Index always has Index Id of 0.
- A Primary Key constraint creates a Clustered Index by default.
- A Primary Key constraint can also be enforced by Nonclustered Index, You can specify the index type while creating Primary Key.
- If the table does not have Clustered Index it is referred to as a "Heap".
- The leaf node of a Clustered Index contains data pages of the table on which it is created.
- Clustered Index enforces a logical order on the rows. Rows are ordered based on Clustering Key.
Nonclustered Index
- Prior to SQL Server 2008 only 249 Nonclustered Indexes can be created. With SQL Server 2008 and above 999 Nonclustered Indexes can be created.
- Nonclustered Indexes have Index Id > 0.
- A Unique Key constraint created a Nonclustered Index by default.
- A Unique Key constraint can also be enforced by Clustered Index, You can specify the index type while creating Unique Key
- Nonclustered Index does not order actual data, It only orders columns present in the Nonclustered Index based on Index Key specified at the time of creation of Nonclustered Index.
- A table may not have any Nonclustered Indexes.
- The leaf nodes of a Nonclustered Index consists of Index pages which contain Clustering Key or RID to locate Data Row.
- When Clustered Index is not present leaf node points to Physical Location of the row this is referred to as RID. When a Clustered Index is present this points to Clustering Key (Key column on which Clustered Index is created).
+1