SQL SERVER – Storing Data in Hindi, Chinese, Gujarati and Tamil (or any other) Language in the Same Table

Long time ago, I have written a blog which talks about multi language support available with SQL Server.

SQL SERVER – 2005 – Multiple Language Support

I have been getting below questions on regular basics and it still baffles me:

1. How do I pass Hindi parameters in SQL Server procedure as I want to insert a row with Hindi text?
2. What is the collation I need to use if I have to store Tamil in SQL Server tables?
3. I am inserting the data in Gujarati but while selecting I am getting “???”

Here are some basic rules which you may want to keep in mind. While storing Unicode data, the column must be of Unicode data type (nchar, nvarchar, ntext). Another rule is that the value must be prefixed with N while insertion.

If you are using a stored procedure then while passing the parameter to stored procedure make sure that N is used before the string. Also make sure the parameter declared within the stored procedure which are used for carrying those variable are also defined as Unicode.

Here is the sample script and the output which can make sure understand the things. I have used Google translate to do translation and I am not sure how accurate it is in Kannada, Tamil and Telugu.

use master
go
If db_id('SQLAuthority') is not null
drop database SQLAuthority
go
set nocount on
go
Create database SQLAuthority
go
use SQLAuthority
go
Create Table MultiLanguage_Will_Not_Work (i int, j varchar(10), k varchar(100))
go
-- Hindi
Insert into MultiLanguage_Will_Not_Work values (1, 'with N', N'मेरा नाम Pinal है')
go
-- Tamil
Insert into MultiLanguage_Will_Not_Work values (2, 'without N', 'என் பெயர் PINAL ஆகிறது')
go
Select * from MultiLanguage_Will_Not_Work
go
Create Table MultiLanguage (i int,j varchar(10), k nvarchar(100))
go
-- Gujarati
Insert into MultiLanguage values (1, 'with N', N'મારું નામ પિનલ છે')
go
-- Telugu
Insert into MultiLanguage values (2, 'without N', 'నా పేరు PINAL ఉంది')
go
-- Kannada
Insert into MultiLanguage values (3, 'with N', N'ನನ್ನ ಹೆಸರು ಪಿನಾಲ್ ಆಗಿದೆ')
go
Select * from MultiLanguage
go
use master
go
drop database SQLAuthority
go

Here is the output

Same would work for any language as well. It is important for us to know the basics and this blog post is one of many basics blogs that I plan to write. Do let me know which topic you would like me to revisit as part of this – back to basics series? More ideas you give me, more interesting the series can be.

Reference: Pinal Dave (https://blog.sqlauthority.com)