{"id":685,"date":"2018-06-24T10:44:26","date_gmt":"2018-06-24T03:44:26","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=685"},"modified":"2020-04-11T20:13:20","modified_gmt":"2020-04-11T13:13:20","slug":"sql-server-nchar","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-nchar\/","title":{"rendered":"SQL Server NCHAR"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL Server NCHAR<\/code> data type to store fixed-length, Unicode character string data.<\/p>\n\n\n\n

To store fixed-length, Unicode character string data in the database, you use the SQL Server NCHAR<\/code> data type:<\/p>\n\n\n

NCHAR(n)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, n specifies the string length that ranges from 1 to 4,000. The storage size of a NCHAR<\/code> value is two times n bytes.<\/p>\n\n\n\n
The ISO synonyms for NCHAR<\/code> are NATIONAL CHAR<\/code> and NATIONAL CHARACTER<\/code>, therefore, you can use them interchangeably.<\/p>\n\n\n\n
Similar to the CHAR<\/a><\/code> data type, you use the NCHAR<\/code> for storing fixed-length character string only. If the lengths of data values are variable, you should consider using VARCHAR<\/code> or NVARCHAR<\/code> data type.<\/p>\n\n\n\n
CHAR vs. NCHAR #<\/a><\/h2>\n\n\n\n
The following are the major differences between CHAR and NCHAR data types<\/a><\/p>\n\n\n\n
\n
CHAR<\/h4>\n<\/th>
\n
NCHAR<\/h4>\n<\/th><\/tr><\/thead>
Store only non-Unicode characters.<\/td>Store Unicode characters in the form of UNICODE UCS-2 characters.<\/td><\/tr>
Need 1 byte to store a character<\/td>Need 2 bytes to store a character.<\/td><\/tr>
The storage size equals the size<\/strong> specified in the column definition or variable declaration.<\/td>The storage size equals double the size<\/strong> specified in the column definition or variable declaration.<\/td><\/tr>
Store up to 8000 characters.<\/td>Store up to 4000 characters.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
SQL Server NCHAR example #<\/a><\/h2>\n\n\n\n
The following statement creates a new table<\/a> with one NCHAR<\/code> column:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> test.sql_server_nchar (\n    val NCHAR<\/span>(1<\/span>) NOT<\/span> NULL<\/span>\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
INSERT<\/span> INTO<\/span> test.sql_server_nchar (val)\nVALUES<\/span>\n    (N'\u3042'<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Notice that you must prefix the Unicode character string constants with the letter N<\/code>. Otherwise, SQL Server will convert the string to the default code page of the database which may not recognize some certain Unicode characters.<\/p>\n\n\n\n
If you insert a character string whose length is greater than the length specified in the column definition, SQL Server issues an error and terminates the statement.<\/p>\n\n\n\n
For example, the following statement attempts to insert a string with two characters into the  val<\/code> column of test.sql_server_nchar<\/code> table:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> test.sql_server_nchar (val)\nVALUES<\/span>\n    (N'\u3044\u3048'<\/span>); \n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SQL Server issued the following error message:<\/p>\n\n\n
String or binary data would be truncated.\nThe statement has been terminated. \n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To find the number of characters and the number of bytes of the values the val<\/code> column, you use the LEN<\/code> and DATALENGTH<\/code> functions as follows:<\/p>\n\n\n
SELECT<\/span>\n    val,\n    len<\/span>(val) length<\/span>,\n    DATALENGTH<\/span>(val) data_length\nFROM<\/span>\n    test.sql_server_nchar;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
In this tutorial, you have learned how to use the SQL Server NCHAR<\/code> data type to store fixed-length, Unicode character strings in the database.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
The following INSERT<\/a><\/code> statement inserts the character a (\u3042) in Japanese into the NCHAR<\/code> column:<\/p>\n\n\n