Row Structure in SQL Server

To completely understand how efficiently your database is using space/pages, it is important to understand the row structure of the database.

Creating the Test Table

Let’s create a table as shown below:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE TABLE [TestingColumnSize]
(
[Column1] INT IDENTITY,
[Column2] VARCHAR (1000) DEFAULT ‘HELLO’,
[Column3] VARCHAR (1000) DEFAULT ‘SQL’,
[Column4] VARCHAR (1000) DEFAULT ‘FOLLOWERS’,
[Column5] VARCHAR (1000) DEFAULT ‘HOPE’,
[Column6] VARCHAR (1000) DEFAULT ‘THIS’,
[Column7] VARCHAR (1000) DEFAULT ‘HELPS’
)

Inserting Data

From here we will insert default values 4 times using the following script:

1
2

INSERT INTO TestingColumnSize DEFAULT VALUES
GO 4

[Screenshot: query results showing the four inserted rows with default values]

Examining Row Size

We now have our table populated with 4 rows, this will allow us to query the table to see how our table is storing this information on the pages. Using the below query you will be able to see how many rows we have and what the max and min rows are using for bytes.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

SELECT
[alloc_unit_type_desc] AS [Data Structure]
, [page_count] AS [pages]
, [record_count] AS [Rows]
, [min_record_size_in_bytes] AS [min row]
, [max_record_size_in_bytes] AS [Max Row]
FROM SYS.dm_db_index_physical_stats
(DB_id()
,OBJECT_ID (N’TestingColumnSize’)
, NULL
, NULL
, N’Sampled’)

Additional information – The below query can be run in a few different modes which can be read about here. However, for the purposes of this demo we are using Sampled mode. Since the heap has fewer than 10,000 pages, DETAILED mode is automatically used.

Currently, our table looks like this:

[Screenshot: query results showing 4 rows at 55 bytes each]

Understanding the Row Structure

However, if you are following along you might ask how we have 55 bytes per row? It doesn’t seem to make sense at first glance since we only used 34 bytes in the default values. (4 on the identity int and 30 for the characters in varchar defaults.) To explain this, you need to understand the structure of the row, below I have created an image showing you what each column looks like when the data structure is in_row_data.

[Screenshot: diagram of in_row_data column structure]

Every column in this data structure is set up this way:

• Row header – consists of a tag and null bitmap (outside the scope of this tutorial) but it makes up 4 bytes.
• Fixed length columns – This would be our integer, making up another 4 bytes.
• Null bitmap – used to optimize storage, allows you not to store “Null” in all columns that are null (outside the scope of this tutorial). Uses 2 bytes PLUS an additional 1 bit per variable length column. 3 bytes.
• Variable-width column offset array – allows SQL to quickly find the end of the variable length column by storing the end value. 2 bytes plus every column value EXCEPT when there are trailing nulls. 2 bytes plus (2 * 6 bytes for our columns) = 14 bytes.
• Variable width columns – The values of the variable width columns. 30 bytes.

This gives us our 55 bytes.

Trailing Nulls and Storage Savings

Now, I mentioned in the variable-width column offset array, that if the row had trailing nulls there was a difference. Let’s demo this.

In our table we created above, we will insert a new row.

1
2
3
4

INSERT INTO TestingColumnSize
(Column2,Column3,Column4,Column5,Column6,Column7)
VALUES
(‘inserting30’,’bytes of data’,’in row’,null,null,null)

Now let’s run our query above to find out row size.

[Screenshot: query results showing the new row at 49 bytes]

We now have 49 bytes for one row thanks to the trailing nulls. This was caused by the variable-width column offset array, it did not have to dedicate 2 bytes to the null columns. However, what happens if we insert a value at the end of the row.

1
2
3
4

INSERT INTO TestingColumnSize
(Column2,Column3,Column4,Column5,Column6,Column7)
VALUES
(‘inserting30’,’bytes of data’,’in row’,null,null,’1’)

Again, running the query above to examine the rows, we find that the offset array needed to store the 6 bytes again.

[Screenshot: query results showing the row with a non-null trailing column back at 55 bytes]

Column Ordering Matters

This can lead to a terrible waste of space if you have columns that are often null stored in the beginning of the table instead of the end. So if you have nullable data, always make sure that you order it in the way where the column most likely to be null is at the end, NEVER out a defaulted value at the end of a row, it will cost you much more space than you think.