SQL Server: Storing language specific data in SQL Server table field.

While working on multi language support we need to insert different language data in same field or different fields of a SQL server table.

There are two basic rules one need to keep in mind while storing multi lingual or Unicode data:

·         First is column must be of Unicode data type (i.e., nchar, nvarchar, ntext).

·         And second is that the value must be prefixed with N while insertion.

Below is the sample script with output to understand it better.

USE MASTER GO -- Drop and Create TestMultiLingualDB IF db_id('TestMultiLingualDB') IS NOT NULL       DROP DATABASE TestMultiLingualDB GO SET NOCOUNT ON GO CREATE DATABASE TestMultiLingualDB GO USE TestMultiLingualDB GO CREATE TABLE MultiLanguage (Id INT, Var_Field VARCHAR(50), NVar_Field NVARCHAR(50)) GO -- Insert Hindi Characters INSERT INTO MultiLanguage VALUES (1, N'यह एक शब्द है', N'यह एक शब्द है') GO -- Insert Punjabi Characters INSERT INTO MultiLanguage VALUES (2, N'ਇਹ ਇੱਕ ਸ਼ਬਦ ਦਾ ਹੁੰਦਾ ਹੈ', N'ਇਹ ਇੱਕ ਸ਼ਬਦ ਦਾ ਹੁੰਦਾ ਹੈ') GO --Select and see inserted values. SELECT * FROM MultiLanguage GO -- Drop TestMultiLingualDB USE MASTER GO DROP DATABASE TestMultiLingualDB GO

In the output you can see that value in Var_Field are saved as ????, even though in script N is prefixed while inserting. This is because field is of VARCHAR type.

Id          Var_Field                           NVar_Field ----------- ----------------------------------- ----------------------------------- 1           ?? ?? ???? ??                       यह एक शब्द है 2           ?? ??? ???? ?? ????? ??             ਇਹ ਇੱਕ ਸ਼ਬਦ ਦਾ ਹੁੰਦਾ ਹੈ

Note: If you are using a stored procedure to insert the Unicode data, then make sure that N is used before the string while passing the parameter to stored procedure. Also make sure the parameter declared in stored procedure is of Unicode type.