Calculate Data Row Space Usage In SQL Server

Most ofa table’s space is occupied by its data. There are also other factors like Index and Table Schema information that occupies the space. There are many system defined stored procedures, using that we can calculate the space occupied. Likethe below system, stored procedure displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database, or displays the disk space reserved and used by the whole database. In this article you will learn how to calculate Data Row Space usage In SQL Server.

Example:

In the above query we find out the row number and space reserved for Employee table.

If you find out more details about space used by a table like size of minimum and maximum record and numbers of page record then you can use below query.

As I explained earlier there are many methods to find out the space occupied by a table but “I think we should have basic idea how to calculate the space occupied by each row and a whole table.” In this article I will explain how to calculate the approximate space occupied by a row or whole table.

There are three key factors that decide the space consumption of a field.

• Data Type
• Nature of Data Type(Fixed or Variable)
• Nullable property of field.

Data Type:

The space occupied by each field mainly depends upon the data type of that particular field. Each data type occupies a different amount of space. The following list provides commonly used data types and space occupied by that data type.

Data Type	Size(In Bytes)
Tinyint	1
Smallint	2
Int	4
Bigint	8
Smallmoney	4
Money	8
Decimal	5-17 bytes, depending on the number of digits
Numeric	5-17 bytes, depending on the number of digits
Bit	Minmum 1 if there are 8 or less bit columns in a table, If there are from 9 up to 16 bit columns, the columns are stored as 2 bytes, and so on.

Nature of Data Type(Fixed or Variable):

Fixed data type always occupies the same space that is defined at declaration time even if the field contains less data but variable length data type always occupies space according the amount of data in field.

Example: if we defined the char(100) for address field then field always use 100 bytes even if address is 20 character long. But if we used varchar(100) then address field only contain 20 bytes.

Nullable Property of Field:

Like variable length data fields, each record in a table containing nullable field uses a little extra storage space. We will read about it in detail later in article.

Until now we read about the spaced occupied by each field now w will read how to calculate space for whole row. Space occupied by each row is manly dependant upon five factors. These factors define the space required by the row to store the data. These five field are the following.

• Row Header
• Fixed Data
• Null Block
• Variable Block
• Variable Data

Now we will read about each factor and how this factor affects the space occupied by a row.

Row Header:

Each row in a table contain a 4byte header. This header keeps track of where the row is placed in the table and which fields are contains a row. Row header is divided into 3 parts.

The first 2 bytes of the row, called Status Bits A and Status Bits B, are the bitmaps containing the information about the row, such as row type. Next 2 bytes are for null bitmap, null bitmap offset is always present in data records, regardless of whether the columns are nullable or not.

Fixed Data:

The fixed length data always occupies the same space that is defined at the declaration of field and is easy to predict. For example small int occupy 2 bytes, biging 8 bytes ,int occupy 4 bytes and char(10) always occupied 10 bytes even if field contains 4 bytes long data.

Null Block:

The Null block is not a compulsory data block, it is only present when table contains nullable data. If table doesn’t contain any nullable data then next block after fixed data block will be variable block. In this case only 4 blocks will be present instead of 5 blocks(null block will be absent ). The null block is also known as null bitmap . If a row contains nullable data then minimum size of null block will be 3 bytes, because null block begins with 2 bytes and 1 bytes for each 8^th columns.

If numbers of column in table is b/w 1-8 then size of null block will be 3 bytes , if numbers of columns in table is b/w 9-16 then size of null block will be 4 bytes and if numbers of columns in table is b/w 17-24 then size of null block will be 5 bytes and so on.

Now a question arises: “What is work of this extra byte in null block?”.

The main task of this byte is to indicate which column is nullable into table. If any column in nullable then bit will set to 1 otherwise bit will be set 0. Let us take some example to clear up this concept.

Example 1:

Table Employee have 5 columns and 2^nd and 4^th columns in table are nullable then the  following null block will be created.

2 bytes are reserved for null block. Now table contain 5 columns so we required (5+7)/8=1 additional byte according the formula and total of 3 bytes for null block. This 1 byte contains the null status of staring 8 columns. But our table only has 5 columns , so the last three bits will be set to 1 that are unused. Now we focus on staring 5 bits. As we asked that columns number 2^nd and 4^th are nullable so bit for both columns will set 1 and bit for remaining columns will be set to 0.

Example 2:

Table Employee have total 11 columns and column 1^st,3^rd and 9^th are nullable type columns then for employee table following

2 bytes are reserved for null block. Now table contain 5 columns so we required (11+7)/8=2 additional bytes according the formula and total 4 bytes for null block. These 2 byte contain the null status of staring 16 columns. But our table only have 11 columns , so last five bit will be set to 1 that are unused. Now we focus on staring 11 bits. As we know that columns number 1^st ,3^rd and 9^th are nullable so bit for both columns will set 1 and bit for remaining columns will be set to 0.

Variable Block:

Variable block is created the first time we create a variable field with variable length data. This block keeps track of the number of variable length data fields within the record and takes up 2 bytes for each variable field. These extra 2 bytes are used to keep the information where the data is positioned with in row.

Example:

If table row contains 2 variable field then size of variable block=2 Byte+(2*2)Byte=6 Bytes. If table row contain 2 variable field then size of variable block=2 Byte+(2*3) Byte=8 Bytes

Variable Data:

The varchar and nvarchar both are examples of variable data type; data length of these data types vary from row to row. Advantages of these datatypes is that if field contain less data as compared to data length assigned at declaration of filed then extra space will not be wasted. Varchar data consumes 1 byte per character and nvarchar data consumes 2 bytes per character, because nvarchar is Unicode.

Let us take an example. Suppose a table contains two columns data type of first column is varchar(20) and data type of second field is nvarchar(20) and both fields contain same values “Pankaj” then data length for varchar field will be 6 and data length for nvarchar field will be 12bytes. So if you want to store Unicode data into a field then only use nvarchar data type in other cases use varchar instead of nvarchar data type. One major difference b/w fixed and variable length data type is that for fixed data type we can calculate the storage consumption without looking towards the actual data but in case of variable data type we need to observe the actual data.

Above explanation is enough to find out the space consumed by a row, now we take some examples and calculate the space occupied by the data of row.

Example 1:

Structure of table:

Data into table:

Now we calculate the space required for above data.

Data Block	Space Required(Bytes)	Reason
Row Header	4	Pre Defined
Fixed Data	(10+4)=14	Data type of Name field is char(10) that is fixed and 4 bytes for integer data type of Id field
Null Block	(2+1)=3	Row have only one null field so it required only one extra byte and 2 bytes are fixed for Null block
Variable block	(2+2)=4	Bytes are fixed for variable block and 2 extra bytes for City field
Variable Data	5	Length of data into City field is 5

Total Space Required=Row Header+ Fixed Data+ Null Block + Variable Block+ Variable Data

=4+14+3+4+5=30 Bytes.

Now we calculate the size of row using system stored procedure.

Example 2:

Structure of Table:

Data Into Table:

Now we calculate the space required for above data.

Data Block	Space Required(Bytes)	Reason
Row Header	4	Pre Defined
Fixed Data	(10+4)=14	Data type of Name field is char(10) that is fixed and 4 bytes for integer data type of Id field
Null Block	(2+1)=3	Row have only two null field and information of these two field can be store by 1 byte so it required only one extra byte and 2 bytes are fixed for Null block
Variable block	(2+2+2)=6	Bytes are fixed for variable block and 2 bytes for City field and 2 bytes for state field
Variable Data	(2*6+9)=21	12 bytes are used for city field because data type is nvarchar so it will take 2 bytes per character and length of field is 6. 8 bytes are required for state field length is 9 and 1 bytes for each character.

Total Space Required=Row Header+ Fixed Data+ Null Block + Variable Block+ Variable Data

=4+14+3+6+21=48 Bytes.

Now we calculate the size of row using system stored procedure.

We can see that size of row, calculated by us and size of row in system procedure is same.

In this article we learned how to calculate the space required for each row. If you have any doubt related to this then post your query into comment section. Thanks for reading this article.

Read more articles on SQL Server:

• Dynamic Data Masking In SQL Server 2016
• Create User Defined Data Types In SQL Server