Sql-server – sql server limitation of data size

databasesql serversql-server-2005

This question is related to my SQL Data type size
A varchar(max) seems to be able to store up to 2GB.
What I can not understand though is that in this link sql row overflow MS says (my emphasis):

A table can contain a maximum of 8,060 bytes per row. In SQL Server
2008, this restriction is relaxed for tables that contain varchar,
nvarchar, varbinary, sql_variant, or CLR user-defined type columns.
The length of each one of these columns must still fall within the
limit of 8,000 bytes; however, their combined widths can exceed the
8,060-byte limit. This applies to varchar, nvarchar, varbinary,
sql_variant, or CLR user-defined type columns when they are created
and modified, and also to when data is updated or inserted.

I don't understand this statement.
They say that a varchar(max) can hold up to 2GB but then they say in the above link that the column length can not be more than 8KB
Isn't this contradictory or am I missing something here?

Best Answer

To make things clear, we could take the first statement which is a very fundamental one:

A table can contain a maximum of 8,060 bytes per row.

In other words: each row must fit into one page (8 kB). And if possible, many rows can fit into one page - but never the other way round.

How about binary large objects (TEXT, NTEXT, BLOB)? Data in these columns is stored in a special place. The row just holds a pointer to the data, hence the fundamental statement above still holds: a table row must fit into one page.

Binary large data is stored outside of the table.
Each table row just holds a pointer in each BLOB field which actually points to the data.

Having this said, the new varchar(max) feature is special and behaves as follows:

  • varchar(n): a usual text fields stored in row, row data must fit into one page.
  • varchar(max), small data content: stored in row as long as all row data fits into one page.
  • varchar(max), big data content: as soon as the data does not fit into one page anymore, a pointer is stored in row and varchar(max) data is stored separately. In this case, varchar(max) behaves similar to the text data type.

Note the big data content depends on all the other columns of the table. Furthermore, varchar(max) can store text in compressed form such that more than 8,060 characters might fit into one data page.

In my opinion, the statement The length of each one of these columns must still fall within the limit of 8,000 bytes seems not to be precise, possibly even incorrect (a varchar(max) column can in fact hold 2 GB of data, though not being stored in row).

Related Topic