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 '10 at 09:39 AM in Default

OracleApprender gravatar image

OracleApprender
771 68 73 75

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

7 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 '10 at 08:58 AM

OracleApprender gravatar image

OracleApprender
771 68 73 75

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 '13 at 04:18 AM Jeff Moden
(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 '10 at 01:04 AM

KillerDBA gravatar image

KillerDBA
1.5k 8 9 10

(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 '10 at 07:57 AM

Sakshi Maurya gravatar 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 '12 at 06:13 AM

swati_kadam gravatar image

swati_kadam
0

(comments are locked)
10|1200 characters needed characters left
As we can create only one primary key on table same way we can create only one clustered index on table. That means Oracle automatically creates clustered index on primary key column. As we can create many unique indexes on table same way we can create many non-clustered indexes on table. Primary key -- Clustered Index Unique Index -- Non clustered Index
more ▼

answered Sep 03 '12 at 06:18 AM

swati_kadam gravatar 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x341
x79

asked: Jan 18 '10 at 09:39 AM

Seen: 36050 times

Last Updated: Nov 30 '13 at 01:47 AM