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

Cluster That Index! Part Two Expand / Collapse
Author
Message
Posted Saturday, July 19, 2003 12:00 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Saturday, February 28, 2009 6:51 AM
Points: 1,489, Visits: 7
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/
Post #14350
Posted Monday, July 21, 2003 12:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 5:36 AM
Points: 5,956, Visits: 286
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/
Post #71289
Posted Tuesday, August 12, 2003 6:31 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 10:09 AM
Points: 219, Visits: 40
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.




Post #71290
Posted Tuesday, August 12, 2003 12:24 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Saturday, February 28, 2009 6:51 AM
Points: 1,489, Visits: 7
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/
Post #71291
Posted Tuesday, August 12, 2003 12:26 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Saturday, February 28, 2009 6:51 AM
Points: 1,489, Visits: 7
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/
Post #71292
Posted Thursday, August 12, 2004 1:54 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, December 8, 2014 2:25 AM
Points: 241, Visits: 192

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.




Post #131342
Posted Thursday, August 12, 2004 12:57 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, September 8, 2004 7:36 AM
Points: 634, Visits: 1
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.
Post #131526
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse