x

What is the basic difference between clustered and a non-clustered index?

Could anyone explain the differences between clustered and a non-clustered indexes to me?. Please let me know if there are any specific requirements to be used in special cases. Thank you.

more ▼

asked Jan 18, 2010 at 09:39 AM in Default

avatar image

OracleApprender
771 73 75 79

(comments are locked)
10|1200 characters needed characters left

10 answers: sort voted first

The difference is that, Clustered index is unique for any given table and we can have only one clustered index on a table. The leaf level of a clustered index is the actual data and the data is resorted in case of clustered index.

Whereas in case of non-clustered index the leaf level is actually a pointer to the data in rows so we can have as many non-clustered indexes as we can on the db.

more ▼

answered Jan 25, 2010 at 08:58 AM

avatar image

OracleApprender
771 73 75 79

Actually, the leaf level contains data that is the same as it is in the table and the pointer. If all of the data requested by the query is located in the leaf level of the NCI, then the query uses the data directly from the index instead of from the table.

Nov 24, 2013 at 04:18 AM Jeff Moden
(comments are locked)
10|1200 characters needed characters left

Gosh, there's a lot of bad info on this on this thread.

First, a Clustered Index does not have to be unique and it does not have to be the Primary Key although it usually helps to be both. There is no such thing as a "Primary Index" in SQL Server although the Primary Key will create an index as does any UNIQUE constraint.

It IS true that there can only be one Clustered Index on a table and that's because the leaf level of the Clustered Index is actually the table data itself. All other indexes are, by definition, Non-Clustered Indexes and they contain copies of the key columns and any INLUDE columns in the leaf level of the Non-Clustered Index.

One of the most important parts about Clustered Indexes is that each and every Non-Clustered Index contains the key columns from the Clustered Index.

This is not a trivial question and I've only scratched the surface here. My very strong recommendation is to open up Books Online and do a deep study of Heaps, Clustered Indexes, and NonClustered Indexes before you look at anything else on the Internet on this subject.

more ▼

answered Nov 30, 2013 at 01:47 AM

avatar image

Jeff Moden
1.9k 3 6 12

(comments are locked)
10|1200 characters needed characters left

Here's a little intro...

Clustered and Non-Clustered Indexes

It's SQL Server-specific.

more ▼

answered Jan 19, 2010 at 01:04 AM

avatar image

KillerDBA
1.5k 9 11 14

(comments are locked)
10|1200 characters needed characters left

Cluster index sorts the data with respect to the column on which it is applied, where as non-clustered index do not, it is just a logical sorting. Thus we can have only single clustered index and multiple non-clustered index on a table. Primary index on a table is clustered index. By default CREATE INDEX statement creates a non-clustered index

more ▼

answered May 25, 2010 at 07:57 AM

avatar image

Sakshi Maurya
1

(comments are locked)
10|1200 characters needed characters left

When we create primary key on table,oracle automatically creates Clustered index on that column.As we can create only one primary key on table,like we can create one and only one clustered index on table.

When we create any normal index on column it is by default Non clustered index. As we can create many unique indexes on table,like we can create many non clustered indexes on table.

more ▼

answered Sep 03, 2012 at 06:13 AM

avatar image

swati_kadam
0

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x408
x93

asked: Jan 18, 2010 at 09:39 AM

Seen: 90966 times

Last Updated: May 10 at 06:57 AM

Copyright 2016 Redgate Software. Privacy Policy