Search code examples
sql-server-2008maxcapacity-planning

SQL Server maximum 8KB per row?


I just happened to read the Maximum Capacity Specification for SQL Server 2008 and saw a maximum of 8060bytes per row? What the... Only 8KB per row allowed? (Yes, I saw "row-overflow storage" special handling, I'm talking about standard behavior)

Did I misunderstand something here? I'm sure I have, because I'm sure I saw binary objects with several MB sizes stored inside SQL Server databases. Does this ominous per row really mean a table row as in one row, multiple columns?

So when I have three nvarchar columns with each 4000 characters in there (suppose three legal documents written in textboxes...) - the server spits out a warning?


Solution

  • Yes, you'll get a warning on CREATE TABLE, an error on INSERT or UPDATE

    LOB types (nvarchar(max), varchar(max) and varbinary(max) allow 2Gb-1 bytes which is how you'd store large chunks of data and is what you'd have seen before.

    • For a single field > 4000 characters/8000 bytes I'd use nvarchar(max)

    • For 3 x nvarchar(4000) in one row I'd consider one of:

      • my design is wrong
      • nvarchar(max) for one or more column
      • 1:1 child table for the "least populated" columns