SQL Server: No Fragmentation in HEAP from INSERT / UPDATE

SQL Server: No Fragmentation in HEAP from INSERT / UPDATE

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.

Reference

This article comes from this forum thread.
 
 

Leave a Comment
  • Please add 8 and 6 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • Carsten Siemens edited Revision 7. Comment: Fixed the images as requested by Uwe

  • Ed Price - MSFT edited Revision 6. Comment: tag. Added Reference section.

  • Carsten Siemens edited Revision 3. Comment: Fixed misspellings

  • Naomi  N edited Revision 2. Comment: Minor edit

  • Naomi  N edited Revision 1. Comment: Minor edit

  • Uwe Ricken edited Original. Comment: Inserting links

Page 1 of 1 (6 items)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
  • Uwe Ricken edited Original. Comment: Inserting links

  • Naomi  N edited Revision 1. Comment: Minor edit

  • Naomi  N edited Revision 2. Comment: Minor edit

  • Carsten Siemens edited Revision 3. Comment: Fixed misspellings

  • 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

  • Ed Price - MSFT edited Revision 6. Comment: tag. Added Reference section.

  • 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! :)

  • Carsten Siemens edited Revision 7. Comment: Fixed the images as requested by Uwe

  • Hello Uwe,

    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)

    Best regards,

    Carsten

  • Regarding images, you can probably just paste them into the Design tab if they're already up on the Forums.

    Thanks!

Page 1 of 1 (11 items)