SQL server Varchar(max) and space taken

Question

If varchar(max) is used as the datatype and the inserted data is less than the full allocation, i.e. only 200 chars, then will SQL Server always take the full space of varchar(max) or just the 200 chars' space?

Further, what are the other data types that will take the max space even if lesser data is inserted?

Are there any documents that specify this?

Mission

the max is like two gigabytes. I don't think it's taking two gigabytes per record. – Fraley

Answer 1

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.

Expound

Answer 2

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.

Stratify

AFract - please don't be so pedantic. Hitesh has simply added value to the original question, and his/her answer is certainly related, which I found helpful, where-as you/yours is not . – Zagazig

Answer 3

varchar size depends on the length of the data. So in your case, it will just take 200 chars.

Spagyric