Need of Nvarchar Over Varchar in SQL Server

In the real world not everyone knows the English language and the language of every person changes region to region.

Need of nvarchar

Suppose you have a website that shows the data only in English and you want that everyone can see your data in his/her language but the data of your website that comes from a database and again modified data stored in a database is only in English and you want to change your data depending on the selected region by the user.

I will explain that to you using a query so follow along using the following procedure.

Step 1: I will create a table named "bookstore" that will contain the book id and book name information using "book_id" and "book_name" columns.

create table bookstore

(

    book_id int identity primary key,

    book_name varchar(100)           

)

Step 2: Now I will store a string like "Accounting Management" in the "book_name" field using various languages, like English, Chinese, Japanese, Russian and Arabic.

insert into bookstore(book_name)values('Accounting Management')

insert into bookstore(book_name)values('會計管理')

insert into bookstore(book_name)values('会計管理')

insert into bookstore(book_name)values('Управленческий учет')

insert into bookstore(book_name)values('إدارة المحاسبة')

 

 

The output will look like:

 

Problem: How can you store the data in a different language?

Answer: In this scenario you need to use "nvarchar" as the datatype in your database.

Now I will create another column like "book_name" but using "nvarchar".

Alter table bookstore Add  book_name1 nvarchar(100)

And select the table as in the following:

Select * from bookstore

Now the table will look like:

Now is the time to insert the same values into a new column using "N" before the value that tells the database it is a Unicode value and in this case "N" only works with "nvarchar", not with "varchar".

 

insert into bookstore(book_name1)values(N'Accounting Management')

insert into bookstore(book_name1)values(N'會計管理')

insert into bookstore(book_name1)values(N'会計管理')

insert into bookstore(book_name1)values(N'Управленческий учет')

insert into bookstore(book_name1)values(N'إدارة المحاسبة')

Select the table using:

Select * from bookstore

Output in all languages

Summary

Now you can understand why we need nvarchar instead of varchar in SQL Server.