Sql-server – Understanding varchar(max) 8000 column and why I can store more than 8000 characters in it

maxsql servervarchar

From this Microsoft doc,+

n defines the string length and can be a value from 1 through 8,000.
max indicates that the maximum storage size is 2^31-1 bytes (2 GB).
The storage size is the actual length of the data entered + 2 bytes.

Please help me understand this.

The max characters for varchar seems to be 8000, which is way less than 2GB worth of data.

I see that there are records in this varchar(max) column of a specific table that have len(mycolumn) > 100 000.
Thus I know I can get way more than 8000 characters into a varchar(max) column.

Question 1: How does the 8000 characters come into play and where should I be aware of it?

Question 2 : will a .net datareader query to this column always return the full result with 100 000+ character?

Best Answer

I can see why you're misunderstanding this - it's a little tricky. These are all valid:

  • VARCHAR(1) - one character string
  • VARCHAR(4000) - 4,000 characters
  • VARCHAR(8000) - 8,000 characters - and if you use a number for this field's definition, that's the highest NUMBER you can use, but watch this:
  • VARCHAR(MAX) - that one holds up to 2GB.

And yes, if you try to get data out of a VARCHAR(MAX) field, and somebody stored 2GB in there, buckle up.

Related Question