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.
BTW, I applied the same fixes as Carsten but Uwe changed the article at the same time (I got that warning), so I didn't post my changes. So I re-applied my changes and forgot these two typos I fixed in the first edit
Uwe, I added your article here (because you implied that you wanted to enter the competition): social.technet.microsoft.com/.../18211.technet-guru-contributions-july-2013.aspx
Naomi, Carsten, thank you for your - really much - appreciated support.
Ed, yes.... This article should be part for the competition :)
I have one - important - request to you...
I wasn't able to add the pics as visible in this article.
May i ask you to add the pics instead if the links ...
Your assistance is pretty much appreciated! :)
I fixed the images. Instead of linking to the images from the forum, I download them and uploaded them to the Wiki using the Wiki editor. (The first image was linked twice in your article - I fixed this too)
Regarding images, you can probably just paste them into the Design tab if they're already up on the Forums.