SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Clustered and Non-Clustered Indexing in SqlServer2005


Clustered and Non-Clustered Indexing in SqlServer2005

Author
Message
poornima.s_pdi
poornima.s_pdi
SSC Veteran
SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)

Group: General Forum Members
Points: 207 Visits: 129
Hi,
I want to know the details about Clustered and Non-Clustered Index in SqlServer2005.How to create Clustered index and how to use?
Please help to know this concept...

Thanks in Advance,
With Regards,
Poornima
michaela
michaela
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1541 Visits: 440
It is hard to give a short answer to your question as indexes are the core of sql server databases. You can srat with BOL, also look on internet for specific details. In short, a non-clustered index makes sense only if a clustered index exists: in a table that has a clustered index, the data is actually stored in the order of the index. What the bookmarks of the non-clustered index (read in BOL for definition) point to depends on if the table also has a clustered index or not.



poornima.s_pdi
poornima.s_pdi
SSC Veteran
SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)

Group: General Forum Members
Points: 207 Visits: 129
Hi Michaela,
Thanks for your reply.
I am need of how to create Clustered Index,What is their use and how to use them.
I already go through some online articles. In that, they have given
DBCC ind(@DBID, @TableID, -1).But i cant able to execute this query in Sqlserver2005.I am getting 'Could not find database 'dbid'. Check sys.databases.' error while executing this query.So I thought of this site to be the best help for me to know about Clustered Index Concept in Detail.Waiting for reply.


With Regards,
Poornima
thasteve
thasteve
SSC Eights!
SSC Eights! (856 reputation)SSC Eights! (856 reputation)SSC Eights! (856 reputation)SSC Eights! (856 reputation)SSC Eights! (856 reputation)SSC Eights! (856 reputation)SSC Eights! (856 reputation)SSC Eights! (856 reputation)

Group: General Forum Members
Points: 856 Visits: 5973
The difference is that a clustered index sorts the data of the table physical so it's more efficient on columns that are searched alot or you try to get ranges of the table using this column or you sort by the column or when you have a unique column and try to get single rows of it.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87623 Visits: 45272
DBCC IND is an undocumented command that shows internal details of the index. It's not really useful to someone getting started.

I would suggest you read the sections in books Online on indexes (or follow the below links for the MSDN articles) and then post if you have specific questions.

http://msdn.microsoft.com/en-us/library/ms189271(SQL.90).aspx - All about indexes
http://msdn.microsoft.com/en-us/library/ms180978%28SQL.90%29.aspx - Table and index architecture

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


poornima.s_pdi
poornima.s_pdi
SSC Veteran
SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)

Group: General Forum Members
Points: 207 Visits: 129
Hi GilaMonster,

Thanks for ur reply.I need to learn step-by-step way to use clustered and Non-Clustered Indexing.From the sites given by you in the previous post, I understand little bit about clustered Indexing and Non-Clustered indexing but still i have doubts. If there is UniqueIdentifier then what is the use of Creating Clustered Indexing...Plz guide me to create and use Clustered Indexing step-by-step.


Thanks in Advance,
With Regards,
Poornima
Shanmuga Raj
Shanmuga Raj
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 Visits: 344
just look this site
http://www.codeproject.com/KB/database/IndexIn2005.aspx
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87623 Visits: 45272
shanmugaraj-307582 (11/10/2011)
just look this site
http://www.codeproject.com/KB/database/IndexIn2005.aspx


I would suggest people don't read that. It has a number of mistakes in it. The main one being that the author of that describes the clustered index as a binary tree. SQL indexes are not binary trees, they are balanced trees. Since most of the article explains how a binary tree index works, it's mostly wrong.

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search