Char nchar nvarchar

The NVARCHAR class stores one character in two bytes. You only need to change NVARCHAR to VARCHAR.

In Microsoft SQL Server, Unicode data is supported for the following data types:

Nchar

Nvarchar

Ntext

Note that the prefix n for these data types comes from the National (Unicode) data type in the SQL-92 standard.

The usage of nchar, nvarchar, and ntext is the same as that of char, varchar, and text, but they are different in the following aspects:

Unicode supports a wider range of characters.

A larger space is required to store Unicode characters.

The nchar and nvarchar columns can contain a maximum of 4,000 characters, not as many as 8,000 characters as char and varchar characters.

Unicode constants start with N to specify: n' A Unicode string '.

All Unicode data uses the same Unicode code page. Sorting rules do not control the code page used for Unicode columns. They only control comparison rules and Case sensitivity.

Differences between nchar, nvarchar, char, and varchar --

Char and varchar are used to describe fixed and variable-length bytes data.

Nchar and nvarchar are used to store fixed and variable-length bytes data of the Unicode Character Set.

For example:

SQL> create table test_char (char_col char (10), varchar_col varchar2 (10 ));

SQL> insert into test_char values ('fixed length', 'extension ');

SQL> SELECT DUMP (CHAR_COL, 16) D_CHAR FROM TEST_CHAR;

D_CHAR
----------------------------------------------------------------
Typ = 96 Len = 10: 61,62, 63,20, 20,20, 20,20, 20,20
Typ = 96 Len = 10: b6, a8, b3, a4, 20,20, 20,20, 20,20

SQL> SELECT DUMP (VARCHAR_COL, 16) D_VARCHAR2 FROM TEST_CHAR;

D_VARCHAR2
----------------------------------------------------------------
Typ = 1 Len = 3: 31,32, 33
Typ = 1 Len = 4: b1, e4, b3, a4

SQL> create table test_nchar (nchar_col nchar (10), nvarchar_col nvarchar2 (10 ));

SQL> insert into test_nchar values ('nchar fixed length', 'nvarchar extension ');

If the length of 'nvarchar extension' is 8 + 2*2 = 12, which exceeds the size defined by the data type, why is the insertion successful?

SQL> select dump (nchar_col, 16) from test_nchar;

DUMP (NCHAR_COL, 16)
--------------------------------------------------------------
Typ = 96 Len = 20: 0, 6e, 5b, 9a, 95, 7f

SQL> select dump (nvarchar_col, 16) from test_nchar;

DUMP (NVARCHAR_COL, 16)
--------------------------------------------------------------
Typ = 1 Len = 20: 0, 6e, 95, 7f

Now I understand that although it still uses ascii code storage, the encoding length of the AL16UTF16 character set used by nchar is changed to 2 bytes. In this way, the Chinese language uses two bytes. For English characters that can be expressed in one byte, the upper-level 0 complement is used to make up two digits. In this way, for the nchar type that uses the AL16UTF16 character set, both Chinese and English are represented by two characters. Therefore, the length of 'nvarchar extension' is 10 and does not exceed the Data Type Limit.

CHAR (n) Type
String function that converts int ASCII code to characters. The n parameter is an integer between 0 and 255. If the integer expression is not in this range, NULL is returned.

Nchar and nvarchar
Nchar is the data type of Unicode data of a fixed length, nvarchar is the data type of Unicode data of a variable length, both use the UNICODE UCS-2 character set.

Nchar (n) Type
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) Type
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. Confusing Data Types in SQL Server

(1)Char, Varchar, text, nchar,Nvarchar, Ntext

Char and varchar are both between 1 and 8000 characters in length. The difference is that char is a fixed-length character data while varchar is a variable-length character data. The so-called fixed length is a fixed length. when the length of the input data does not reach the specified length, it is automatically filled with English spaces to make the length reach the corresponding length; the variable-length character data is not filled with spaces. Text stores variable-length non-Unicode data. The maximum length is 2 ^ 31-1 (2,147,483,647) characters.

Compared with the preceding three data types, the names only contain letters "n", which indicate that the characters of the Unicode data type are stored. Friends who have written programs should be familiar with Unicode. It is enough to store only one byte of English characters, but there are many Chinese characters and two bytes are required for storage. It is easy to cause confusion when both English and Chinese characters exist, 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 length of nchar and nvarchar is between 1 and 4000. Compared with char and varchar, nchar and nvarchar can store up to 4000 characters, whether in English or Chinese. char and varchar can store up to 8000 English and 4000 Chinese characters. It can be seen that when using nchar and nvarchar data types, you do not have to worry about whether the entered characters are English or Chinese characters, which is more convenient, but there is some loss in the amount of stored English hours.

　　(2) datetime and smalldatetime

Datetime: Date and Time data from January 1, 3% to seconds.

Smalldatetime: Date and Time data from January 1, January 1-20, 1900 to January 1, June 6, accurate to minutes.

　　(3) bitint, int, smallint, tinyint, and bit

Bigint: integer data from-2 ^ 63 (-9223372036854775808) to 2 ^ 63-1 (9223372036854775807.

Int: integer data from-2 ^ 31 (-2,147,483,648) to 2 ^ 31-1 (2,147,483,647.

Smallint: integer data from-2 ^ 15 (-32,768) to 2 ^ 15-1 (32,767.

Tinyint: integer data from 0 to 255.

Bit: an integer of 1 or 0.

　　(4) decimal and numeric

The two data types are equivalent. There are two parameters: p (precision) and s (number of decimal places ). P specifies the maximum number of decimal digits that can be stored on the left and right of the decimal point. p must be a value ranging from 1 to 38. S specifies the maximum number of decimal digits that can be stored on the right of the decimal point. s must be a value ranging from 0 to p. The default decimal place is 0.

　　(5) float and real

Float: floating point data from-1.79 ^ 308 to 1.79 ^ 308.

Real: floating point data from-3.40 ^ 38 to 3.40 ^ 38. In SQL Server, the synonym for real is float (24 ).