Really Small details about storage ( data size ) on columns

Question

I'm doing tests to better understand those minimal technical details about tables, pages, and etc.

I'm using sp_spaceused to better understand this with INT values ( I will do a lot of more tests with other datatypes, But I think the logic behind each datatype is the same).

OK. First I created a simple table with id int.

create table Aliens( id int)

Inserting only 1 value (Insert into table value (1)), I have this:

| name | rows | reserved | data | index_size | Unused |
|------|------|----------|------|------------|--------|
| id   | 1    | 16 KB    | 8 KB | 8 KB       | 0 KB   |

OK. I can understand that we have here USED 8KB because this is the size of a page. correct? But why does SQL server reserves 16KB for this?

Then, I delete this row, and the result is the same:

| name | rows | reserved | data | index_size |
|------|------|----------|------|------------|
| id   | 1    | 16 KB    | 8 KB | 8 KB       |

This is because I didn't rebuild the table. After rebuilding the table:

| name | rows | reserved | data | index_size | unused |
|------|------|----------|------|------------|--------|
| id   | 0    | 72 KB    | 8 KB | 8 KB       | 56 KB  |

Why now I have more reserved ( 72KB )? I deleted data and rebuilded. Now, is my table bigger? Is this because of log? and why is unused 56KB? 72-56 is 16. Is this because DATA and INDEX are separated? my table has no PK, nor indexes.

Now, when I insert 286 values ( all 1's, with go 286):

| name | rows | reserved | data  | index_size | unused |
|------|------|----------|-------|------------|--------|
| id   | 286  | 24 KB    | 16 KB | 8 KB       | 0 KB   |

Now I can see data = 16kb because There's so much information, that the SQL is now using 2 pages. But why index_size is still 8kb?

and How about NULL VALUES? according to This question here, null values on int field takes 4 bytes. a Char(10) takes 10 bytes. So, null values store the same amount as the datatype itself? Unless we are using sparse columns.

Thanks. any additional information would be appreciated.I think it's time to improve my knowledge with this details.

Answer 1

What you want to read up on is how SQL Server allocates pages (try this). Looking at data size in a row will take different tools.

Here's sample test code for a later version of SQL Server (I ran this on 2016)

USE [TestDB]
GO

DROP TABLE IF EXISTS dbo.Aliens
CREATE TABLE dbo.Aliens(ID INT)
INSERT dbo.Aliens VALUES (1)

SELECT *
FROM sys.dm_db_database_page_allocations(DB_ID(),OBJECT_ID('dbo.Aliens'),NULL,NULL,'DETAILED')

EXEC sp_spaceused 'dbo.Aliens'

This shows an IAM page and a data page for the table, and bunch of blank pages. Why the blank pages? SQL Server reserves an entire extent for the table (because I'm using 2016 - earlier versions behave differently), expecting to use those blank pages for later inserts. In 2008r2, your table starts with a single page in a mixed extent, but the rebuild pushes it to a uniform extent.

Here's code to use if you want to look at rows

DECLARE @pageid BIGINT,
@file BIGINT,
@db INT = (SELECT DB_ID())

SELECT TOP 1 @pageid = allocated_page_page_id, @file = allocated_page_file_id
FROM sys.dm_db_database_page_allocations(DB_ID(),OBJECT_ID('dbo.Aliens'),NULL,NULL,'DETAILED')
WHERE page_type = 1

DBCC TRACEON(3604)

DBCC PAGE(@db,@file,@pageid,3)