SQL Server Varchar (max) and space usage

Question

If varchar(max) is used as the data type, and the inserted data is less than the full distribution, that is, only 200 characters, then will SQL Server always occupy the full varchar(max) space or just 200 character space?

Also, what are the other types of data that will occupy the maximum space, even if smaller data is inserted into it?

Are there any documents that indicate this?

Answer 1

From MS DOCS to char and varchar (Transact-SQL) :

char [ ( n ) ]
Fixed-length string data other than Unicode. n defines the length of the string and should be a value from 1 to 8000. The storage size is n bytes . The ISO char character is a character.

varchar [ ( n | max ) ]
Variable-length string data other than Unicode. n determines the length of the string and can take a value from 1 to 8000. max indicates that the maximum storage size is 2 ^ 31-1 bytes (2 GB). Storage size is the actual length of the entered data + 2 bytes . ISO synonyms for varchar are distinguished by characters or characters.

So for varchar, including max, the storage will depend on the actual length of the data, while char always has a fixed size, even if all the space is not used.

Answer 2

Use CHAR only for strings whose length you know to be fixed. For example, if you define a domain whose values ​​are limited to "T" and "F", you should probably do this CHAR[1] . If you keep US social security numbers, make a domain CHAR[9] (or CHAR[11] if you want punctuation).

Use VARCHAR for strings that may vary in length, such as names, short descriptions, etc. Use VARCHAR when you do not want to worry about stripping tailings. Use VARCHAR if there is no good reason not to

Answer 3

The size of varchar depends on the length of the data. So in your case it only takes 200 characters .