You're not totally correct concerning a HEAP, some rdbms allow the table and index structures to be defined ( not all B Tree's by default )  heaps do not always fragment. IF the table is not subject to updates which cause deferred updates and there are no deletes then your heap will not fragment, all new data arrives on the top of the heap - hence the term. Secondary indexes on a heap can be defragmented without issue. Technically if your heap is a fifo ( first in first out ) then even the deletes would not fragment the table structure. 

I agree that it's best practice to have a clustered index, and in fact most tables I see are still heaps , but with a clustered index. Also agreed that the leaf or data fragmentation in a table cannot be removed without a clustered index, however not all heaps fragment.

In the days before good databases were avaialable for PC's I was having to retrive data from random files in an ordered manner.  We used bianry files to store the data and then built an index for the ordering (MySQL anyone?).  We used a tree.  Not exactly a B-tree.

First, we did not give a rip about balancing.  The ordering of blocks within the index file was of almost no concern.  Even at 233 clock speeds we were able to fetch the first record in an order in way under a second.

Performance suffered when we had to add all of the overhead of making it multi-user capable.  This was in the days when the only networking that we had was shared file access.

Thanks for the article, very nice, and I look foward to the continuation on this subject.

ThomBeaux

It was a great article!!! I am waiting for the next article as this gives ignition to all thoughts regarding Fragmentation.

Thanks for an enlightening article, Joe.

You state: "Running any form of query on a heap will force a full scan of the table and could take a considerable amount of time. And the results from the DBCC SHOWCONTIG command, which is a tool for measuring fragmentation, is also of little use on a heap. "

I want to make sure I understand correctly. If TableA has a non-clustered index on ColumnA, but no clustered indexes..... and I select * from TableA Where ColumnA = 'abc'......... a table scan will be performed?  I didn't know that. I thought by having an index on the column I'm querying on, that was sufficient to prevent a table scan (unless the table only has a few rows of course).  Please elaborate.

Thanks, John

John,

You are right to point that out. Perhaps I should have made it clear that we were dealing with data that had NO indexes applied (clustered and non-clustered).

With part 1 of the series I simply wanted to get people thinking in terms of a heap of data stored in data pages. And to also introduce a few basics we could build on.
As you will see in the following parts we will introduce clustered and non-clustered indexes.

Joe

Joe,

Thanks alot for nice article. Would like to go for next part of this with not much gap. Hope you will post it very soon. By the way, I have question here...how data stores if we define a clustured index on a column which contains duplicate data and how it results when we SELECT it..please clarify.

Onceagain thanks for spending time for this good work.

-LRP