SQL Server DB table data size query result not increasing if data count increasing

Question

I have a query which will show the Table Name, Number of Rows and Size of the record of a Database tables in SQL Server. when I executed the query I got a result showing

TableName            NumberOfRows   SizeinKB
TBL_PROCESS_AUDIT2   1              16

But When I inserted more record into the same table using the same insert query and executed the query, it showing more number of rows (50) but the same size (16kb).

TableName            NumberOfRows   SizeinKB
TBL_PROCESS_AUDIT2   50             16

Actually when the number of rows of a table is increasing, it's size also should increase. So for 1 record the size is 16KB so for 50 records, the size should be 800KB. When I inserted bulk record like some 2000 records, the size is varying.

What would be the logic or problem in my query? I want the actual size of the records of a DB table.

My Query:

CREATE TABLE #RowCountsAndSizes (TableName NVARCHAR(128),rows CHAR(11),      
       reserved VARCHAR(18),data VARCHAR(18),index_size VARCHAR(18), 
       unused VARCHAR(18))

EXEC       sp_MSForEachTable 'INSERT INTO #RowCountsAndSizes EXEC sp_spaceused ''?'' '

SELECT     TableName,CONVERT(bigint,rows) AS NumberOfRows,
           CONVERT(bigint,left(reserved,len(reserved)-3)) AS SizeinKB
FROM       #RowCountsAndSizes 
ORDER BY   NumberOfRows DESC,SizeinKB DESC,TableName

DROP TABLE #RowCountsAndSizes

Answer 1

SQL Server stores data on 8kb pages. You can't have a table that's smaller than than two pages. So when you add one row to the table, you have a minimum space consumption of 2 * 8kb = 16kb. But those pages aren't full, which means adding 15 more rows doesn't increase table size by (15 * initial size), it just means that the data page holding the initial row had enough room for more data. If you add 1000 more rows, I bet your size will increase, but this will vary depending on what's in a row and how many rows can fit on an 8kb page.

I don't know that it matters, really, how much data is in a table if you want to only look at the space on a page that's used, and ignore the space on a page that isn't used. You're taking 8 kb on disk and 8 kb in memory whether the page has 10 bytes of data or 7,997 bytes. So you will see that most space calculation scripts just take (number of pages) * 8,192 because the unused space on a page is very rarely relevant.