Clustered and Non-Clustered Indexing in SqlServer2005

Question

Clustered and Non-Clustered Indexing in SqlServer2005

poornima.s_pdi

SSCrazy

Points: 2720
More actions
August 18, 2008 at 1:01 am

#197047

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

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

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

michaela SSCertifiable Points: 5649 More actions · Answer 1

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 SSCrazy Points: 2720 More actions · Answer 2

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 SSCrazy Points: 2707 More actions · Answer 3

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.

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

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 SSCrazy Points: 2720 More actions · Answer 5

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 Ten Centuries Points: 1178 More actions · Answer 6

just look this site

http://www.codeproject.com/KB/database/IndexIn2005.aspx

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

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