ForProgramString Field in, which is in SQL ServerChar, varchar, nchar, and nvarcharThe four types are used to match (text and ntext are not considered for the time being). When a database is created, these four types are often vague. Here we will compare them.
-
- Fixed Length or variable length
The so-called fixed length is fixed length. when the length of the input data does not reach the specified length, it will be automatically filled with English spaces to make the length reach the corresponding length;VaRPrefix, indicating that the actual storage space is longer, suchVarchar and nvarcharVariable-length character data is not filled with spaces. The exception is that text is also variable-length.
-
- Unicode or non-Unicode
In the database, only one byte is required for English characters, but two bytes are required for Chinese characters and many other non-English characters. If both English and Chinese characters exist, the occupied space may lead to confusion, leading to garbled characters. Unicode Character Set is generated to solve the incompatibility problem of character sets. All its characters are expressed in two bytes, that is, English characters are also expressed in two bytes. The prefix n indicates Unicode characters, suchNchar, nvarcharThese two types Use the Unicode Character Set.
- Based on the above two points to see the field capacity
| Char, varchar |
Up to 8000 English letters and 4000 Chinese Characters |
| Nchar, nvarchar |
It can store 4000 characters, regardless of English or Chinese Characters |
-
- Usage (personal preferences)
If the data size is very large and can be 100% characters long and only ANSI characters are savedChar
It cannot be an ANSI character or can be determined.Nchar;
For ultra-large data, suchArticleContent, useNtext
Other generalNvarchar
Comparison of char, varchar, nchar, and nvarchar features
- Char
CharIt is convenient to store fixed-length data,CharHigh Field indexing efficiency, such as definitionChar(10), no matter whether the data you store reaches 10 bytes, it occupies 10 bytes of space.
- Varchar
Store variable-length data,No storage efficiencyCharHighIf the possible value of a field is not fixed, we only know that it cannot exceed 10 characters and define itVarchar(10) is the most cost-effective.VarcharThe actual length of the type is the actual length of its value + 1. Why "+ 1? This byte is used to save the actual length.
In terms of spaceVarcharSuitable; in terms of efficiency, useCharThe key is to find a balance point based on the actual situation.
- Text
TextStores variable-length non-Unicode data. The maximum length is 2 ^ 31-1 (2,147,483,647) characters.
- Nchar, nvarchar, ntext
The three names are named N more than the first three ". AndChar,VarcharIn comparison,Nchar,NvarcharA maximum of 4000 characters can be stored, whether in English or Chinese.Char,VarcharA maximum of 8000 English and 4000 Chinese characters can be stored. We can see thatNchar,NvarcharThe data type is convenient because you do not have to worry about whether the entered characters are English or Chinese characters.
So generally, if it contains Chinese characters, useNchar/nvarcharIf it is a pure English text or number, useChar/varchar.
For string fields in the program, sqlserver hasChar, varchar, nchar, and nvarcharThe four types are used to match (text and ntext are not considered for the time being). When a database is created, these four types are often vague. Here we will compare them.
-
- Fixed Length or variable length
The so-called fixed length is fixed length. when the length of the input data does not reach the specified length, it will be automatically filled with English spaces to make the length reach the corresponding length;VaRPrefix, indicating that the actual storage space is longer, suchVarchar and nvarcharVariable-length character data is not filled with spaces. The exception is that text is also variable-length.
- Unicode or non-Unicode
In the database, only one byte is required for English characters, but two bytes are required for Chinese characters and many other non-English characters. If both English and Chinese characters exist, the occupied space may lead to confusion, leading to garbled characters. Unicode Character Set is generated to solve the incompatibility problem of character sets. All its characters are expressed in two bytes, that is, English characters are also expressed in two bytes. The prefix n indicates Unicode characters, suchNchar, nvarcharThese two types Use the Unicode Character Set.
-
- Based on the above two points to see the field capacity
| Char, varchar |
Up to 8000 English letters and 4000 Chinese Characters |
| Nchar, nvarchar |
It can store 4000 characters, regardless of English or Chinese Characters |
-
- Usage (personal preferences)
If the data size is very large and can be 100% characters long and only ANSI characters are savedChar
It cannot be an ANSI character or can be determined.Nchar;
For ultra-large data, such as article content, useNtext
Other generalNvarchar
Comparison of char, varchar, nchar, and nvarchar features
- Char
CharIt is convenient to store fixed-length data,CharHigh Field indexing efficiency, such as definitionChar(10), no matter whether the data you store reaches 10 bytes, it occupies 10 bytes of space.
- Varchar
Store variable-length data,No storage efficiencyCharHighIf the possible value of a field is not fixed, we only know that it cannot exceed 10 characters and define itVarchar(10) is the most cost-effective.VarcharThe actual length of the type is the actual length of its value + 1. Why "+ 1? This byte is used to save the actual length.
In terms of spaceVarcharSuitable; in terms of efficiency, useCharThe key is to find a balance point based on the actual situation.
- Text
TextStores variable-length non-Unicode data. The maximum length is 2 ^ 31-1 (2,147,483,647) characters.
- Nchar, nvarchar, ntext
The three names are named N more than the first three ". AndChar,VarcharIn comparison,Nchar,NvarcharA maximum of 4000 characters can be stored, whether in English or Chinese.Char,VarcharA maximum of 8000 English and 4000 Chinese characters can be stored. We can see thatNchar,NvarcharThe data type is convenient because you do not have to worry about whether the entered characters are English or Chinese characters.
So generally, if it contains Chinese characters, useNchar/nvarcharIf it is a pure English text or number, useChar/varchar.
