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.
Other Interview question related to Varchar and Nvarchar:
What would be the output of the following script?
What would be the output of the following script?
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 - Syntax | varchar[(n|max)] | nvarchar[(n|max)] |
| 2 - Basic Defination | Variable-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 character | Varchar 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- Storage | It 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 "????? ??????"
DECLARE @Name NVARCHAR(20)
SET @Name = 'विकास अहलावत'
SELECT @Name
Ans: Output would be "????? ??????"
How will you print or save name as 'विकास अहलावत'?
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 : विकास अहलावत
Superb..nice concepts
ReplyDeleteThanks for your kind comment..
ReplyDeleteWorking 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.
ReplyDeletei like this
ReplyDeleteUseful 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