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.

2 Comments »

  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

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,267 other followers