Difference between VARCHAR and NVARCHAR in SQL Server

Hello guys, if you are confused between VARCHAR and NVARCHAR data types of SQL Server and wondering what is the difference between VARCHAR and NVARCHAR, or you have been asked this question on technical Interviews and you couldn't answer then you have come to the right place. There is a subtle difference between these two character data types in SQL Server, while both supports variable length, the VARCHAR data type is used to store non-Unicode characters while NVARCHAR is used to store Unicode characters. It also takes more space than VARCHAR. 

For example, in the case of VARCHAR, each character takes 1 byte but in the case of NVARCHAR, each character takes 2 bytes of storage, which means NVARACHAR is twice as expensive as VARCHAR type. 

In the past, I have shared many SQL Server-based interview questions, which are liked and loved by you guys. Today, also I am going to share a frequently asked SQL Server interview question about VARCHAR vs. NVARCHAR? While everyone who has worked in SQL Server knows about VARCHAR, not many SQL Server developers and DBA know about NVARCHAR, which makes this question interesting.

While we will look into the difference between these two, it's also worth noting the similarities between them. For example, both VARCHAR and NVARCHAR are character data types and used to store text or String values. Both are also variable-length data types, so storage size depending upon actual data stored. 

By the way, if you're not familiar with data types in Microsoft SQL server then I also suggest you check out these free Microsoft SQL Server online courses to learn more about fundamental data types in SQL Server. 






Differences  between VARCHAR and NVARCHAR in SQL Server

Here are a few important differences between VARCHAR and NVARCHAR data types in SQL Server. You can keep these differences in mind while choosing the right data type for your columns in a table or database. 

1. VARCHAR is a non-Unicode character data type with a maximum length of 8,000 characters, while NVARCHAR is a Unicode character data type with a maximum length of 4,000 characters.

2. VARCHAR literals are enclosed in single quotes, like 'John,' but NVARCHAR literals are prefixed with N also, for example,  N'John.'

3. In the case of VARCHAR data type, each character occupies 1 byte, while in the case of NVARCHAR, each character needs 2 bytes of storage, which means NVARCHAR is twice as expensive as VARCHAR.

4. Use of index can fail if you provide wrong data type, like in SQL Server, when you have an index over a VARCHAR column and present it a Unicode String, MSSQL Server will not use the index. If you want to learn more, you can always see these Microsoft SQL Server online courses to learn these fundamental SQL Server database concepts in depth. 


Differences  between VARCHAR and NVARCHAR in SQL Server




That's all about the difference between VARCHAR and NVARCHAR data types in SQL. You should always use the data type that will take less space. In SQL Server NVARCHAR takes more space than VARCHAR data type, almost 2x as much space as VARCHAR. 

You should use VARCHAR if you know that all your data would be in ASCII encoding, but if you are going to store Unicode string, like storing data from different languages. You need to use NVARCHAR to support Unicode data. NVARCHAR is a must if you intend to support internationalization (i18n)

Other SQL Interview Questions from this blog:
  • How to find the second highest salary in MySQL? (solution)
  • What is the difference between UNION and UNION ALL in SQL? (answer)
  • Difference between LEFT and RIGHT OUTER JOIN in SQL? (answer)
  • How to find duplicate records in a table? (query)
  • How to delete from a table using join in SQL? (tutorial)
  • Difference between TRUNCATE and DELETE in SQL? (answer)
  • Difference between self and equijoin in SQL? (answer)
  • What is the difference between View and Materialized View in the Oracle database? (answer)
  • How to find the second highest salary in Oracle using ROW_NUMBER? (answer)
  • Difference between WHERE and HAVING clause in SQL? (answer)
  • Difference between isNull() and Coalesce() in SQL Server? (answer)
  • 10 SQL Queries from Programming Job Interviews (queries)
  • 5 Courses to Learn SQL Server and T-SQL (Courses)

Thanks for reading this article so far. If you find this article useful, then please share it with your friends and colleagues. If you have any questions or feedback, then please drop a note.

No comments:

Post a Comment

Feel free to comment, ask questions if you have any doubt.