Cluster That Index - Part 2

Question

Cluster That Index - Part 2

Chris Hedgate

One Orange Chip

Points: 25041
More actions
October 5, 2003 at 12:00 am

#176791

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/chedgate/clusterthatindexpart2.asp
--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

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

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

ray_higdon SSCarpal Tunnel Points: 4589 More actions · Answer 1

Just read both of them, I am always amazed that there is always something new to learn about indexing. Excellent article, hesitant to trust that page splitting is so nominal (from first article) but have to do more testing.

Ray Higdon MCSE, MCDBA, CCNA

------------

Ray Higdon MCSE, MCDBA, CCNA

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 2

quote:
Just read both of them, I am always amazed that there is always something new to learn about indexing. Excellent article, hesitant to trust that page splitting is so nominal (from first article) but have to do more testing.

Thanks Ray, glad that you liked them. If you do any testing on page splits please notify me with your findings.

--

Chris Hedgate @ Apptus Technologies (http://www.apptus.se)

http://www.sql.nu

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 3

About the title, "Cluster that index - part 2", it should have been either simply "Forward pointers" or maybe part 3 och Cluster that index, since there is already a part 2 (http://www.sqlservercentral.com/columnists/chedgate/clusterthatindexparttwo.asp).

--

Chris Hedgate @ Apptus Technologies (http://www.apptus.se)

http://www.sql.nu

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

blaireau SSC Rookie Points: 36 More actions · Answer 4

I got a question:

If a data page is split, there is a pointer added to the original page to point toward the new page (forward pointer like you just explained). If this new page is split again, is there another Forward pointer added to this page? This would mean that we are now at left with 3 pages?

Is there a way to set SQL to automatically removed this forward pointer? In a job maybe?

BTW good short and simple article.

Eric Blais

Eric Blais
Blaireau47@hotmail.com
Web Developper
Always looking for a challenge

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 5

quote:
If a data page is split, there is a pointer added to the original page to point toward the new page (forward pointer like you just explained). If this new page is split again, is there another Forward pointer added to this page?

No, the original pointer would just be changed to point to the new location.

--

Chris Hedgate @ Apptus Technologies (http://www.apptus.se)

http://www.sql.nu

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

DCPeterson SSCoach Points: 15266 More actions · Answer 6

Good series of articles Chris. I especially appreciated that you got the "physical ordering" thing right. That is the source of much confusion, and if I were to make one recommendation it would be to emphasize the fact that clustered indexes do not maintain the physical ordering of data pages on disk.

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/