{"id":700,"date":"2018-06-24T11:54:39","date_gmt":"2018-06-24T04:54:39","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=700"},"modified":"2020-04-11T20:13:20","modified_gmt":"2020-04-11T13:13:20","slug":"sql-server-nvarchar","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-nvarchar\/","title":{"rendered":"SQL Server NVARCHAR"},"content":{"rendered":"\n

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

Overview of SQL Server NVARCHAR data type #<\/a><\/h2>\n\n\n\n

SQL Server NVARCHAR<\/code> data type is used to store variable-length, Unicode string data. The following shows the syntax of NVARCHAR<\/code>:<\/p>\n\n\n

NVARCHAR(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 defines the string length that ranges from 1 to 4,000. If you don’t specify the string length, its default value is 1.<\/p>\n\n\n\n
Another way to declare a NVARCHAR<\/code> column is to use the following syntax:<\/p>\n\n\n
NVARCHAR(max)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, max is the maximum storage size in bytes which is 2^31-1 bytes (2 GB).<\/p>\n\n\n\n
In general, the actual storage size in bytes of a NVARCHAR<\/code> value is two times the number of characters entered plus 2 bytes.<\/p>\n\n\n\n
The ISO<\/code> synonyms of NVARCHAR<\/code> are NATIONAL CHAR VARYING<\/code> or NATIONAL CHARACTER VARYING<\/code>, so you can use them interchangeably in the variable declaration or column data definition.<\/p>\n\n\n\n
VARCHAR vs. NVARCHAR #<\/a><\/h2>\n\n\n\n
The following table illustrates the main differences between VARCHAR<\/code> and NVARCHAR<\/code> data types:<\/p>\n\n\n\n
<\/th>\n
VARCHAR<\/p>\n<\/th>
NVARCHAR<\/strong><\/td><\/tr><\/thead>
Character Data Type<\/td>Variable-length, non-Unicode characters<\/td>Variable-length, both Unicode and non-Unicode characters such as Japanese, Korean, and Chinese.<\/td><\/tr>
Maximum Length<\/td>Up to 8,000 characters<\/td>Up to 4,000 characters<\/td><\/tr>
Character Size<\/td>Takes up 1 byte per character<\/td>Takes up 2 bytes per Unicode\/Non-Unicode character<\/td><\/tr>
Storage Size<\/td>Actual Length (in bytes)<\/td>2 times Actual Length (in bytes)<\/td><\/tr>
Usage<\/td>Used when data length is variable or variable length columns and if actual data is always way less than capacity<\/td>Due to storage only, used only if you need Unicode support such as the Japanese Kanji or Korean Hangul characters.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
SQL Server NVARCHAR example #<\/a><\/h2>\n\n\n\n
The following statement creates a new table<\/a> that contains one NVARCHAR<\/code> column:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> test.sql_server_nvarchar (\n    val NVARCHAR<\/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
In this example, the string length of the NVARCHAR<\/code> column is one by default.<\/p>\n\n\n\n
To change the string length of the  val<\/code> column, you use the ALTER TABLE ALTER COLUMN<\/code> statement:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> test.sql_server_Nvarchar \nALTER<\/span> COLUMN<\/span> val NVARCHAR<\/span> (10<\/span>) NOT<\/span> NULL<\/span>;\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_varchar (val)\nVALUES<\/span>\n    (N'\u3053\u3093\u306b\u3061\u306f'<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The statement worked as expected because the string value has a length that is less than to the string length defined in the column definition.<\/p>\n\n\n\n
The following statement attempts to insert a new string data whose length is greater than the string length of the  val<\/code> column:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> test.sql_server_nvarchar (val)\nVALUES<\/span>\n    (N'\u3042\u308a\u304c\u3068\u3046\u3054\u3056\u3044\u307e\u3057\u305f'<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SQL Server issued an error and terminated the statement:<\/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 storage size in bytes of the values stored in the NVARCHAR<\/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) len<\/span>,\n    DATALENGTH<\/span>(val) data_length\nFROM<\/span>\n    test.sql_server_nvarchar;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this tutorial, you have learned how to use the SQL Server NVARCHAR<\/code> data type to store variable-length, Unicode data 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 statement inserts<\/a> a new string into the  val<\/code> column of the test.sql_server_nvarchar<\/code> table:<\/p>\n\n\n