Kurdish Sorani Letters sql server

Question

I am trying to create a database containing Kurdish Sorani Letters. My Database fields has to be varchar cause of project is started that vay.

First I create database with Arabic_CI_AS I can store all arabic letters on varchar fields but when it comes to kurdish letters for example

ڕۆ these special letters are show like ?? on the table after entering data, I think my collation is wrong. Have anybody got and idea for collation ?

Answer 1

With that collation, no, you need to use nvarchar and always prefix such strings with the N prefix:

CREATE TABLE dbo.floo
(
  UseNPrefix bit,
  a varchar(32) collate Arabic_CI_AS,
  b nvarchar(32) collate Arabic_CI_AS
);

INSERT dbo.floo(UseNPrefix,a,b) VALUES(0,'??','??');
INSERT dbo.floo(UseNPrefix,a,b) VALUES(1,N'??',N'??');

SELECT * FROM dbo.floo;

Output:

UseNPrefix	a	b
False	??	??
True	??	??

• Example db<>fiddle

In SQL Server 2019, you can use a different SC + UTF-8 collation with varchar, but you will still need to prefix string literals with N to prevent data from being lost:

CREATE TABLE dbo.floo
(
  UseNPrefix bit,
  a varchar(32) collate Arabic_100_CI_AS_KS_SC_UTF8,
  b nvarchar(32) collate Arabic_100_CI_AS_KS_SC_UTF8
);

INSERT dbo.floo(UseNPrefix,a,b) VALUES(0,'??','??');
INSERT dbo.floo(UseNPrefix,a,b) VALUES(1,N'??',N'??');

SELECT * FROM dbo.floo;

Output:

UseNPrefix	a	b
False	??	??
True	??	??

• Example db<>fiddle

Basically, even if you are on SQL Server 2019, your requirements of "I need to store Sorani" and "I can't change the table" are incompatible. You will need to either change the data type of the column or at least change the collation, and you will need to adjust any code that expects to pass this data to SQL Server without an N prefix on strings.