How table scan happens in heap ?

Though this blog I want to explain how table scan happens in case of a heap table

USE master

GO

-- Create database HeapAlloc

CREATE DATABASE HeapAlloc;

Use HeapAlloc

GO

Create a clustered table. We will populate this table with 100 rows each row occupying a page. Also as col1 is an identity column it will assign each row a unique value starting from 1. Thus each page has 1 row with col1 haveing values between 1 - 100 for each of the 100 pages that will be allocated. We will then take a snapshot of the pages allocated to this table

CREATE TABLE Cluster(col1 int identity primary key, col2 char(8000))

-- populate this table to allocate 102 pages = 100 data pages + 1 IAM + 1 Index page

INSERT INTO Cluster VALUES ('a')

GO 100

Lets check the pages allocated to this table. As is visible the minimum page allocated is 80 and max is 276

DBCC IND(HeapAlloc, Cluster, 1)








Now we create a heap table to which we will allocate few rows (at least 1 uniform extent)

CREATE TABLE Heap(col1 int identity, col2 char(8000))

Populate this table to allocate 16 pages = 8 in mixed extent and rest 8 in uniform extent. This also means that col1 will have values from 1 - 16 for the 16 allocated pages

INSERT INTO Heap VALUES ('b')

GO 16

Lets check the pages allocated to this table. As is visible the minimum page allocated is 118 and max is 287

DBCC IND(HeapAlloc, Heap, 1)



-- Now we drop the cluster table and release all its pages

DROP TABLE Cluster;

At this point we insert another 1 row (col1 = 17) in the Heap table and see where the page allocation happens using the DBCC IND command

INSERT INTO Heap VALUES ('b')

DBCC IND(HeapAlloc, Heap, 1)



Clearly we see that page 184 has been allocated for this new row (17) which was not there previously. When I fire a select, this is how I expect the output to come 1 2 3 4 5 6 7 8 17 9 10 11 12 13 14 15 16.

SELECT * FROM Heap



Indeed it is. This is so because BOL clearly says this "Table scans or serial reads of a heap can be performed by scanning the IAM pages to find the extents that are holding pages for the heap. Because the IAM represents extents in the same order that they exist in the data files, this means that serial heap scans progress sequentially through each file. Using the IAM pages to set the scan sequence also means that rows from the heap are not typically returned in the order in which they were inserted."