Tables and space used by them

This came up when a dear colleague of mine came to her boss and told him that even though the table she just created had only one row, “is taking up much more space than needed” – this is the actual quote.
Let see what she meant.

CREATE
TABLE dbo.Table_1
(

large_column CHAR(8000)
NULL

)

ON

[PRIMARY]

GO

I must apologize for the very creative names I’m giving to my tables in my examplesJ. Okay, so now I have one table with no data in it. In other words, it should not hold any space in the database, right?

There’s one way to tell this (more, actually, but one this is the one that I’m presenting here, since it’s not commonly used), by using sp_spaceused. If I use this stored procedure, I get the following:

EXEC
sp_spaceused
Table_1

And the result:

Now let’s insert exactly one record:

INSERT
INTO Table_1 VALUES (REPLICATE
(‘1’, 8000));

Now let’s execute sp_spaceused again:

EXEC
sp_spaceused
Table_1

And for the result:

So I inserted roughly 8 KB and I already used 16 KBJ.

There’s nothing mysterious here, really, and the main idea is this: the basic unit of storage for SQL Server – and any RDBMS for that matter is a page, which, for SQL Server, can hold roughly 8 KB of data. A logical grouping of 8 pages is called an extent, and the extent comes in two flavors: a mixed extent (at least two objects share pages from that extent) and a uniform extent (all the pages belong to a particular object).

Armed with this knowledge, this is what happens when you actually insert a row in the table:

1. The table will need a special page, called IAM page (Index Allocation Map). This particular page will keep track of all the pages that will be allocated to the table, and it is the index_size column in our case (see here for further details).
   Truth be told, in our particular case it does not hold any information about indexes, since there are no indexes present on our table, but that’s ok, since our “table” is not quite a table, but a structure called heap.
2. Only after that IAM page is created, we can actually insert a record into our “table”. Of course, this means that another page is needed.

Now you know why your table (heap, really) holds much more storage than actually there’s data.

Please bear in mind that this is a mini-post, so to speak, since I did not cover anything related to the allocation itself (GAM, SGAM, PFS pages, and so on), but I’ll cover that in a not too distant post.