Oracle Scratchpad

June 28, 2010

SQL Server 4

Filed under: SQL Server — Jonathan Lewis @ 6:06 pm BST Jun 28,2010

SimpleTalk have just published another of my SQL Server articles – which talks about storage methods and block dumping in SQL Server, and ends with the suggestion that the reason that SQL Server DBAs seem to be very keen on “clustered indexes” may be related to the fact that SQL Server doesn’t seem to handle free space management for heap tables very well.

In the same week: SimpleTalk interviews Tom Kyte in their “Geek of the Week” series.


  1. I came to Oracle from using Ingres, which also had ‘BTree’ structured tables as the preferred option. Looking back, I think one reason was the locking mechanism since, by clustering the records (eg for a customer), there was less impact when entire pages/blocks were locked.
    I do remember that we had one monthly summary that did year-to-date figures, and it was quicker to re-organize the whole table with a different Btree key (and reorganise it back afterwards) than to do the maths with the ‘wrong’ structure.

    Still, I’m sure SQL Server people would say Oracle dudes are married to heap structures because index-organized tables weren’t supported in Oracle for so long, and even now have to be on the primary key. Clustered tables always seemed to require a lot of planning.

    Comment by Gary — June 28, 2010 @ 10:47 pm BST Jun 28,2010 | Reply

    • Gary,

      The locking issue is an interesting one. On an order processing system you’re less likely to have two people entering an order for the same customer at the same time – so minimising overlaps between customers reduces a block-level lock problem. conversely if the data is simply being stored (as the heap would do) in order of arrival then you’d be maximising block-level locking. I’ll have to mention that idea somewhere in the next couple of articles.

      History does account for a lot of the strange beliefs in modern systems – but if you want to invoke history to explain the resistance to using index clustere you’d have to have a much better argument than “require a lot of planning”. (Technically it should require a lot of planning to pick the right clustered index in SQL Server – but I’m not sure it happens.)

      Comment by Jonathan Lewis — June 30, 2010 @ 9:00 am BST Jun 30,2010 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by