Jan 21, 2012

Difference between varchar and nvarchar in sql server


What are differences between varchar and nvarchar or char and nchar in sql server?

1. Data type nvarchar always use 2 bytes to store a single character while varchar use one byte or two byte depending upon collation. For example:

DECLARE @Str1 VARCHAR(5) = 'A'
DECLARE @Str2 NVARCHAR(5) = N'A'

SELECT
    DATALENGTH(@Str1) [Varchar],
    DATALENGTH(@Str2) [NVarchar]

Output:
Varchar
NVarchar
1
2

2. Data type nvarchar stores the characters in Unicode which uses Unicode universal code page (UCS-2) while varchar uses many different types of code pages

3.  In data type varchar(n)
n can be 1<= n <= 8000

While In data type nvarchar(n)
n can be 1<= n <= 4000

4. Data type nvarchar support all type of collation while varchar supports only those collations which use one byte to store a single character. For example:
It is right to declare:

DECLARE @Str2 NVARCHAR(5) = N'A' COLLATE Bengali_100_CI_AI

But not this:

DECLARE @Str1 VARCHAR(5) = 'A' COLLATE Bengali_100_CI_AI
Due to Collation 'Bengali_100_CI_AI' is supported on Unicode data types only and cannot be applied to char, varchar or text data types.

When we should use varchar and nvarchar data type for a column of a table in sql server

We should use varchar when there is no need to store the data of different languages that is collation which requires only one byte to store a single character since:

1. Varchar saves 50% memory space than nvarchar
2. Query execution with varchar is faster than nvachar since due to less size less number of pages will have to search.

1 comment:

  1. Anonymous7/24/2012

    Thanks! that helped me to elect correct data type.

    ReplyDelete