Introduction

HEAP won't be "fragmented" in two cases:
- only INSERTS may occur
- UPDATES will occur in the attributes with fixed data length (e.g. char)

 The following example demonstrates the above mentioned situations:


Creating Scenario For The Test


First a simple HEAP will be created:

USE tempdb;
GO
   
CREATE TABLE dbo.heap
(
 Id      int         NOT NULL,
 col1    char(200)   NOT NULL
);
   
SET NOCOUNT ON
GO
   
DECLARE @i int = 1
WHILE @i <= 80
BEGIN
INSERT INTO dbo.heap
(id, col1)
SELECT  @i,'value: ' + CAST(@i AS varchar(5))    
 SET @i += 1
END
GO

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 sys.fn_PhysLocFormatter.

SELECT sys.fn_PhysLocFormatter(%%physloc%%) AS PageSlot, * FROM dbo.heap WHERE Id = 34;
  GO

 

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.


Performing The test

Now let's run an update on the row with the [Id] = 34 and check the physical location again:

UPDATE  dbo.heap
SET     col1 = 'Uwe Ricken'
WHERE   Id = 34;
   
-- what has happened to the physical location?
SELECT sys.fn_PhysLocFormatter(%%physloc%%) AS PageSlot, * FROM dbo.heap WHERE 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
DELETE  dbo.heap
WHERE   Id = 34;
   
-- and insert it with exact the same values
INSERT INTO dbo.heap
VALUES ('34', 'Uwe Ricken')
   
-- what has happened to the location?
SELECT sys.fn_PhysLocFormatter(%%physloc%%) AS PageSlot, * FROM dbo.heap WHERE Id = 34;
GO

  


Conclusion

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.


Reference

This article comes from this forum thread.