There are four character string data types in Microsoft SQL Server. They are:
- CHAR
- NCHAR
- VARCHAR
- NVARCHAR
You should definitely choose the best character string data type to suit your needs. But you might look at VARCHAR and NVARCHAR and wonder, what’s the difference?
Well, in this very brief tutorial we’ll answer this age-old question.
VARCHAR will use 1 byte to store each character in a character string, while NVARCHAR will use 2 bytes.
To understand the difference between VARCHAR and NVARCHAR, you need to understand a thing or two about how SQL will store character string data in memory.
Let’s think about the character string ‘Neptune’.
The word ‘Neptune’ has 7 characters. If we use VARCHAR as the data type, SQL Server will use 1 byte per character to store this value in memory. Therefore (if my math is correct), the word ‘Neptune’ will use 7 bytes of memory.
If we used NVARCHAR as the data type instead, SQL Server will use 2 bytes per character to store this value in memory. Therefore (using advanced mathematics), the word ‘Neptune’ would use 14 bytes of memory.
But why does NVARCHAR use 2 bytes per character? Because NVARCHAR is designed to store a character string value in multiple languages. It needs that extra space to store your value in several languages.
VARCHAR, on the other hand, only stores your character string in English and one other language.
You might waste space if you use the wrong data type
Now that you understand the difference between VARCHAR and NVARCHAR, think about how much wasted space you would have if you use NVARCHAR when you don’t need to.
If you want to store your character string data in just English, it would be a huge waste of space to use the NVARCHAR data type. If you think about it, each value would use twice as much memory as it would if you were to use VARCHAR. For example, the word ‘Neptune’ went from using 7 bytes with VARCHAR to using 14 bytes with NVARCHAR.
Using that extra space is fine if you know you’ll store your values in several languages. But if you won’t, you’ll waste so much space!
Next steps:
Leave a comment if you found this tutorial helpful!
I hope you enjoyed this very brief tutorial on the difference between VARCHAR and NVARCHAR. These data types are listed in the following FREE guide:
FREE EBook on SQL Server Data Types!
This guide discusses the most common data types you will likely encounter as a database professional. You should definitely understand these common data types if you want to perform your job well. Download the guide today!
Now that you understand the difference between VARCHAR and NVARCHAR, do you also know the difference between CHAR and VARCHAR?
I also have a full tutorial on all the character string data types. Check it out here:
SQL Server character data types (and the differences between them)
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
I hope you found this tutorial helpful. If you have any questions, leave a comment. Or better yet, send me an email!
