Non-English languages and SQL Server
Probably this is not important for English developers but is a common question for amateur non-English developers: When we want to retrieve our information from SQL server just get question marks! Sometimes Persian speakers try to send Persian characters via my blog contact page but as Community Server saves them in SQL Server I receive question marks! Although this question is answered many times but I receive many emails about it! So answer it again with some extra information.
The reason is based on database default language. To solve, you must use nchar, nvarchar and ntext data types for their columns. Also if you want to specify Unicode characters in your strings, must use N character before your string. Here is an example:
SELECT N'.NET is great!'
OK! It solves that problem but what about sorting for Unicode characters? Collation shows to SQL Server how it would arrange or compare data. Execute following commands on your SQL Server to find your default collation:
SELECT serverproperty('collation')SELECT databasepropertyex('master','collation')
My result was Arabic_CI_AS for both commands. Now execute this command on your database to find the list of all available collations on your SQL Server:
SELECT * FROM ::fn_helpcollations()
SQL Server uses your default collation for sorting and comparing. So what would you do to use another collation in your commands. Here is a simple example. I think that it's self-describing!
CREATE TABLE CollationTestForMyBlog
(
MyColumn VARCHAR(15) COLLATE Arabic_CI_AS NOT NULL
)
INSERT INTO CollationTestForMyBlog(Mycolumn)
VALUES ('کیوان')
SELECT MyColumn
FROM CollationTestForMyBlog
WHERE MyColumn = 'کیوان' COLLATE Arabic_CI_AS
I used a Persian (Arabic) string (My first name) for comparing in WHERE clause so applied Arabic collation for it.