Archives
for this blog

I have total of 11 years of IT experience with Application development, Database Development and Database Administration. I have worked with different version of SQL server from 7.0 to 2008.Started my carrier as VB ,VC++ and database developer in a banking sector for implementing their core banking solution. Currently working as Database Administrator with wide knowledge in performance tuning, high availability solution, troubleshooting and server monitoring. This blog is my humble attempt to share my knowledge and what I learned from my day to day work.

In our last post, we have gone through the data page structure and we have noticed that there is an overhead of 7 bytes in each records. Let us try to understand how SQL Server stores the records in a data pages.

Data record stores actual data. In other words, it store the records in heap or the records in the leaf level of clustered index. The data records are stored in a structure which helps SQL server manage these record efficiently. Let us see a pictorial representation of a data record.

The section which are marked in blue are part of all data records.The other section will be part of the data record depends on fixed length / variable length column are available in the table structure.

The first one byte is used for status Bits 1 which define the properties of the record :

Bit 0: Versioning information. In SQL server 2008 this is always 0Bits 1 to 3: This is three bit value define the record type.

0 data record.

1 Forwarded record.

2 a forwarding stub.

3 Index record.

4 blob fragment or row overflow data.

5 ghost index record.

6 ghost data record

7 ghost version record

Bit 4: Null bitmap exists or not.In SQL server 2008 null bitmap exists even if there is no null able columns

Bit 5: Indicate variable column exists or not.

Bit 6 :Indicate that row contain versioning information

Bit 7 : Not used in SQL server

The second byte used for status bits 2 . Only one bit is used in this to indicate that the record is ghost forwarded record.

The next 2 bytes are used to store the length of fixed portion of the record.This include the two status bytes,2 bytes used for this field and actual size of fixed length data in the table. For example if a table does not have any fixed length column the value in this field will be 4.This is the same value will be displayed in the pminlen field in the page header. (Refer the Page Structure post)

The next n bytes are used to store the fixed length data available in the table where n is the total size of the fixed length column in the table. This part will not be there in the record structure if all column in the table are variable length column.

Next 2 bytes are used to store the total number of column in the table.

Next n bytes are used for null bitmap. one bit for each column in the table. value 1 in each bit indicate the corresponding column has NULL value in that record. The value of n will be no.column the table/8 and round to next integer value.

Next 2 bytes are used to store the number of variable length column in the table.

Next n bytes are used to store variable column offset array .This is nothing but the starting offset value (with respect to the page) of each variable length column.Each variable column requires 2 bytes. The value of n will be 2 X no. of variable length column in the table.

Next n bytes are used to store the actual data of variable length column . The value of n will be the total size (actual data stored not in the definition of the table) of variable length column.

Let us see the same example that we considered in the page structure post

The pminlen value 707 in the header is the total size of the column (703 bytes) , 2 bytes used for status bytes and 2 bytes used to store the size of fixed length columns. The Length 710 mention in the record slot is the sum of pminlen, size of null bitmap (1 byte) and 2 bytes used to store the number column in the table.

The length 91 shown in the slot 0 is the sum of :
1 bytes for status bits 1
1 bytes for status bits 2
2 bytes used to store the size of fixed length columns
26 bytes of fixed length column (title,dob,phone,countrycode)
2 bytes used to store total number of columns
2 bytes for null bitmap, round up total no of column/8 = 9/8=2
2 bytes to store number of variable length columns
10 bytes to store the variable length column offset (number of variable length column X2)
45 bytes to store the variable length data (actual size of the data). This can be obtained by running the below query

SELECT DATALENGTH(FirstName)+DATALENGTH(Lastname)+DATALENGTH(email)+

DATALENGTH(Designation)+DATALENGTH(PersonalPreference) FROM VariableLength

Hope you got clear idea about the storage of data in the SQL server page.