How is nvarchar(n) stored in SQL Server?

Question

Does it occupy fixed N*2 or it may use less storage if the actual value to be stored is smaller then N*2 bytes?

I have a huge table with many fields of fixed nvarchar type. Some are nvarchar(100) and some are nvarchar(400) etc.

Data in column is never an exact size, it varies from 0 to N. Most of data is less then N/2.

For example, a field called RecipientName is of type nvarchar(400) and there are 9026424 rows.

Size of only RecipientName would be 800*9026424 = 6.72 GB.

but actual storage size of entire table is only 2.02 GB. Is there any compression applied or some smaller then N with power of 2 is chosen?

Answer 1

NCHAR data type:

1. It is a fixed length data type.

2. It Occupies 2 bytes of space for EACH CHARACTER.

3. It is used to store Unicode characters (e.g. other languages like Spanish, French, Arabic, German, etc.)

For Example:

Declare @Name NChar(20);
Set @Name = N'Sachin'
Select @Name As Name, DATALENGTH(@Name) As [Datalength In Bytes], LEN(@Name) As [Length];


Name    Datalength  Length
Sachin  40          6

1. Even though declared size is 20, the data length column shows 40 bytes storage memory size because it uses 2 bytes for each character.

2. And this 40 bytes of memory is irrespective of the actual length of data stored.

NVARCHAR data type:

1. It is a variable length data type.

2. It Occupies 2 bytes of space for EACH CHARACTER.

3. It is used to store Unicode characters (e.g. other languages like Spanish, French, Arabic, German, etc.)

For Example:

Declare @Name NVarchar(20);
Set @Name = N'Sachin'
Select @Name As Name, DATALENGTH(@Name) As [Datalength], LEN(@Name) As [Length];


Name    Datalength  Length
Sachin  12          6

1. Even though declared size is 20, the data length column shows 12 bytes storage memory size because it uses 2 bytes for each character.

2. And this 12 bytes of memory is irrespective of the length of data in the declaration.