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

392you can see loads of free space on the page in SQL Internals Viewer Screenshot. Maybe something to do with PFS units? – Martin Smith Mar 14 '12 at 20:54