SQL server Varchar(max) and space taken
From MS DOCS on char and varchar (Transact-SQL):
char [ ( n ) ]
Fixed-length, non-Unicode string data. n defines the string length and must be a value from 1 through 8,000. The storage size is n bytes. The ISO synonym for char is character.
varchar [ ( n | max ) ]
Variable-length, non-Unicode string data. 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. The ISO synonyms for varchar are char varying or character varying.
So for varchar, including max - the storage will depend on actual data length, while char is always fixed size even when entire space is not used.
Use CHAR
only for strings
whose length you know to be fixed. For example, if you define a domain
whose values are restricted to 'T' and 'F', you should probably make
that CHAR[1]
. If you're storing US social security numbers, make the
domain CHAR[9]
(or CHAR[11]
if you want punctuation).
Use VARCHAR
for strings that can vary in length, like names, short
descriptions, etc. Use VARCHAR
when you don't want to worry about
stripping trailing blanks. Use VARCHAR
unless there's a good reason
not to.