SQL INTERVIEW QUESTION: DIFFERENCE BETWEEN VARCHAR() AND NVARCHAR() DATATYPES

Difference between Varchar() and NVarchar() Data types in SQL Server : Interview Questions
Following are the difference between these two datatypes,
This is the very important interview question, and most favorite interviewer's question, you must face this questions when you go as a fresher for .net or SQL developer interview.

Varchar()NVarchar()
1 - Syntaxvarchar[(n|max)]nvarchar[(n|max)]
2 - Basic DefinationVariable-length, non-Unicode character data.



Variable-length Unicode character data. Support every language (i.e. Hindi, Korean, Chines etc)


3- See
difference
by Example
Example 1: (will give same result)








Example 2: (See the diff in case of Unicode characters, output showing ??)









"???" Means your data has lost
Example 1: (will give same result)


Example 2: (See the diff in case of Unicode characters, output showing as expected)









Note: for Unicode character must prefix with "N" otherwise it will also show "??", Good interview questions!!
4 - Bytes required for each characterVarchar takes 1 byte per character
Example 1:

NVarchar takes 2 byte per character
Example 1:

5- Optional Parameter n range Optional Parameter n value can be from 1 to 8000.
Can store maximum 8000 Non-Unicode characters.

Example: (If you exceed the limit it will throw error)
DECLARE @Name VARCHAR(8001)

Resule:
The size (8001) given to the type 'varchar' 
exceeds the maximum allowed for any data type (8000).

Optional Parameter n value can be from 1 to 4000.Can store maximum 4000 Unicode/Non-Unicode characters

Example: (If you exceed the limit it will throw error)
DECLARE @Name NVARCHAR(4001)

Resule:
The size (4001) given to the parameter '@Name' exceeds the maximum allowed (4000).
6 - When optional parameter
n not specified
It will consider as 1

Example:








Note: some time I face this would be the
root-cause when you not specify size then,
it will take automatically one and will make
sub-string of your actual string and your logic
would be wrong. So be careful about this.

It will consider as 2

Example:

7 - When
Use?
If you are sure that your column or variable data not storing any Unicode character then you must use varchar.If you are not sure, or your column contain multiple language(Unicode characters) then you must use nvarchar.
8- StorageIt will take one byte per character plus two bytes extra for defining offset.
It will take two byte per character plus two bytes extra for defining offset.

Other Interview question related to Varchar and Nvarchar:

What would be the output of the following script?
DECLARE @Name VARCHAR(20)
SET @Name = 'विकास अहलावत'

SELECT @Name

Ans: Output would be "????? ??????"


What would be the output of the following script?
DECLARE @Name NVARCHAR(20)
SET @Name = 'विकास अहलावत'
SELECT @Name

Ans: Output would be "????? ??????"


How will you print or save name as 'विकास अहलावत'?
Ans: To save Unicode character we need to take data type nvarchar and string must be with "N", otherwise you will lost the even you have Nvarchar data type you can see in the above question

DECLARE @Name NVARCHAR(20)
SET @Name = N'विकास अहलावत'
SELECT @Name
Output would be : विकास अहलावत

Comments

  1. Superb..nice concepts

    ReplyDelete
  2. Thanks for your kind comment..

    ReplyDelete
  3. Working with nvarchar isn't much different, assuming your client software is built to handle Unicode. SQL Server will transparently upconvert a varchar to nvarchar, so you don't strictly need the N prefix for string literals unless you're using 2-byte (i.e. Unicode) characters in the literal. Be aware that casting nvarchar to varbinary yields different results than doing the same with varchar.

    ReplyDelete
  4. Useful information. Lucky me I discovered your website accidentally, and I am stunned why this twist of fate did not happened in advance! I bookmarked it.

    ReplyDelete

Archive

Contact Form

Send