3

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?

Community
  • 1
user76678
  • 349
  • 3
  • 5
  • 16
  • Look at the comment mrdenny left on Mike Insch's answer to your last question: http://serverfault.com/questions/304564/sql-server-variable-size-and-increase-it/304579#304579 – squillman Aug 24 '11 at 21:06
  • Does it mean that automatically the sql server overflows restricting the size to 8KB but the data of e.g. 1GB are still inserted by in different page or something?I.e. the quote I mention is not related to the administrator? – user76678 Aug 24 '11 at 21:14
  • 1
    The column will contain a pointer to the 2GB blob, and that pointer plus the rest of the column must not exceed those numbers you noted. –  Aug 24 '11 at 21:31
  • So this `must still fall within the limit of 8000 bytes` of the MSDN link is not an recomendation to the administrator?It is merely a statement of their own implementation detail? – user76678 Aug 24 '11 at 21:37

1 Answers1

3

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).

WLanger
  • 96
  • 1
  • 6