how storage is reserved by SQL Server? and howto overrule it ?

Question

I am trying to understnad how SQL Server allocates and reserves space.

Having run the examples from the article "How table design can impact your SQL Server performance?" [1], I received the results [My 1.1] diverting from those in article [1.1].
Why?

Why in one case the excessive space is reserved/allocated (all cases in [1]) but not in another [My 1.1]?
(Note that in [1] in both cases the excessive space is reserved, though on my computer only in one of cases)

How is space allocated, reserved by SQL Server?
And how can I control/manage it?

[1] ======
How table design can impact your SQL Server performance?
http://sqlserver-training.com/how-table-design-can-impact-your-sql-server-performance

[My 1.1]
My results diverting from [1] below

name                               rows  reserved  data     index_size  unused
---------------------------------- ----- --------- -------- ----------- ---------
Fixed_Lenght_Row_Table_Optimised   10000 40008 KB  40000 KB 8 KB        0 KB

[1.1] Results in [1] diverting from mine above

name                               rows  reserved  data     index_size  unused
---------------------------------- ----- --------- -------- ----------- ---------
Fixed_Lenght_Row_Table_Optimised   10000 40072 KB  40000 KB 8 KB        64 KB

---

[1.2]
Results from [1] coinciding with mine

name                                 rows  reserved  data     index_size  unused
----------------------------------   ----- --------- -------- ----------- ---------
Fixed_Lenght_Row_Table_Non_Optimised 10000 80072 KB  80000 KB 8 KB        64 KB

Answer 1

The article in the link is dubious at best. Does not specify what SQL Server version to use, and what are the various options enabled/disabled. Since SQL Server 2005 there is row small-lob storage for columns over spilling the 8K limit (see Table and Index Organization), there are default in-row vs. out-of-row options (see sp_tableoption) and there are many compression options (row-level, page-level, Unicode).

For accurate information I would stick to the official product documentation, starting from Planning and Architecture (Database Engine). For a more digestible read, buy one of the well established books, like Microsoft SQL Server 2008 Internals or Inside Microsoft SQL Server 2005: The Storage Engine.

Answer 2

It makes sense: you have 64k unused space. You'll often see minor differences in reserved space like this

I'd also run it this way too with the 2nd parameter. It may make no difference

EXEC [sp_spaceused][1] 'Fixed_Lenght_Row_Table_Non-Optimised', 'true'

And then from sp_spaceused

When you drop or rebuild large indexes, or drop or truncate large tables, the Database Engine defers the actual page deallocations, and their associated locks, until after the transaction commits. Deferred drop operations do not release allocated space immediately. Therefore, the values returned by sp_spaceused immediately after dropping or truncating a large object may not reflect the actual disk space available. For more information about deferred allocations, see Dropping and Rebuilding Large Objects.

Finally, will you have tables that will have 4000 byte+ rows? If you want to override to optimise, I'd say you're premature... just make your implementation of the design is correct (they are separate steps)