CODEWITHSUNDEEP
mysqlsqlvarchar
guy
guy

Reputation: 111

SQL VARCHAR length

I read a lot of articles and SO questions about the topic, but all they did was to confuse me more.

I'm trying to understand what is the longest string that a VARCHAR can hold, and how to define it.

In some places it said that a VARCHAR can be created with a max length of 255 (i.e. VARCHAR(255)) - I don't understand if it means 255 bytes or characters.

In other places it said that a VARCHAR can hold up to 8000 bytes - and then depends on the language (if it's 1-byte per character, such as Latin, or more - that determined the length of the longest string).

In simple terms, what does the n in VARCHAR(n) stands for, and what is the range of n? Is it bytes? Is it the number of a character? between 0-255? between 0-8000?

How does a really long text is saved? does it get split into multiple columns?

Upvotes: 0

Views: 1392

Answers (1)

svyat1s
svyat1s

Reputation: 943

VARCHAR is storing strings in 1 byte per symbol (an opposite to nvarchar that can use 2 or more bytes per character). You can read details here.

A common misconception is to think that CHAR(n) and VARCHAR(n), the n defines the number of characters. But in CHAR(n) and VARCHAR(n) the n defines the string length in bytes (0-8,000). n never defines numbers of characters that can be stored. This is similar to the definition of NCHAR(n) and NVARCHAR(n). The misconception happens because when using single-byte encoding, the storage size of CHAR and VARCHAR is n bytes and the number of characters is also n. However, for multi-byte encoding such as UTF-8, higher Unicode ranges (128-1,114,111) result in one character using two or more bytes. For example, in a column defined as CHAR(10), the Database Engine can store 10 characters that use single-byte encoding (Unicode range 0-127), but less than 10 characters when using multi-byte encoding (Unicode range 128-1,114,111). For more information about Unicode storage and character ranges, see Storage differences between UTF-8 and UTF-16.

Upvotes: 1

Related Questions