Use the SQL Server NVARCHAR data type to define columns, variables, and parameters variable length characters. NVARCHAR types are variable in length. They take up more memory than the characters stored. This differs from the CHAR type, which always occupies the full amount defined.
The NVARCHAR type stores up to 4000 characters with each character taking two bytes. NVARCHAR is well suited for storing extended character set data, such as Kanji.
Generally speaking, If you’re writing an application supporting multiple languages, then use NVARCHAR over VARCHAR.
Defining NVARCHAR types
Here, we use NVARCHAR to define a Person table with FirstName as 20 in length, and LastName 40 in length:
CREATE TABLE Person ( PersonID INT NOT NULL, FirstName NVARCHAR(20), LastName NVARCHAR(40) )
Here we declare a variable:
DECLARE @firstName NVARCHAR(20);
These definitions are like VARCHAR definitions. The main difference is the columns take up twice the space!
SQL SERVER NVARCHAR Example
Check out this query having LastName define as NVARCHAR:
Notice how the DATALENGTH is twice as long as the character length. Also notice, when LastName is converted to VARCHAR, the DATALENGTH is the same as the character length.
SELECT LastName ,Len(LastName) [Length] ,DATALENGTH(LastName) [Bytes] ,DATALENGTH(CAST(LastName as VARCHAR(20))) [BYTES as VARCHAR] FROM Person.Person
To learn more about SQL Server data types, check out these useful resources:
- Common Data Types used in SQL Server
- How do I handle a “Error converting data type” error?
- Recommended book – SQL Queries for Mere Mortals