Wikis - Page Details

First published by Uwe Ricken (4MVP, Microsoft Partne)
When: 26 Jul 2013 5:17 AM
Last revision by Carsten Siemens (eMicrosoft Partne)
When: 27 Jul 2013 4:43 AM
Revisions: 9
Comments: 11

Options

Can You Improve This Article?

Positively! Click Sign In to add the tip, solution, correction or comment that will help other users.

Report inappropriate content using these instructions.

SQL Server: No Fragmentation in HEAP from INSERT / UPDATE

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:

First a simple HEAP will be created:

01.USE tempdb;

02.GO

03.

04.CREATE TABLE


dbo.heap

05.(

06.

Id

int

NOT

NULL,

07.

col1

char

(200)

NOT NULL

08.);

09.

10.SET NOCOUNT ON

11.GO

12.

13.DECLARE @i int = 1

14.WHILE @i <= 80

15.BEGIN

16. INSERT INTO


dbo.heap

17. (id, col1)

18. SELECT @i,

19. 'value: ' + CAST

(@i

AS


varchar

(5))

20.

21. SET @i += 1

22.END

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

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

2.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.
Now let's run an update on the row with the [Id] = 34 and check the physical location again:

1.UPDATE dbo.heap

2.SET col1 = 'Uwe Ricken'

3.WHERE Id = 34;

4.

5.

-- what has happened to the physical
 location?

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

01.-- Delete the record

02.DELETE dbo.heap

03.WHERE Id = 34;

04.

05.

-- and insert it with exact the same
 values

06.INSERT INTO


dbo.heap

07.VALUES ('34'

'Uwe Ricken')

08.

09.-- what has happened to the location?

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

11.GO

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 will never be used at any time and new rows will ALWAYS be inserted at the "end" of the heap.