Consider that we have a database and inside it, there is a table with a column. This column stores 1000 bytes char data. By convention, we know that every data page stores 8KB of data separated into 3 part (head, data and offset). Meanwhile, I want to store 10 records. let us calculate it.

first of all 8 KB = 8 * 1014 bytes = 8192 bytes

then the page head section gets 96 bytes and every record stores 1000 bytes data in data section and 2 bytes in offset section. if we calculate

96 bytes 10 * (1000 2) bytes = 10116 bytes

But every page is able to store only 8192 bytes. In this scenery, we need 2 pages

The first page is able to store only 8 records which is allocated 8000 bytes

If we calculate first page data, we can see 8112 bytes (96 bytes 8 * (1000 bytes 2 bytes) = 8112 bytes)

obviously, we can see that in the first page we have 80 bytes (8192 bytes - 8112 bytes = 80 bytes) of free space.

Now! My question is what about 80 bytes? This free space is remained as free or SQL Server will use it after.

Thank you!

CodePudding user response：

My question is what about 80 bytes. This free space is remainded as free or sql server will use it after.

It'll remain as empty space in that data page until some sort of operation causes it to be reclaimed or used.

One example would be if one of the existing rows in that data page grew bigger than 1,000 bytes to say 1,080 bytes. It'll use that remaining 80 bytes of free space in the data page to accommodate that row growth.