Summary: in this tutorial, you will learn how to use the SQL Server
NVARCHAR data type to store variable-length, Unicode string data.
Overview of SQL Server
NVARCHAR data type
NVARCHAR data type is used to store variable-length, Unicode string data. The following shows the syntax of
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.
Another way to declare a
NVARCHAR column is to use the following syntax:
In this syntax, max is the maximum storage size in bytes which is 2^31-1 bytes (2 GB).
In general, the actual storage size in bytes of a
NVARCHAR value is two times the number of characters entered plus 2 bytes.
ISO synonyms of
NATIONAL CHAR VARYING or
NATIONAL CHARACTER VARYING, so you can use them interchangeably in the variable declaration or column data definition.
VARCHAR vs. NVARCHAR
The following table illustrates the main differences between
NVARCHAR data types:
|Character Data Type||Variable-length, non-Unicode characters||Variable-length, both Unicode and non-Unicode characters such as Japanese, Korean, and Chinese.|
|Maximum Length||Up to 8,000 characters||Up to 4,000 characters|
|Character Size||Takes up 1 byte per character||Takes up 2 bytes per Unicode/Non-Unicode character|
|Storage Size||Actual Length (in bytes)||2 times Actual Length (in bytes)|
|Usage||Used when data length is variable or variable length columns and if actual data is always way less than capacity||Due to storage only, used only if you need Unicode support such as the Japanese Kanji or Korean Hangul characters.|
The following statement creates a new table that contains one
CREATE TABLE test.sql_server_nvarchar (
val NVARCHAR NOT NULL
In this example, the string length of the
NVARCHAR column is one by default.
To change the string length of the
val column, you use the
ALTER TABLE ALTER COLUMN statement:
ALTER TABLE test.sql_server_Nvarchar
ALTER COLUMN val NVARCHAR (10) NOT NULL;
The following statement inserts a new string into the
val column of the
INSERT INTO test.sql_server_varchar (val)
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.
The following statement attempts to insert a new string data whose length is greater than the string length of the
INSERT INTO test.sql_server_nvarchar (val)
SQL Server issued an error and terminated the statement:
String or binary data would be truncated.
The statement has been terminated.
To find the number of characters and the storage size in bytes of the values stored in the
NVARCHAR column, you use the
DATALENGTH functions as follows:
In this tutorial, you have learned how to use the SQL Server
NVARCHAR data type to store variable-length, Unicode data in the database.