What is NVARCHAR Data Type in SQL Server?

Summary: In this article, you will learn What is NVARCHAR data type and how to use the SQL NVARCHAR to store variable-length, Unicode string data.

Definition and Usage

NVARCHAR data type in SQL Server is used for the purpose to store variable-length and Unicode string data. To use the SQL NVARCHAR data type to define variable, columns, and parameters variable length characters. this types are variable in length. Its take up more memory than the characters stored. This differs from the SQL Server CHAR type.

Its stores up to 4000 characters with every character taking two(2) bytes. furthermore, This is well favorable for storing the extended character set data, such as John.

Syntax

The following shows the syntax:

NVARCHAR(n)

In this syntax:

Here, n defines the string length, which range belong to from 1 to 4,000. Furthermore, if you do not specify the string length, then its default value is 1.

Ulternative way to declare a SQL NVARCHAR column is to use the following syntax:

NVARCHAR(max)

In this syntax:

max is defined the maximum storage size in bytes which is 2^31-1 bytes (2 GB).
Basically, the actual storage size in bytes of a NVARCHAR value is two(2) times the number of characters entered plus two(2) bytes.

Note:Important Note: The ISO synonyms of NVARCHAR are NATIONAL CHARACTER VARYING or NATIONAL CHAR VARYING thus you’ll be able to use them interchangeably in the variable declaration or column data definition.

Defining SQL NVARCHAR Types

Here, we create a Student table with FirstName as 30 in length, LastName 40 in length and City 50 in length:

CREATE TABLE Student ( 
   StudentID INT NOT NULL, 
   FirstName NVARCHAR(30), 
   LastName NVARCHAR(40),
   City NVARCHAR(50),
   Country NVARCHAR(50),
   Pincode INT NOT NULL 
)

CREATE TABLE Student (

StudentID INT NOT NULL,

FirstName NVARCHAR(30),

LastName NVARCHAR(40),

City NVARCHAR(50),

Country NVARCHAR(50),

Pincode INT NOT NULL

)

Here we declare a variable:

DECLARE @firstName NVARCHAR(30);

1	DECLARE @firstName NVARCHAR(30);

These definitions are like VARCHAR definitions. The main difference is the columns take up twice the space!

SQL SERVER NVARCHAR Data Type Example

Check out this query having FirstName define as NVARCHAR:
SQL NVARCHAR Example

SELECT 
        LastName, 
        Len(FirstName) Length, 
        DATALENGTH(FirstName) Bytes, 
        DATALENGTH(CAST(FirstName as VARCHAR(30))) [BYTES as VARCHAR] 
FROM   Student;

SELECT

LastName,

        Len(FirstName) Length,

        DATALENGTH(FirstName) Bytes,

        DATALENGTH(CAST(FirstName as VARCHAR(30))) [BYTES as VARCHAR]

FROM   Student;

NVARCHAR

Difference Between VARCHAR vs. NVARCHAR

Property	VARCHAR	NVARCHAR
Character Data Type	Variable-length and non-Unicode characters	Variable-length, both Unicode and non-Unicode characters like as Chinese, Korean, or Japanese.
Usage	It is used only when data length is variable or variable length columns, furthermore if the actual data is always way less than capacity	Due to storage only, used only if you would like Unicode support such as the Japanese Kanji or Korean Hangul characters.
Storage Size	Real Length (in bytes)	Two(2) times Actual Length (in bytes)
Character Size	Takes up one(1) byte per character	Takes up two(2) bytes per Unicode/Non-Unicode character
Maximum Length	Range From 1 to 8,000 characters	The Range from 1 to 4,000 characters

Another Example

The following given below statement creates a new table that contains one NVARCHAR column:

CREATE TABLE demo_table (
    val NVARCHAR NOT NULL
);

CREATE TABLE demo_table (

val NVARCHAR NOT NULL

);

In this demo example, the string length of the NVARCHAR column is one(1) by default.

Furthermore, to change the string length of the val column, then you will use the ALTER TABLE ALTER COLUMN statement:

ALTER TABLE demo_table 
ALTER COLUMN val NVARCHAR (20) NOT NULL;

1 2	ALTER TABLE demo_table ALTER COLUMN val NVARCHAR (20) NOT NULL;

The following given below statement inserts a new string into the val column of the demo_table table:

INSERT INTO demo_table (val)
VALUES
    (N'こんにちは');

INSERT INTO demo_table (val)

VALUES

(N'こんにちは');

Furthermore, the statement worked obviously as a result of the string value has a length that’s less than to the string length defined in the column definition.

The following given below statement attempts to insert a new string data, furthermore, whose length is greater than the string length of the val column:

INSERT INTO demo_table (val)
VALUES
    (N'ありがとうございましたこんにちはこんにちは');

INSERT INTO demo_table (val)

VALUES

(N'ありがとうございましたこんにちはこんにちは');

When insert a new string, then SQL Server issued an error and The statement has been terminated.
Insert Error
To find the number of characters and the storage size in bytes of the values stored in the NVARCHAR column, then you will use the LEN and DATALENGTH functions as follows:

SELECT
    val,
    LEN(val) len,
    DATALENGTH(val) data_length
FROM
    demo_table;

SELECT

val,

LEN(val) len,

DATALENGTH(val) data_length

FROM

demo_table;

output as per LEN and DATALENGTH

Conclusion:

In this Article, you have learned how to use the NVARCHAR data type in SQL Server to store variable-length and Unicode data in the database.

Tutorialscan.com