512 Bytes are not being used from SQL Server's 8 KByte data page
While it is true that SQL Server uses 8k (8192 bytes) data pages to store 1 or more rows, each data page has some overhead (96 bytes), and each row has some overhead (at least 9 bytes). The 8192 bytes is not purely data.

For a more detailed examination of how this works, please see my answer to the following DBA.SE question:

SUM of DATALENGTHs not matching table size from sys.allocation_units

Using the information in that linked answer, we can get a clearer picture of the actual row size:


Row Header = 4 bytes
Number of Columns = 2 bytes
NULL Bitmap = 1 byte
Version Info** = 14 bytes (optional, see footnote)
Total Per Row Overhead (excluding Slot Array) = 7 bytes minimum, or 21 bytes if version info is present
Total Actual Row Size = 263 minimum (256 data + 7 overhead), or 277 bytes (256 data + 21 overhead) if version info is present
Adding in the Slot Array, the total space taken per row is actually either 265 bytes (without version info) or 279 bytes (with version info).


Using DBCC PAGE confirms my calculation by showing: Record Size  263 (for tempdb), and Record Size   277 (for a database that is set to ALLOW_SNAPSHOT_ISOLATION ON).

Now, with 30 rows, that is:


WITHOUT version info

30 * 263 would give us 7890 bytes. Then add in the 96 bytes of page header for 7986 bytes used. Finally, add in the 60 bytes (2 per row) of the slot array for a total of 8046 bytes used on the page, and 146 remaining. Using DBCC PAGE confirms my calculation by showing:


m_slotCnt    30  (i.e. number of rows)
m_freeCnt    146   (i.e. number of bytes left on the page)
m_freeData   7986  (i.e. data + page header -- 7890 + 96 -- slot array is not factored into the "used" bytes calculation)

WITH version info

30 * 277 bytes for a total of 8310 bytes. But 8310 is over 8192, and that didn't even account for the 96 byte page header nor the 2 byte per row slot array (30 * 2 = 60 bytes) which should give us only 8036 usable bytes for the rows.

BUT, what about 29 rows? That would give us 8033 bytes of data (29 * 277) + 96 bytes for page header + 58 bytes for slot array (29 * 2) equaling 8187 bytes. And that would leave the page with 5 bytes remaining (8192 - 8187; unusable, of course). Using DBCC PAGE confirms my calculation by showing:


m_slotCnt    29  (i.e. number of rows)
m_freeCnt    5   (i.e. number of bytes left on the page)
m_freeData   8129  (i.e. data + page header -- 8033 + 96 -- slot array is not factored into the "used" bytes calculation)



Regarding Heaps

Heaps fill data pages slightly differently. They maintain a very rough estimate of the amount of space left on the page. When looking at the DBCC output, look at the row for: PAGE HEADER:    Allocation Status   PFS (1:1). You will see the VALUE showing something along the lines of 0x60 MIXED_EXT ALLOCATED   0_PCT_FULL (when I looked at the Clustered table) or 0x64 MIXED_EXT ALLOCATED 100_PCT_FULL when looking at the Heap table. This is evaluated per Transaction, so doing individual inserts such as the test being performed here could show different results between Clustered and Heap tables. Doing a single DML operation for all 30 rows, however, will fill the heap as expected.

However, none of these details regarding Heaps directly affect this particular test since both versions of the table fit 30 rows with only 146 bytes remaining. That isn't enough space for another row, regardless of Clustered or Heap.

Please keep in mind that this test is rather simple. Calculating the actual size of a row can get very complicated depending on various factors, such as: SPARSE, Data Compression, LOB data, etc.



To see the details of the data page, use the following query:

DECLARE @PageID INT,
        @FileID INT,
        @DatabaseID SMALLINT = DB_ID();

SELECT  @FileID = alloc.[allocated_page_file_id],
        @PageID = alloc.[allocated_page_page_id]
FROM    sys.dm_db_database_page_allocations(@DatabaseID,
                            OBJECT_ID(N'dbo.TestStructure'), 1, NULL, 'DETAILED') alloc
WHERE   alloc.[previous_page_page_id] IS NULL -- first data page
AND     alloc.[page_type] = 1; -- DATA_PAGE

DBCC PAGE(@DatabaseID, @FileID, @PageID, 3) WITH TABLERESULTS;


** The 14-byte "version info" value will be present if your database is set to either ALLOW_SNAPSHOT_ISOLATION ON or READ_COMMITTED_SNAPSHOT ON.