Wrong sort order string with "ک" letter in SQL Server sort order

Question

I have a problem with Order By in a SQL Server query and Persian language. I can't sort Persian text very well.

I use NVarchar(X) type and everything is correct even select and Like queries. but when I try to sort string field I see just letter "ک" is going at the end even after "ی" letter. Right now I'm using Persian_100_CI_AS collation.

For example

SELECT TOP (200) Id, Name
FROM Groups
ORDER BY Name DESC

The result is

ID       Name
------------------------------    
10071   کنترل کیفیت> مدیر
10018   کنترل کیفیت> پرسنل

Previous rows should not stay at the first row in case of descending order.

10040   وظایف - مدیریت
10031   واحد شبکه
10046   نیروی فنی - برق چاپ> پرسنل
10067   Iso 17025> پرسنل
10011    آزمایشگاه - فرمولها - شرایط خط - کنترل کیفیت - برنامه تولید - آمار - گزارش تولید
10043    آزمایشگاه - فرمولها - شرایط خط - کنترل کیفیت - آمار - گزارش تولید 2

Any help will be appreciated.

score 2 · Accepted Answer · answered Jun 18 '20 at 04:15

It's possible that your fields have different collations than you think. Run this query to see what do you have in your DB. Are they exactly Persian_100_CI_AS?

DECLARE @defaultCollation NVARCHAR(1000)
SET @defaultCollation = CAST(
      DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS NVARCHAR(1000)
  )

SELECT C.Table_Name,
     Column_Name,
     Collation_Name,
     @defaultCollation DefaultCollation
FROM   Information_Schema.Columns C
     INNER JOIN Information_Schema.Tables T
          ON  C.Table_Name = T.Table_Name
WHERE  T.Table_Type = 'Base Table'
     AND RTRIM(LTRIM(Collation_Name)) <> RTRIM(LTRIM(@defaultCollation))
     AND COLUMNPROPERTY(OBJECT_ID(C.Table_Name), Column_Name, 'IsComputed') = 0
ORDER BY
     C.Table_Name,
     C.Column_Name

If the collations are different, run this query which gives you Alter statements to run them and fix the incompatible collations issue.

DECLARE @defaultCollation NVARCHAR(1000)
SET @defaultCollation = CAST(
      DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS NVARCHAR(1000)
  )


select 'ALTER TABLE '
+ QUOTENAME(C.TABLE_SCHEMA)
+'.'+ QUOTENAME(C.Table_Name)
+' ALTER COLUMN ' +' [' +Column_Name+'] '
+  DATA_TYPE+'('+cast(character_maximum_length as varchar(10))+')' +' COLLATE Persian_100_CI_AS '
+(case IS_NULLABLE when 'YES' then 'NULL' else 'NOT NULL' end )+';'

FROM   Information_Schema.Columns C
     INNER JOIN Information_Schema.Tables T
          ON  C.Table_Name = T.Table_Name
WHERE  T.Table_Type = 'Base Table'
     AND RTRIM(LTRIM(Collation_Name)) <> RTRIM(LTRIM(@defaultCollation))
 --AND DATA_TYPE='nvarchar'
 AND character_maximum_length>0
ORDER BY
     C.Table_Name,
     C.Column_Name

There was another problem happens to indexes too. you have to copy data to another column and re-build non-clustered indexes. — Reza Arani, Jun 19 '20 at 05:23

Wrong sort order string with "ک" letter in SQL Server sort order

1 Answers1