Clustered and Non-Clustered Indexing in SqlServer2005

  • 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

  • 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.

  • 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

  • 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.

  • 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
  • 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

  • just look this site

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

  • 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

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

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