Clustered Index and Non-Clustered Index

Question

Clustered Index and Non-Clustered Index

lokesh.shukla-634965

SSC-Addicted

Points: 470
More actions
January 2, 2015 at 3:11 am

#74778

1). What is the difference between Clustered Index and Non-Clustered Index?
2). How these both indexes are related?
3). A table with Clustered Index and another table with Non-Clustered Index, which one will perform better and why?
4). How these two indexes affect tables (in DML)?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Divine Flame SSCoach Points: 15943 More actions · Answer 1

lokesh.shukla-634965 (1/2/2015)
1). What is the difference between Clustered Index and Non-Clustered Index?
2). How these both indexes are related?
3). A table with Clustered Index and another table with Non-Clustered Index, which one will perform better and why?
4). How these two indexes affect tables (in DML)?

1.

Clustered Index:

When a Clustered Index is created on a table, the table data is physically sorted as per the clustered index key. That is the reason you can have only one Clustered Index on a table.

Non-Clustered Index:

The index pages of Non-Clustered index contain two things i.e. index key & a pointer to the particular data row in the table. This pointer might either be a Row Id (File Id + Page Id + Row Slot)if the table is a heap OR the clustered index key value if a clustered index has already been created on table. You can have 999 non-Clustered indexes on a table (SQL Server 2008 & above).

2.

See answer number 1 (Non-Clustered index).

3.

If we are talking about an index created on the same column, Clustered index tends to perform better. However there are various other things that affect how the query\index will perform such as statistics of the table, SARGability & whether or not index is being used by the query. Creating random index is only going to make matter worse.

4.

Indexes do affect the DML operation, however generally the performance gain in read operation is way more high than the penalty incurred in write operations. In case your table is going to receive write activity more frequently than the read activity then the index should be created with much more caution.

Must Read:

http://www.sqlservercentral.com/articles/Indexing/68439/

Sujeet Singh

Divine Flame SSCoach Points: 15943 More actions · Answer 2

By the way your post should be opened under General SQL Server Questions not under "Discuss Content Posted by Steve Jones" ;-).

Sujeet Singh

LutzM SSC Guru Points: 107049 More actions · Answer 3

A slight correction regarding the clustered index:

The sort oder is not physical (in terms of sequential location on a spindle), but logical.

See for a great explanation. (You might want to read all articles of that series)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

lokesh.shukla-634965 SSC-Addicted Points: 470 More actions · Answer 4

Thanks Sujeet, for your nice comments and the link provided.

But, my 2nd question is still not better understood (How clustered index and non-clustered index are related?).

Can you elaborate it more?

LutzM SSC Guru Points: 107049 More actions · Answer 5

lokesh.shukla-634965 (1/3/2015)
Thanks Sujeet, for your nice comments and the link provided.
But, my 2nd question is still not better understood (How clustered index and non-clustered index are related?).
Can you elaborate it more?

Did you have a look at the series of articles I recommended?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 6

These look like interview, homework, or test questions. I strongly recommend that you open "Books Online" (the help system for SQL Server) and start reading all you can about indexes because just knowing the answers to the questions you posted won't help you in your job. Only a deep understanding will do that. Use these questions as the first part of a study guide rather than an end to the means.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Divine Flame (1/2/2015)
When a Clustered Index is created on a table, the table data is physically sorted as per the clustered index key.

Logically. Not physically. Same as nonclustered indexes

If the clustered index enforced the physical sort order, then a clustered index would always have 0% logical fragmentation, since fragmentation is a measure of how the logical and physical orders differ.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Divine Flame SSCoach Points: 15943 More actions · Answer 8

GilaMonster (1/4/2015)
Divine Flame (1/2/2015)
When a Clustered Index is created on a table, the table data is physically sorted as per the clustered index key.
Logically. Not physically. Same as nonclustered indexes
If the clustered index enforced the physical sort order, then a clustered index would always have 0% logical fragmentation, since fragmentation is a measure of how the logical and physical orders differ.

Thanks Gail & Lutz for correcting me there.

Sujeet Singh