SQL SERVER – Storing a Non-English String in Table – Unicode Strings

Yesterday I had a meeting with my client  Comprehensive Database Performance Health Check, and they wanted to know how to store a non-English string in a table. Let us learn about it.

Let us assume that English is a default language for the database and we want to restore the foreign language words in the SQL Server table. When you want to store a foreign language in your SQL Server, you have to use the data type NVARCHAR for your column. If you do not use the datatype NVARCHAR, you will be not able to store non-English strings.

Let us see an example:

-- Creating a table
CREATE TABLE MultiLang 
	(Lang VARCHAR(100),
	Strings VARCHAR(1000),
	NStrings NVARCHAR(1000))
GO
-- Inserting the data
INSERT INTO MultiLang (Lang, Strings, NStrings)
	VALUES ('English', 'How are you?', N'How are you?');
INSERT INTO MultiLang (Lang, Strings, NStrings)
	VALUES ('Gujarati', 'તમે કેમ છો?', N'તમે કેમ છો?');
INSERT INTO MultiLang (Lang, Strings, NStrings)
	VALUES ('Hindi', 'आप कैसे हो?', N'आप कैसे हो?');
GO
-- Retrieve data
SELECT *
FROM MultiLang
GO
-- Clean up
DROP TABLE MultiLang
GO

Here is the output of the table.

You can clearly see that when we have Unicode datatype we are able to store the non-English string. The official recommendation to use nvarchar is when the sizes of the column data entries vary considerably and the string length may be greater than 4,000 byte-pairs.

Well, that’s it for today. Let me know if you have any questions or want to see this demonstration converted into a video. I will be happy to create another SQL in the Sixty Seconds video if you want me to create his blog post in the video.

Here are a few recent blog posts:

• Move TempDB for Performance – SQL in Sixty Seconds #107
• Do MAX(col) Scan Table? – SQL in Sixty Seconds #106
• Rollback TRUNCATE – Script – SQL in Sixty Seconds #105

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