Why does my heap not fill its pages completely while an identical clustered index can fill to m_freeCnt = 0?

-- Example 1: With Clustered Index

CREATE TABLE tbl5 
(
    i VARCHAR(900) NOT NULL 
) ;
GO
CREATE CLUSTERED INDEX CIX_tbl5
    ON tbl5 (i ASC) ;
GO

INSERT      INTO tbl5
            ( i )
VALUES
            ( REPLICATE('a' , 900) )
    ,       ( REPLICATE('b' , 900) ) 
    ,       ( REPLICATE('c' , 900) )
    ,       ( REPLICATE('d' , 900) ) 
    ,       ( REPLICATE('e' , 900) ) 
    ,       ( REPLICATE('f' , 900) ) 
    ,       ( REPLICATE('g' , 900) ) 
    ,       ( REPLICATE('h' , 900) )  -- 900 x 8  + (13 x 8) = 7200 + 104 = 7304
    ,       ( REPLICATE('i' , 761) ) ;-- 7304 + 761 + 13  
GO                                    -- = 8078 + 96(Page Header) + 18 (Slot)
                                      -- = 8192
                                      -- m_freeCnt = 0

DBCC IND(tempdb,tbl5,-1) ; -- everything is IN_ROW_DATA
GO
DBCC TRACEON(3604) ;
GO
DBCC PAGE(tempdb,1,177,3) ;
GO

-- Example 2: With heap. When column 'i' is 392 m_freeCnt = 387. When 'i' = 393, I get a new page. Why can I not add more bytes to 'i'?

CREATE TABLE tbl6
(
    i VARCHAR(900) NOT NULL 
) ;
GO
INSERT      INTO tbl6
            ( i )
VALUES
            ( REPLICATE('a' , 900) ) 
    ,       ( REPLICATE('b' , 900) ) 
    ,       ( REPLICATE('c' , 900) ) 
    ,       ( REPLICATE('d' , 900) ) 
    ,       ( REPLICATE('e' , 900) ) 
    ,       ( REPLICATE('f' , 900) ) 
    ,       ( REPLICATE('g' , 900) ) 
    ,       ( REPLICATE('h' , 900) ) 
    ,       ( REPLICATE('i' , 393) ) ;
GO

DBCC IND(tempdb,tbl6,-1); 
GO
DBCC TRACEON(3604);
GO
DBCC PAGE(tempdb,1,181,3);
GO

'page split' and 'heap' should not be used in the same sentence. Do you mean why wasn't the row of size 393+change fit in the free space of the first page of the heap? That is because the PFS free space info has not high enough resolution, see Managing Extent Allocations and Free Space:


The PFS has one byte for each page, recording whether the page is
allocated, and if so, whether it is empty, 1 to 50 percent full, 51 to
80 percent full, 81 to 95 percent full, or 96 to 100 percent full.

Would fit in page, indeed, but the PFS only says that the page is '95_PCT_FULL' and the record inserted is fairly big and close to the free space as-per-PFS (95% of 8060 is 403), so the INSERT chooses to allocate a new page.

With a B-Tree the record position is deterministic due to key order, so the engine must load the page and check the free space, and the record will fit.

StackExchange Database Administrators Q#15026, answer score: 6