A HEAP won't be "fragmented" in two cases:
- only INSERTS may occur
- UPDATES will occur in the attributes with fixed data length (e.g.
The following example demonstrates the above mentioned situations:
WHILE @i <= 80
@i += 1
After 80 records have been inserted the most interesting issue is the location of the single rows. The following query displays all information by using
Id = 34;
The result of the pic (see link) shows in the first attribute [PageSlot] the information about the fileId:PageId:SlotId of the physical location of the record.
Now let's run an update on the row with the [Id] = 34 and check the physical location again:
-- what has happened to the physical location?
Id = 34
The result won't change because the updated information fits completely into the fixed reserved space of 200 bytes. Indeed the situation will change if you delete a record and add the record again with the same values:
-- Delete the record
-- and insert it with exact the same values
-- what has happened to the location?
As you can see from the script the record with ID = 34 has been dropped and inserted again. Now the result for the physical location has changed to a different location. The explanation for that behaviour is quite simple. While each record has a fixed length
SQL Server can locate the entry and run a simple update (no forwarded records will occur!). If you release storage on a page (with a delete) it maybe used again from Microsoft SQL Server if a scan of the PFS (Page Free Space) records "enough" space on the
page for the storage of the data. But don't trust on that behaviour. If Microsoft SQL Server detect a better performance to put the data at its very end it will be done.
This article comes from
this forum thread.