Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Clustered and Non-Clustered Indexing in SqlServer2005 Expand / Collapse
Author
Message
Posted Monday, August 18, 2008 1:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, February 1, 2013 4:55 AM
Points: 27, Visits: 41
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
Post #554115
Posted Monday, August 18, 2008 1:18 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 8:19 PM
Points: 1,393, Visits: 438
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.


Post #554117
Posted Monday, August 18, 2008 1:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, February 1, 2013 4:55 AM
Points: 27, Visits: 41
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
Post #554120
Posted Monday, August 18, 2008 1:34 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, July 10, 2014 2:20 AM
Points: 778, Visits: 5,946
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.
Post #554123
Posted Monday, August 18, 2008 1:54 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:14 AM
Points: 39,964, Visits: 36,319
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 2008, MVP
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

Post #554140
Posted Tuesday, September 9, 2008 1:33 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, February 1, 2013 4:55 AM
Points: 27, Visits: 41
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
Post #565949
Posted Thursday, November 10, 2011 1:57 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, September 19, 2014 12:27 AM
Points: 62, Visits: 328
just look this site
http://www.codeproject.com/KB/database/IndexIn2005.aspx
Post #1203325
Posted Thursday, November 10, 2011 3:00 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:14 AM
Points: 39,964, Visits: 36,319
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 2008, MVP
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

Post #1203349
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse