In the previous article, I have 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: varchar, nvarchar, and varbinary require more space to store data into data page.
Sometimes, This type of individual column may fit in the limit of 8KB, but if we have multiple column of this type, the combination of this type of columns exceeds the limit of 8KB. e.g. we have three separate columns like, VARCHAR(MAX), NVARCHAR(MAX) and TEXT so this all combine column 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, while 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 back to the original page in the IN_ROW_DATA allocation unit.
Whenever we are combining VARCHAR,NVARCHAR, BLOB, VARBINARY kind of data column in one table, internally SQL Server separates the data pages because it may exceed the 8KB row size.
My suggestion is: if we have large data columns, we should create a separate table for these columns. Because asynchronously, we can perform JOIN operations, which is performing better than selecting data from internal fragmented pages.