SQL Server: No Fragmentation in HEAP from INSERT / UPDATE

Article
1/17/2024

Introduction

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

Share via