SQL server Varchar(max) and space taken

aads picture aads · Jun 19, 2013 · Viewed 9.4k times · Source

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?

Answer

Yuriy Galanter picture Yuriy Galanter · Jun 19, 2013

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.