What is the difference Between VARCHAR and NVARCHAR data types?
VARCHAR
- VARCHAR data type is used to store non-Unicode string data of variable length.
- Stores data at 1 byte per character.
- Supports up to 8000 characters.
Syntax varchar(n) or varchar(max)
n specify the string length that ranges from 1 to 8000. Default value for n is 1.
max specify the maximum storage size which is 231-1 bytes (2 GB)
NVARCHAR
- NVARCHAR data type is used to store Unicode string data of variable length, Can store both Unicode and non-Unicode strings.
- It stores data at 2 byte per character.
- It supports up to 4000 characters.
Syntax nvarchar(n) or nvarchar(max)
n specify the string length that ranges from 1 to 8000. Default value for n is 1.
max specify the maximum storage size which is 231-1 bytes (2 GB)
Letsย see the difference between VARCHAR vs NVARCHAR
Data Storage Vs Length
- VARCHAR
In following T-SQL, declared a variable @str that is of datatype varchar of size 30.
DECLARE @Str VARCHAR(30) SET @Str = 'Microsoft' SELECT @Str AS Strtext, DATALENGTH(@Str) AS StrText_OccupiedLength , LEN(@Str) AS StrText_length
As varchar has a variable-length and takes 1 bytes per character to store the data so it takes 9 bytes to store @strt value ‘microsoft’ that is length of @strt * 1 bytes (9 *1) =9.
- NVARCHAR
In following T-SQL, declared a variable @str that is of datatype nvarchar of size 30.
DECLARE @Str NVARCHAR(30) SET @Str = 'Microsoft' SELECT @Str AS Strtext, DATALENGTH(@Str) AS StrText_OccupiedLength , LEN(@Str) AS StrText_length
As nvarchar has a variable-length and takes 2 bytes per character to store the data so it takes 9 bytes to store value ‘microsoft’ that is length of @str* 2 bytes (9 *2) =18.
Using VARCHAR and NVARCHAR to store a unicode data ?
- VARCHAR
Varchar can not store Unicode data , here we try to store Hindi language text to @str variable of type varchar.
DECLARE @Str VARCHAR(30) SET @Str = N'เคฎเคพเคเคเฅเคฐเฅเคธเฅเคซเฅเค' SELECT @Str AS Strtext, DATALENGTH(@Str) AS StrText_OccupiedLength , LEN(@Str) AS StrText_length
As you can see, it does not support Unicode characters or multilingual data.
- NVARCHAR
NVarchar can store Unicode data , again we try to store Hindi language text to @str variable of type nvarchar.
Note: Always make sure that you prefix Unicode string literals with an N prefix.
DECLARE @Str NVARCHAR(30) SET @Str = N'เคฎเคพเคเคเฅเคฐเฅเคธเฅเคซเฅเค' SELECT @Str AS Strtext, DATALENGTH(@Str) AS StrText_OccupiedLength , LEN(@Str) AS StrText_length
As you can see, it supports the unicode data that you can see in the first column of result set.
Also Read..
3,952 total views, 3 views today