This article explains how much memory is consumed in SQL Server. Before starting, just understand the use of the char, varchar, nchar and nvarchar data types.
Non-Unicode String
char: char (character) is used for storing the non-Unicode string values when the length of your values is fixed and it consumes the memory on the basis of the declared length, not on the length of the pased value.
Declare @variable char(3)
It will consume 3 bytes either pass the "abc" or "a" values.
varchar: varchar (variable character) stores non-Unicode string values when the length of your values vary, in other words you don't know the length of each value. It consumes memory on the basis of the passed value length, not on the declared length.
Declare @variable varchar(3)
It will consume 3 bytes if you pass "abc" but if you pass "a" then it will consume 1 byte.
Unicode String
nchar: nchar (national character) stores Unicode string values when the length of your values are fixed. It consumes memory on the basis of the declared length, not on the length of the passed value. Unicode data consumes 2 times the space that non-Unicode data does.
Declare @variable nchar(3)
It will consume 6 bytes; either pass the "abc" or "a" values.
nvarchar: nvarchar (national variable character) is used for storing the non-Unicode string values when the length of your values vary, in other words you don't know the length that the values will be. It consumes the memory on the basis of the passed value length not on the declared length. Unicode data consumes 2 times the space that non-Unicode data does.
Declare @variable nvarchar(3)
It will consume 6 bytes if you pass "abc" but if you pass "a" then it will consume 2 bytes.
Note: Unicode DataTypes are mainly used in multilingual senarios where you can store the data in any language, like Spanish, English, Japanese but non-Unicode data types don't.
Example: check the memory consumption of char, varchar, nchar and nvarchar DataTypes in a real scenario.
1. Declare the single variable of all 4 DataTypes with the same length as in the following:
DECLARE @NonUnicodeChar char(10)
DECLARE @NonUnicodeVarChar varchar(10)
DECLARE @NonUnicodeNChar nchar(10)
DECLARE @NonUnicodeNVarChar nvarchar(10)
![Declare four DataTypes]()
2. Set the same values in all variables as in the following:
set @NonUnicodeChar='abcd'
set @NonUnicodeVarChar='abcd'
set @NonUnicodeNChar='abcd'
set @NonUnicodeNVarChar='abcd'
3. Get the data length of all values as in the following:
SELECT
DATALENGTH(@NonUnicodeChar) as char,
DATALENGTH(@NonUnicodeVarChar) as varchar,
DATALENGTH(@NonUnicodeNChar)as nchar,
DATALENGTH(@NonUnicodeNVarChar)as nvarchar;
Output: the output will be like:
10 of char: Due to fixed length
4 of varchar: Due to the length of the passed value
20 of nchar: Due to Unicode and fixed length
8 of nvarchar: Due to Unicode and the length of the passed value