CODEWITHSUNDEEP
sql-server
LonelyRogue
LonelyRogue

Reputation: 466

Understanding documentation of "Row-Overflow Data Exceeding 8 KB"

The SQL Server Documentation on Storage and Row Size limit of 8060 Bytes says

The length of individual columns must still fall within the limit of 8,000 bytes for varchar, nvarchar, varbinary, sql_variant, and CLR user-defined type columns. Only their combined lengths can exceed the 8,060-byte row limit of a table.

Please someone explain and help me understand:

  1. "length of individual columns must still fall.." - Is the Length referred here is represented by LEN() OR DATALENGTH()?
  2. How can be the largest size allowed by VARCHAR(MAX) be less than 8060 Bytes?
  3. "Only their combined lengths can exceed" - does it mean sum of Sizes of all VARCHAR(MAX) columns in the table, even if filled to the full of DataType capacity, are allowed without Error - "Msg 511 Cannot create a row of size XXXX which is greater than the allowable maximum of 8060".

Upvotes: 2

Views: 1142

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521904

The length restriction must refer to the data length, and not the actual text length. For different encodings, two strings of the same length may have a different size.

For VARCHAR and the other types mentioned (not including NVARCHAR(MAX)), each column is limited to 8060 bytes, but the sum of several such columns may still exceed 8060 bytes.

Regarding the apparent contradiction for NVARCHAR(MAX), the link you gave goes on to add a note:

This restriction does not apply to varchar(max), nvarchar(max), varbinary(max), text, image, or xml columns. For more information about the storage of these columns, see Using Large-Value Data Types, Using text and image Data, and Using XML Data.

Upvotes: 1

Related Questions