Understanding of the N prefix for SQL Server

The plus n prefix specifies that the following string is a Unicode constant, and the SQL ServerUnicodeData types are SQL-based- theThe national character data type in the standard. Sql- theuse the prefix character N to identify these data types and their values. 1,UnicodeThe data is used in SQL Servernchar、varcharAndntextData type for storage. These data types are used for columns that store 1 of characters from multiple character sets. When the items in the column containUnicodeThe number of characters is not the same (at most4000), usingnvarcharType. When items in a column are of the same fixed length (at most4000AUnicodecharacters), usingncharType. When any item in the column exceeds4000Unicode characters, use antexttype. In Microsoft SQL Server -, traditionally non-UnicodeData types allow the use of characters defined by a specific character set. The character set is selected when you install SQL Server and cannot be changed. When storing data using non-Unicode data types, such as varchar,Char, text, and so on, if a character sort sequence is not specified (character set), the default character set is used, even if a character sort sequence is specified for a field, if the default sort sequence of SQL Server is not the same as the sort sequence of the specified field, the addition of n will result in garbled characters. If the default character set is a single-byte character set such as the Latin character set (Collation name is latin1_general_ci_as), if the name is a varchar type, the character set is the Chinese character set (Collation Name is Chinese_prc_ci_as), it will be garbled with the following insert statementInsertA (name)Values('AA in'Because the data is inserted by default when it is sent to the server by the Latin1_General_CI_AS character set, and then converted to collation name Chinese_prc_ci_as, it is garbled, but if it is changed to the following:InsertA (name)Values(N'AA in') is inserted correctly because the n prefix, sent to SQL Server in Unicode, and then converted to chinese_prc_ci_as, does not cause garbled characters. 2, conversion between Unicode and non-Unicode2-1 UNICODE--non-unicode:convert (varchar (), name Collate Chinese_prc_cs_as_ks_ws)--name is of type Nvarvhar, when name is ' AA 'Select datalength(Convert(varchar( -), name Collate Chinese_prc_cs_as_ks_ws)) fromx, the length returned is 4 (Unicode is 6)2-2Non-Unicode--〉unicode: Convert (nvarchar (), name)--name is a varchar type when name is ' AA 'Select datalength(Convert(nvarchar( -), name)) fromx, the returned length is 6 (non-Unicode time is 4)3, test the Classification test sql:------------------------------------------------------------Drop TableaCreate TableA (avarchar(2))Insert  intoAValues('a')Insert  intoAValues(N'a')Insert  intoAValues('Shenzhen')Insert  intoAValues(N'Shenzhen')SelectALen(a),datalength(a) fromaDrop Table#aCreate TableA (avarchar(2))Insert  into#aValues('a')Insert  into#aValues(N'a')Insert  into#aValues('Shenzhen')Insert  into#aValues(N'Shenzhen')SelectALen(a),datalength(a) from#a------------------------------------------------------------Drop TableaCreate TableA (avarchar(8000))Insert  intoASelect REPLICATE('a',8000) Insert  intoASelect REPLICATE('Deep',8000) Insert  intoASelect REPLICATE(N'a',8000) Insert  intoASelect REPLICATE(N'Deep',8000) SelectALen(a),datalength(a) froma3-1The . CharSet is a double-byte character set such as the Chinese character set (Collation name is chinese_prc_ci_as)<1>. Define VARCHAR (2)   (1) Formal Table summary: Under the Chinese character set, define varchar (x), regardless of the use of n"', the English characters accounted for 1 bytes, that is, you can save an X English character; Whether using n or not"', the Chinese characters account for 2 bytes, which can be saved (x/ 2) A Chinese, select result for the Chinese character itself, not garbled;2Summary of the temporary table: under the Chinese character set, the definition of varchar (x) is the same as the formal table;<2>. Define nvarchar (2)   (1) Formal Table summary: Under the Chinese character set, define nvarchar (x), regardless of the use of n"', the English characters accounted for 2 bytes, that is, you can save an X English character; Whether using n or not"', Chinese characters are accounted for 2 bytes, that is, you can save X, the select result is the Chinese character itself, not garbled;2Temporary table Summary: Under the Chinese character set, the definition of nvarchar (x), the same as the formal table performance;<3>. When the type is varchar, the length x anddatalength() corresponds to the size of a byte; English len ()= datalength(); Chinese len ()= datalength()/ 2; When the type is nvarchar, the length x andLen() corresponds to a character length;3-2The . CharSet is a single-byte character set such as the Latin character set (Collation name is latin1_general_ci_as)<1>. Define VARCHAR (2)   (1) Formal Table summary: Under the English character set, define varchar (x), regardless of the use of n"', the English characters accounted for 1 bytes, that is, you can save an X English character; Whether using n or not"', Chinese characters accounted for 1 bytes, that is, you can save x Chinese, but only the first half of the English encoding, so the select result is garbled; (Special: If you use n"', the maximum number of characters inserted at this time is 4000) English and ChineseLen()= datalength(); (2Summary of the temporary table: under the English character set, define varchar (x), regardless of the use of n"', the English characters accounted for 1 bytes, that is, you can save an X English character; Do not use n"', Chinese accounted for 1 bytes, can save x Chinese characters, but all only deposited in the first half of the character encoding, displayed as garbled; use n"', Chinese accounts for 2 bytes and can only save X/2Characters, not garbled, take out still for Chinese characters, explained in the English character set by using n"'It is possible to save the Chinese characters, except for n"'Save in Chinese, other English and ChineseLen()= datalength(); Use n"'Saved Chinese characters Len ()= datalength()/ 2;<2>. Define nvarchar (2)   (1) Formal Table summary: Under the English character set, define nvarchar (x), regardless of the use of n"', the English characters accounted for 2 bytes, that is, you can save x English character, (note that each word transmitting varchar with a large space) regardless of using n"', the Chinese characters are 2 bytes, that is, you can save x medium characters, but do not use n"'only the first half of the Chinese code is saved, so the select result is garbled; use n"'The Chinese characters are saved and removed;2Temporary table Summary: In the English character set, the definition of nvarchar (x), and the formal table performance is the same;<3>. When the type is varchar, the length x anddatalength() corresponds to a byte size; (n in a temporary table"'the length of the Chinese characters is very special;) when the type is nvarchar, the length x andLen() corresponds to a character length;4, using the maximum length of the classified varchar and nvarchar types, gets the following experience:<1>Maximum length problem1. Using varchar under the Chinese character set, the maximum length can be defined as 8000, and this 8000 refers to the number of bytes (datalength()), which can hold up to 8,000 English characters and 4,000 Chinese characters; Special: If the character n is stored'a', the maximum 4,000 characters can be saved, but the space occupied is 4000 bytes;2. Using nvarchar in the Chinese character set, the maximum length can be defined as 4000, and this 4000 refers to the number of characters (Len()), which can hold up to 4,000 English characters and 4,000 Chinese characters;3. Using varchar under the English character set, the maximum length can be defined as 8000, and this 8000 refers to the number of bytes (datalength()), since the Chinese and English are saved to 1 bytes, so the maximum can be saved 8,000 English, Chinese characters;4. Using nvarchar in the English character set, the maximum length can be defined as 4000, and this 4000 refers to the number of characters (Len()), which can hold up to 4,000 English characters and 4,000 Chinese characters;<2>Text display problem1. N"'To be used with data type nvarchar, nchar, if the Char field type is forced to use n for varchar"', there will be some special phenomena, or even uncontrollable;2. In the English character set, want to save special symbol characters, Chinese and other double-byte characters, when defining the table structure to use nvarchar or nchar, when saving with n"';3. In the Chinese character set, the database system can save special symbol characters, Chinese and other double-byte characters by default. Even without using n"', both are treated as double-byte. But for the sake of unification, it is recommended that if you use nvarchar or nchar when defining a table structure, you will use n when saving"', if you are using varchar and char when defining the table structure, do not use N at this time"'operation;4.SUBSTRING(expression, start, length) Length: Is an integer that specifies the length of the substring (the number of characters or bytes to return). The Chinese character set is based on the number of characters in the English character set.Char, varchar is taken by byte count,nchar, the nvarchar is taken by the number of characters;

Understanding of the N prefix for SQL Server