Cluster That Index! Part Two

Question

Cluster That Index! Part Two

Chris Hedgate

One Orange Chip

Points: 25041
More actions
July 19, 2003 at 12:00 am

#357945

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/chedgate/clusterthatindexparttwo.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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Hi Chris,

quote:
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/chedgate/clusterthatindexparttwo.asp

this is no comment on your article, but on your homepage.

The link to your discussion forum yields a

Fatal error: Call to undefined function: pg_connect() in /var/www-sql.nu/phpBB/db/postgres7.php on line 79

error.

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

kyleburke SSC Veteran Points: 231 More actions · Answer 2

Great Article. One addition comment about page splitting from your example. If your are worried about page slitting because of heavy inserts to a table put the clustered index on the identity column. Since the clustered index will be order by the data, if you put a clustered index on an identity column then the new row will be added to the end of the data pages and page splits won't occur.

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

quote:
this is no comment on your article, but on your homepage.
The link to your discussion forum yields a
Fatal error: Call to undefined function: pg_connect() in /var/www-sql.nu/phpBB/db/postgres7.php on line 79
error.

Ah, you mean at sql.nu. Yes, a while back we had a major crash and haven't brought our database online again (since the forums wheren't really used). We're a bit ashamed, but since we're soon moving to an entirely different server and environment we don't want to spend time fixing it now. Thnaks for the heads up though.

--

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 4

quote:
Great Article. One addition comment about page splitting from your example. If your are worried about page slitting because of heavy inserts to a table put the clustered index on the identity column. Since the clustered index will be order by the data, if you put a clustered index on an identity column then the new row will be added to the end of the data pages and page splits won't occur.

Thanks Kyle, great comment. That should have been in the article as well, but I guess there'll be a part 3 some time.

--

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/

chrismj SSChasing Mays Points: 618 More actions · Answer 5

True, but that kind of defeats the purpose of the clustered index, as you are not likely to do searches on the ID, specially not range searches. You should cluster on other incrementing values that are carrying information, like creation date.

Vic Kirkpatrick SSC Eights! Points: 984 More actions · Answer 6

Depending (of course) on the business need. Here, we have plenty of tables in which we only query them via the identity column (PK). There are no columns that require searching through ranges of data on these tables, so in these cases Kyle's comment holds water.