How many characters in varchar(max)?
Sql Server-2008SizeVarcharSql Server-2008 Problem Overview
How many characters can a SQL Server 2008 database field contain when the data type is VARCHAR(MAX)?
Sql Server-2008 Solutions
Solution 1 - Sql Server-2008
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.
Solution 2 - Sql Server-2008
For future readers who need this answer quickly:
> 2^31-1 = 2 147 483 647 characters, or roughly 2.147 billion
Solution 3 - Sql Server-2008
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.)
Solution 4 - Sql Server-2008
There are a few gotchas worth mentioning - you may need to force the use of varchar(max)
and print only handles 8000 chars
https://stackoverflow.com/questions/7850477/how-to-print-varcharmax-using-print-statement/10206672