Number of Characters in SQL Server varchar(max)?

1
votes

Microsoft's documentation on the varchar(max) data type:

"Variable-length, non-Unicode string data. . . 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"

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

I thought 2^31 bytes = 2 GB, not that 2^31-1 bytes = 2 GB. Am I wrong on this point?

Two of the bytes are reserved for column overhead, so the question becomes:

How many characters will the data type store?
a) 2^31-3 = 2,147,483,645 bytes = 2,147,483,645 Characters
b) 2^31-2 = 2,147,483,646 bytes = 2,147,483,646 Characters

1
sqlsql-serverdatabasedatabase-designdata-structures

1 Answers

2
votes

The number 2^31-1 is 0x7fffffff in hex. It's the largest possible positive 32-bit number on a twos-compliment machine (like the x86 and just about everything else).

The documentation is telling you that this is the maximum storage size, which has to hold the length of the data plus 2 bytes. This means that the maximum data size is 2^31-1-2, or 2,147,483,645 (0x7FFFFFFD).