What is the difference between char varchar nchar nvarchar (Why does SQL server automatically add spaces at the end of the string)

Based on the low carbon principle, several variables are declared as nchar. The result shows that there is a large string of spaces on the tail each time. in C #, trim has to be hard-working and error-prone for many times. When I check the Internet, the four types of strings seem to be similar. In fact, I pay much attention to them. This post is the most comprehensive and is hereby forwarded.

Original: http://zhidao.baidu.com/question/59109925.html

 

The original post is very long and the layout is a bit scattered (from inside to outside, it is very less "trim"). Let's look at the following:

 Conclusion: 1. varchar: variable-length non-Unicode data, which can contain a maximum of 8,000 characters. 2. nvarchar: a variable-length Unicode data with a maximum length of 4,000 characters. 3. Char: Non-UNICODE character data with a fixed length. It can contain a maximum of 8,000 characters. 4. nchar: Unicode data of a fixed length. It can contain a maximum of 4,000 characters. 5. Both char and varchar are character strings encoded in unicode format. The result is an integer of the characters.

Nchar (N)

Unicode data with a fixed length of n characters. The value of N must be between 1 and 4,000. The storage size is twice the size of n Bytes.

The synonyms of nchar in the SQL-92 are national char and national character.

Nvarchar (N)

Unicode data with a variable length of n characters.

The value of N must be between 1 and 4,000. The storage size of bytes is twice the number of input characters. The length of the input data can be zero.

The synonym for nvarchar in the SQL-92 is national char varying and National Character varying.

Note if n is not specified in the data definition or variable declaration statement, the default length is 1. If n is not specified by the cast function, the default length is 30.

How to Use nchar (N) and nvarchar (N)

If you want the sizes of all data items in the column to be close to the same, use nchar. Nvarchar is used if you want the data items in a column to vary greatly.

Objects that use nchar or nvarchar are assigned the default sorting rules of the database, unless the Collate clause is used to assign specific sorting rules.

Set ansi_padding off is not applicable to nchar or nvarchar. Set ansi_padding on always applies to nchar and nvarchar. 2. Char and varchar character data types with fixed length (char) or variable length (varchar.

Char [(n)]

Character data with a fixed length of n Bytes and is not Unicode. N must be a value between 1 and 8,000. The storage size is n Bytes.

The synonym for char in the SQL-92 is character.

Varchar [(n)]

Variable-length and non-UNICODE character data with a length of n Bytes. N must be a value between 1 and 8,000. The storage size is the actual length of the input data bytes, rather than n Bytes.

The length of the input data can be zero. The synonym for varchar in the SQL-92 is Char varying or character varying.

Note if n is not specified in the data definition or variable declaration statement, the default length is 1. If n is not specified by the cast function, the default length is 30.

The default database sorting rules will be assigned to objects using Char or varchar unless a specific sorting rule is assigned to the Collate clause. This sorting rule controls the code page used to store character data.

For websites that support multiple languages, Unicode nchar or nvarchar data types should be considered to minimize character conversion issues.

How to Use Char or varchar

If you want the data values in the column to be close to the same size, use char. If you want the data values in the column to be significantly different, use varchar. If set ansi_padding is off when you execute create table or alter table, a char column defined as null will be processed as varchar.

When the collation code page uses double-byte characters, the storage size is still n Bytes. Depending on the string, the storage size of n Bytes may be less than n characters.

 

After understanding this, you can understand why SQL server automatically adds spaces to the end of the string because they are declared as Char or nchar.

Note that the original space will not automatically disappear even if it is changed to the VaR type. You need to manually delete the space.

 

Click to download the free agile development textbook: Martian agile development manual