This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In the previous article, I discussed the 8KB data page of the SQL Server, but what happened when the row is larger and cannot fit into a single 8KB page?
Each row is strictly prohibited to use only 8KB size per data page, but the data type like char, varchar, nvarchar, and varbinary require more space to store data into data page.
Sometimes, the individual column may fit in the limit of 8KB, but if we have multiple columns and the combination of all these types of columns exceeds the limit of 8KB, SQL Server moves data in different allocation unit or raise an error.
e.g. we have three separate columns like VARCHAR(MAX), CHAR(6000) and TEXT so combination of these all columns cannot fit in the 8KB size.
The SQL Server Database Engine moves the record column with the largest width to another page in the ROW_OVERFLOW_DATA allocation unit, and it is maintaining a 24-byte pointer on the original page.
To moving of the large records to another page is a dynamic process because records can become a larger at any point of time while executing UPDATE operations.
If the UPDATE operation makes a small size of the record, it backs to the original page in the IN_ROW_DATA allocation unit.
Whenever we are combining VARCHAR, NVARCHAR, BLOB, VARBINARY kind of data column in a table, internally SQL Server separates the data pages because it may exceed the 8KB row size.
My suggestion is: if we have large size of data columns, we should create a separate table for those columns. Because asynchronously, we can perform JOIN operations, which is performing better than selecting data from internal fragmented pages.
