SQL Server storage internals

SQL Server stores table data in Pages.  A Page is a the smallest unit of data storage in MS SQL Server.  The page size is 8 KB unit, due to this, the maximum size of a row is 8 KB.

CREATE TABLE Tbl
(
    ID INT,
    Data CHAR(8000),
    Info CHAR(50)
)

The above code will throw error:

Msg 1701, Level 16, State 1, Line 1
Creating or altering table 'T' failed because the minimum row size would be 16011, including 7 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.
But when we create table with VARCHAR, TEXT, BLOB types the data get stored out side of the data Page and pointer is get stored in the data row, that the reason we can have table with multiple VARCHAR(MAX) columns.

Every heap table contains a Page called IAM which holds the pointers to all data pages.  You can find this information by querying  SYSINDEXES table.

SELECT [first],[root],[FirstIAM] 
FROM SYSINDEXES 
WHERE ID = OBJECT_ID('Tbl')

For tiny table which has less then 64 KB information the memory allocated page by page.  The moment table reaches more than 64 KB (8 Pages), the memory allocation happens Extend by Extend.  An extend is 8 consecutive pages.  So for the table which are more than 64 KB, the IAM Page consists of pointer to all the Extends of the table.

So internally for any heap table IAM is the starting point which says where the data is stored.  The similar concept applies for Clustered and non-clustered tables.

The following query fill give information about how many pages used for a given table.

SELECT dpages,reserved, [rows]
 FROM SYSINDEXES 
WHERE ID = OBJECT_ID('Tbl') 

To know the data structure of each table type check MSDN

Extend

The next level of memory unit in SQL server is Extend.  An Extend can be Mixed Extend or Uniform Extend.  When an extend consists of pages of single table then it is Uniform Extend.  If an Extend consists of pages which are belong to different tables then it is called Mixed Extend.



File

The MDF, NDF files (data files) which we create for a Database are divided into Extends and used.  The first Extend’s first page in each file is a file header Page that contains information about the file. The header page has details about the address of available free uniform extends GAM, available free mixed extends SGAM, Available pages with free spaces PFS.

• GAM – Global Allocation Map
• List of free uniform extends, which can be allocated.  When ever the Engine needs a new uniform extends, it takes up one from GAM.
• SGAM – Shared Global Allocation Map
• List of free mixed extends, which can be allocated. When ever the Engine needs a new new page, it takes up one from SGAM, scan thru the Extend to occupy the free Page. 
• PFS – Page free space
• List of pages which has some free space to hold new data row.  When a new data row needs to be inserted it checks the table’s associated pages which has free space to accommodate the new row.  If no page available with free space, it takes up new Page / Extend.

For further read check the blog, though it is very old still most of them applicable.

To summarize Database data can be stored in one or more files. Each file consists of logical unit called Extend.  Each extend consists of 8 Pages.  Each Page can hold one or more data rows.