SQL Server record size: How many fields is too much?

The maximum size of a table row in SQL Server in limited to 8 KB, or to be more precise 8060 bytes, if we follow Microsoft online articles, such as Maximum Capacity Specifications for SQL Server - SQL Server | Microsoft Learn. This limit arises from the SQL Server data storage architecture: table rows are organized in 8-kilobyte pages, and a single row cannot exceed the size of the page, therefore 8KB is the maximum amount of storage space that can be allocated for a single row. Each page also carries some metadata, which reduces the space available for the table data to the mentioned 8060 bytes. Besides the page metadata, every row has a few bytes of metadata of its own, so the actual size of user data in a row is slightly below this number. Some details can be found in the article The Curious Case of… the 8060-byte row size limit - Paul S. Randal

Does this all mean that if we create a table with too many text fields, we risk failed inserts when a record becomes too large to fit in a single page? The length of a text field in Business Central is limited to 2048 Unicode characters: 4096 bytes. Two text fields this long are sufficient to exceed the page size. So we can't create a table with two Code[2048] fields? Or can we? And what about SQL data types like varchar(max), nvarchar(max), varbinary(max), xml, JSON, or image that can store up to 2 gigabytes?

When it comes to all the large text and binary data types, the capacity by far exceeding the 8KB cap is explained by the storage architecture: these columns are not necessarily stored in regular data pages. If a value of a column is short enough and the whole row fits into one page, the wide text column will be stored in the data page along with all other columns. But any insert or update operation that results in a larger row size will push the the row data into a large object (LOB) storage unit. A similar reallocation occurs if a table has columns of one of the types: varchar, nvarchar, varbinary, or sql_variant, and the row size grows beyond the page limit. The largest column of the excessively large row is moved away from the data page into the ROW_OVERFLOW_DATA allocation unit. The page data stores only a pointer to the allocated large object, thus circumventing the row size restriction.

This introduction pretty much answers the initial question - whether it is possible to store more than 8KB of text data in a table record. In the following few paragraphs, I'm going run a few illustrative examples and look at the physical data allocation in SQL table using the table definition below or reference.

table 57140 "Long Rec"
{
    DataClassification = SystemMetadata;
    
    fields
    {
        field(1; Id; Integer) {}
        field(2; "Long Text Field 1"; Text[1500]) {}
        field(3; "Long Text Field 2"; Text[1500]) {}
    }
    
    keys
    {
        key(PK; Id)
        {
            Clustered = true;
        }
    }
}

At first, I'm making the record relatively small so it can fit in one page and pad both text fields to full length with random characters:

local procedure GenerateRandomText(Length: Integer): Text
var
    String: Text;
    RandomChar: Char;
    I: Integer;
begin
    for I := 1 to Length do begin
        RandomChar := Random(94) + 32;
        String += RandomChar;
    end;

    exit(String);
end;

A single record is sufficient for this test. Once the table data is ready, I can run the first example.

Example 1

In this, and following examples, I will be using the DBCC IND command to peek into the physical data behind the logical table structure.

DBCC IND('CRONUS', [CRONUS International Ltd_$Long Rec$c016468e-0f74-4c49-8e39-12d2bdc1d7b1], -1)

Allocation for the table includes two index allocation map (IAM) pages, two index pages, and one data page.

Page 143816 in line 2 is the one containing the table data (PageType 1 in the command output means that this is a data page).

Inspect the page content, I can see some more details, including the actual record size.

And the detailed allocation of table columns within the page. Each of the two long text fields takes up 3000 bytes.

If you thoroughly sum up all the columns lengths in the SQL table definition (or in the screenshot above), the total will amount to 6076 bytes, which does not seem to tally with the number 6090 reported by DBCC as the record size. The 14 bytes difference is the additional row metadata overhead mentioned above. I have Read Committed Snapshot Isolation disabled in this database, so the 14 bytes discrepancy cannot be explained by the row version. And actually with RCSI enabled, the record size grows to 6104 bytes.

Example 2

Now I've changed the fields' lengths from 1500 to 2000 characters, generated new strings and run the DBCC IND again to look at the data allocation.

Two new pages appeared in the list: one IAM page and one text/image page, both with the IAM chain type "Row-overflow data". Since the record size is now above the 8060 bytes limit, the SQL Server storage engine moved a part of the record off-page. And the total row size in the page data has actually gone down from the previous 6090 to 4114 bytes.

Inspecting the page context, I can see that Text Field 1 still remains in page data, while the Text Field 2 has been replaced with a 24-byte link to the page overflow storage unit where the value of the second text field now resides.

If now I reduce the text field length back to 1500 characters and look at the page allocation again, I can see that the row-overflow pages are gone from the list of allocated pages and the row size has returned to 6090 bytes. If a row stored in the row overflow allocation unit is reduced, and the new size allows it to fit in a page, the storage engine immediately reallocates it back into the data page and removes the row overflow allocation unit from the IAM chain.

Example 3: Indexing considerations

And finally, a few notes on indexing of long text fields.

Index cannot be created on a field whose length exceeds 1700 bytes, but the BC compiler will not raise an error if you declare such an index. If I try to create a key on one of the long text fields, the extension deployment fails with an UnprocessableEntity error: " An unexpected error occurred after a database command was cancelled."

The actual error message underpinning the error can be found in the Windows event log, which is not accessible if you are deploying your app on a SaaS sandbox.

This error during the deployment happens when long text values already exist in the table and the BC server attempts to create an index on one of those. If I declare the index first and then try to insert a record that results in a key value exceeding 1700 bytes, something goes wrong.

This is probably never a good idea to declare an index on a field that long, there are definitely better tools for this purpose, like full text indexes.

    fields
    {
        field(1; Id; Integer) {}
        field(2; "Long Text Field 1"; Text[2000])
        {
            OptimizeForTextSearch = true;
        }
        field(3; "Long Text Field 2"; Text[2000])
        {
            OptimizeForTextSearch = true;
        }
    }
    
    keys
    {
        key(PK; Id)
        {
            Clustered = true;
        }

        // These indexes can cause SQL Server errors
        // key(LongKey1; "Long Text Field 1") {}
        // key(LongKey2; "Long Text Field 2") {}
    }

Example 4: Clustered index on a text field

And out of sheer curiosity, I ran another experiment involving a clustered index on a wide text column. On the one hand, the architecture guide quoted above states that the reallocation of an overflowing row starts from the widest column. On the other hand, a few sections later in the same guide we read that

The index key of a clustered index can't contain varchar columns that have existing data in the ROW_OVERFLOW_DATA allocation unit.

This looks like a contradiction worth exploring.

Besides, Business Central's Text and Code fields are mapped to nvarchar type in SQL Server, and the note above does not say anything about nvarchar. So what happens if I define a clustered index on a text field and try to push it off-page?

Maximum size of a clustered index in SQL Server is 900 bytes, and due to this restriction, I need another table with smaller text columns that the one I used so far.

field(1; Id; Integer) {}
field(2; "Text Field 1"; Text[400]) {}
field(3; "Text Field 2"; Text[300]) {}
field(4; "Text Field 3"; Text[300]) {}
...
field(14; "Text Field 13"; Text[300]) {} 

The total size of all text fields is 8000 bytes. Together with the Id and all the system fields the record size is above the page limit, and according to the pages architecture guide, the column with the largest width is the first to be moved to the ROW_OVERFLOW_DATA unit. Text Field 1 is the longest in my table, and this is the field I want in the clustered index.

keys
{
    key(PK; Id) {}
    key(TextKey1; "Text Field 1")
    {
        Clustered = true;
    }
}

After deploying the extension and inserting one record, I'm reviewing the row allocation. And it's actually the Text Field 2 that is moved away from the page, keeping the clustered index field in the data page.

Redefine the clustered index on Text Field 2 and deploy - and the same record is now reallocated in such a way that Text Field 2 is stored in the data page and Text Field 1 goes to the row overflow storage.

So the statement about the columns with the largest width being moved off-page first has at least one exception: columns which are part of the clustered index are exempt from his rule and are not reallocated to row overflow data units.

And as a short conclusion, I want to emphasize again that SQL Server does not prevent developers from creating tables with lots of text fields whose total length can exceed the 8KB page limit. Its storage engine is flexible enough to allow reallocation of such fields out of page data. Of course large text fields have their caveats and should be used carefully with query performance in mind. Still a table with twenty Text[250] fields is not a hidden disaster that would crash the application once the 8KB threshold is achieved. Although it is certainly a potential performance bottleneck for a query reading the table much too frequently, even if not all of the fields are returned by the query.