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:
01.
USE tempdb;
02.
GO
03.
04.
CREATE
TABLE
dbo.heap
05.
(
06.
Id
int
NOT
NULL
,
07.
col1
char
(200)
08.
);
09.
10.
SET
NOCOUNT
ON
11.
12.
13.
DECLARE
@i
= 1
14.
WHILE @i <= 80
15.
BEGIN
16.
INSERT
INTO
17.
(id, col1)
18.
SELECT
@i,
19.
'value: '
+
CAST
(@i
AS
varchar
(5))
20.
21.
@i += 1
22.
END
23.
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.
sys.fn_PhysLocFormatter(%%physloc%%)
PageSlot, *
FROM
WHERE
Id = 34;
2.
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:
UPDATE
col1 =
'Uwe Ricken'
3.
4.
5.
-- what has happened to the physical location?
6.
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
-- and insert it with exact the same values
VALUES
'34'
)
-- 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 will never be used at any time and new rows will ALWAYS be inserted at the "end" of the heap.
This article comes from this forum thread.
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
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! :)
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!