Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sent_sara
Constraint Violating Yak Guru
377 Posts |
 Posted - 2008-07-07 : 01:14:58
|
| Hi friends,just i need to know the basic fundamental of clustered index..can any one help????Leaf Node of the Clustered index contains the actual data pages.Incase of Non clustered index Leaf node contains the pointer to the data page.I need to know whether non clustered index refers to the pointer to the data page of clustered index?if so,incase if there is no clustered index ,how the non clustered index refers? |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-07-07 : 01:44:24
|
| On your first question, yes, the non-clustered index points to the clustered index leaf.If there is no clustered index, then the non-clustered index points to the records in the table itself. |
 |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2008-07-07 : 01:59:04
|
| A good way to think about the difference between clustered ad non clustered indexes is : what else is in the leaf level.i.e the clustered index also has the data pages whereas the non clustered index does not have themJack Vamvas--------------------Search IT jobs from multiple sources- http://www.ITjobfeed.com |
|
|
|
sent_sara
Constraint Violating Yak Guru
377 Posts |
Posted - 2008-07-07 : 03:03:05
|
txs for ur reply RickD,OK i agree what u say,suppose if i drop a clustered index ,what happens to the reference of non clustered index whether automatically the non clustered index refers to the table????quote:
Originally posted by RickD
On your first question, yes, the non-clustered index points to the clustered index leaf.If there is no clustered index, then the non-clustered index points to the records in the table itself.
|
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-07-07 : 04:05:29
|
| Have you tried it? What else do you think it would refer to? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-07-07 : 04:09:42
|
| The table goes back to being a heap and the leaf-level entries of the NC index point to the rows in the heap.That's why changing the clustered index of a table with lots of nonclustered indexes is so time consuming. All of the NC indexes have to be rebuilt.--Gail ShawSQL Server MVP |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-07-07 : 11:20:20
|
| Remember in SQL 2000,If you rebuilt Non-unique clustered index,all Non-clustered are rebuilt as well.But in SQL 2005, they are not rebuilt(NC indexes). |
|
|
|
|
|
|
|
|
Non Clustered index