How many characters in varchar(max)?

Question

How many characters can a SQL Server 2008 database field contain when the data type is VARCHAR(MAX)?

check out this https://stackoverflow.com/questions/28980502/sql-varcharmax-vs-varcharfix — vicky, Mar 09 '22 at 13:27

score 68 · Accepted Answer · answered Nov 19 '09 at 05:39

68

From http://msdn.microsoft.com/en-us/library/ms176089.aspx

varchar [ ( n | max ) ] Variable-length, non-Unicode character data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of data entered + 2 bytes. The data entered can be 0 characters in length. The ISO synonyms for varchar are char varying or character varying.

1 character = 1 byte. And don't forget 2 bytes for the termination. So, 2^31-3 characters.

answered Nov 19 '09 at 05:39

i_am_jorf

53,608
15
131
222

12

Technically, 1 character = 1 byte only for certain character encodings. – Amber Nov 19 '09 at 05:43
1

Ah true. DBCS strings, e.g., would give you less. So, watch out for that. – i_am_jorf Nov 19 '09 at 05:44
@Dav, yeah that was one of my considerations too. However due to the number of bytes available, this will meet my needs for this scenario. – Russell Nov 19 '09 at 05:44
That's about half way to .2 in `2.1 x 10^9` for those of us who aren't calculators. – 千里ちゃん Oct 24 '11 at 13:37
Not to be that guy, but... 2^31 bytes (2 gigs)... if there's enough internal memory to handle it... all the times it's being used and filled with that much data... i.e. there's also a memory concern... – Erk Apr 09 '19 at 19:35

score 68 · Answer 2 · edited Jul 29 '21 at 05:41

68

For future readers who need this answer quickly:

2^31-1 = 2 147 483 647 characters, or roughly 2.147 billion

edited Jul 29 '21 at 05:41

NotTheDr01ds

15,620
5
44
70

answered Dec 04 '17 at 12:21

Dmyan

1,044
9
14

score 8 · Answer 3 · answered Nov 19 '09 at 05:40

See the MSDN reference table for maximum numbers/sizes.

Bytes per varchar(max), varbinary(max), xml, text, or image column: 2^31-1

There's a two-byte overhead for the column, so the actual data is 2^31-3 max bytes in length. Assuming you're using a single-byte character encoding, that's 2^31-3 characters total. (If you're using a character encoding that uses more than one byte per character, divide by the total number of bytes per character. If you're using a variable-length character encoding, all bets are off.)

score 1 · Answer 4 · answered Mar 03 '21 at 11:47

There are a few gotchas worth mentioning - you may need to force the use of varchar(max)

https://dba.stackexchange.com/questions/18483/varcharmax-field-cutting-off-data-after-8000-characters

and print only handles 8000 chars

How to print VARCHAR(MAX) using Print Statement?

How many characters in varchar(max)?

4 Answers4

Linked