Why nVARCHAR

One of my friends ask me a question why use nVARCHAR not the VARCHAR. He also told me when he makes any Table objects by nVARCHAR data types it takes double in length. I try to explain his query by T-SQL statements.

CREATE TABLE Table_1

       (

        Emp_name1 VARCHAR(20),

        Emp_Name2 nVARCHAR(20)

        )

GO

sp_columns Table_1

GO

COLUMN_NAME       TYPE_NAME         LENGTH

Emp_name1         varchar           20   

Emp_Name2         nvarchar          40

This article is dedicated to him to understand the difference between VARCHAR and nVARCHAR and why it takes double in length.

To explain it I am just inserting some values to my above table and observe the result closely.

INSERT INTO Table_1

            ( Emp_name1, Emp_Name2)

VALUES ('A', 'A')           

GO

SELECT DATALENGTH(Emp_name1) [VARCHAR_LENGHT],

       DATALENGTH(Emp_name2) [nVARCHAR_LENGHT]

FROM Table_1      

GO

VARCHAR_LENGHT    nVARCHAR_LENGHT

1                 2

If we observe the output closely we can see the VARCHAR takes 1 bytes and nVARCHAR takes 2 bytes.

VARCHAR is ASCII based and nVARCHAR is UNICODE based and 1 byte for ASCII and 2 byte for UNICODE.

So what it means, by using nVARCHAR we just loose the disk space?

In this article I am trying to explain the Difference UNICODE vs. ASCII and UNICODE advantage. To support my point I am gathering some facts that are mentioned bellow.

Here I am just mentioned some major differences only.

1.    UNICODE takes 2 byte. 1 byte for language page 1 byte for sign value.

2.     ASCII takes 1 byte. It doesn't contain info about language page and all bytes contain sign info.

3.     If we will use in our application different language in one time. I mean we can see record on English and Japan language - UNICODE can solve this problem as because it has language page info.

4.    If we will use in our application different language in one time. I mean we can see record on English and Japan language - ASCII can't solve this problem, because it can store info only about one language.

Hope you like it.

Posted by: MR. JOYDEEP DAS