cluster and non-cluster index

Question

can any body answer this question ? In what cases developer use cluster and non cluster index .
suppose 40,000 rows are there in table .In that case which index will it use ?
i have great confusion to know this concept .i have searched so many blogs ,msdn sites bt i couldn't knw the difference how to correlate.
like cluster index,non cluster ,heap

Answer 1

Good morning,

In most part of the cases tables with clustered index would outperform heap tables. So it's usually the good practice to have one. Which one to have is the different question.

There is the presentation: "Everything you always wanted to know about indexes but were afraid to ask" available for download from: http://aboutsqlserver.com/presentations/ This should give you some ideas about internal structure of the indexes, how sql server uses them and finally have a few guidelines for index strategy design. Hope it would help.

---

Thank you!

Dmitri V. Korotkevitch (MVP, MCM, MCPD)

My blog: http://aboutsqlserver.com

Answer 2

Hi,

Check the below link to know more detail

http://social.msdn.microsoft.com/Forums/en-US/sqlkjmanageability/thread/ff7d1b60-2872-4aaa-b705-df95ca9cdd94

---

PS.Shakeer Hussain

Answer 3

You can learn more about Index Tuning and how you can use clustered and non-clustered indexes at my blog 

http://www.sql-server-performance.com/2013/sql-server-index-tuning/

If any further help needed about a specific expensive query , you can share here to be a good sample for index tuning  

---

Shehap (DB Consultant/DB Architect) Think More deeply of DB Stress Stabilities

Answer 4

Hi ,

You can say it 's a basic rule of thumb that use no clustered indexes when small amounts of data will be returned and clustered indexes when larger result sets will be returned by your query. On-Clustered index is depends upon the query running against the table. There is no ideal index structure available. It all depends upon the kind of application you have and query you run.  You may also do a live search on Covering Indexes.

Click on below link :

http://msdn.microsoft.com/en-us/library/ms190639(SQL.90).aspx

http://msdn.microsoft.com/en-us/library/ms179325(SQL.90).aspx

---

Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

Answer 5

IMO, here are the default rules for indexing a relational design:

1) Have a clustered primary key on every table, plus a unique non-clustered index on every alternate key.

2) Then add a non-clustered index on every foreign key that is not also one of your alternate keys.  

Any other physical design decisions (additional indexes, compression, partitioning) should be added later in response to performance testing.

David

---

David http://blogs.msdn.com/b/dbrowne/