inforakesha

A fine WordPress.com site

Difference Between Sql Server VARCHAR and NVARCHAR Data Type

Leave a comment

Below table lists out the major difference between the VARCHAR and NVARCHAR Data Type in Sql Server:

Varchar[(n)] NVarchar[(n)]
Basic Definition Non-Unicode Variable Length character data type.Example:
DECLARE @FirstName AS VARCHAR(50) =‘BASAVARAJ’
SELECT @FirstName
UNicode Variable Length character data type. It can store both non-Unicode and Unicode (i.e. Japanese, Korean etc) characters.Example:
DECLARE @FirstName AS NVARCHAR(50)=‘BASAVARAJ’
SELECT @FirstName
No. of Bytes required for each character It takes 1 byte per character

Example:
DECLARE @FirstName AS VARCHAR(50) =‘BASAVARAJ’
SELECT @FirstName ASFirstName,DATALENGTH(@FirstName) ASLength
Result:
FirstName Length
BASAVARAJ 9

It takes 2 bytes per Unicode/Non-Unicode character.Example:
DECLARE @FirstName AS NVARCHAR(50)=‘BASAVARAJ’
SELECT @FirstName ASFirstName,DATALENGTH(@FirstName) ASLength

Result:
FirstName Length
BASAVARAJ 18

Optional Parameter nrange Optional Parameter n value can be from 1 to 8000.Can store maximum 8000 Non-Unicode characters. Optional Parameter n value can be from 1 to 4000.Can store maximum 4000 Unicode/Non-Unicode characters
If Optional Parameter n is not specified in the variable declaration or column definition If Optional parameter value nis not specified in the variable declaration or column definition then it is considered as 1.Example:
DECLARE @firstName VARCHAR=‘BASAVARAJ’
SELECT @firstName FirstName,DATALENGTH(@firstName)Length

Result:
FirstName Length
B 1

If Optional parameter value n is not specified in the variable declaration or column definition then it is considered as 1.Example:
DECLARE @firstName NVARCHAR=‘BASAVARAJ’
SELECT @firstName FirstName,DATALENGTH(@firstName)Length

Result:
FirstName Length
B 2

If Optional Parameter n is not
specified in while using
CAST/CONVERT functions
When this optional parameter n is not specified while using the CAST/CONVERT functions, then it is considered as 30.Example:
DECLARE @firstName VARCHAR(35)=‘BASAVARAJ PRABHU BIRADAR INDIA ASIA’
SELECT CAST(@firstName AS VARCHAR)FirstName,DATALENGTH(CAST(@firstNameAS VARCHAR)) Length
Result:
FirstName Length
BASAVARAJ PRABHU BIRADAR INDIA 30
When this optional parameter n is not specified while using the CAST CONVERT functions, then it is considered as 30.Example:
DECLARE @firstName NVARCHAR(35)=‘BASAVARAJ PRABHU BIRADAR INDIA ASIA’
SELECT CAST(@firstName AS NVARCHAR)FirstName,DATALENGTH(CAST(@firstNameAS NVARCHAR)) Length
Result:
FirstName Length
BASAVARAJ PRABHU BIRADAR INDIA 60
Which one to use? If we know that data to be stored in the column or variable doesn’t have any Unicode characters. If we know that the data to be stored in the column or variable can have Unicode characters.
Storage Size Takes no. of bytes equal to the no. of Characters entered plus two bytes extra for defining offset. Takes no. of bytes equal to twice the no. of Characters entered plus two bytes extra for defining offset.

As both of these are variable length datatypes, so irrespective of the length (i.e. optional parameter n value) defined in the variable declaration/column definition it will always take the no. of bytes required for the actual charcters stored. The value of n defines maximum no. of characters that can be stored.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s