How to use ORDER BY, LOWER in SQL SERVER 2008 with data other than unicode

Question

The question concerns the Armenians. I am using SQL Server 2005, sorting SQL_Latin1_General_CP1_CI_AS, the data is mostly in Armenian, and we cannot use unicode.

I tested on ms sql 2008 with window sorting for the Armenian language (Cyrillic_General_100_), I found here, ( http://msdn.microsoft.com/en-us/library/ms188046.aspx ) but it did not help.

I have a function that sets hexadecimal values ​​and a lower function that takes every char in each row and converts it to lower form, but this is not an acceptable solution, it works very slowly, calling this function on every column of a huge table.

Is there any solution to this problem not using unicode and not working with hexadecimal values ​​manually?

UPDATE:

On the left side are mixed words of a word, sorted in the correct order and with subordinate representations on the right side. Hope this helps. Thank. Words are written in Unicode.

• ԱբԳդԵզ → աբգդեզ

• ԱգԳսԴԼ → ագգսդլ

• ԲաԴֆդԴ → բադֆդդ

• ԳԳԼասա → գգլասա

• ԴմմլօՏ → դմմլօտ

• ԵլԲնՆն → ելբննն

• ԶՎլուտ → զվլուտ

• էԹփձջՐ → էթփձջր

• ԸխԾդսՂ → ըխծդսղ

• ԹԶէըԿր → թզէըկր

Answer 1

One solution would be to create a computed column for each text column, which converts the value to Armenian matching and sets it to lowercase as follows:

Alter Table TableName
    Add TextValueArmenian As ( LOWER(TextColumn COLLATE Latin1_General_CI_AS) ) PERSISTED

.

, , SCHEMABINDING, , .

EDIT , , Accent. , Latin1_General_CS_AS Cyrillic_General_100_CS_AS, .

. , , , , , . -, , , , - Notepad ++, StackOverflow UTF-8, UTF-8. -, , : UTF-8 SQL Server. , SQL Server 2008 ( ) UTF-8. UTF-8, :

• UTF-8 ( , )
• UTF-8 . , SQL Server Unicode, Unicode. , .
• CLR- SQL Server UTF-8. Microsoft , SQL Server, . CodePlex. Books Online. , CLR SQL Server, , .

, , , , Unicode SQL Server.

If object_id('tempdb..#Test') Is Not Null
    Drop Table #Test
GO
Create Table #Test
(
    EntrySort int identity(1,1) not null
    , ProperSort int 
    , MixedCase nvarchar(50)
    , Lowercase nvarchar(50)
)
GO
Insert #Test(ProperSort, MixedCase, Lowercase)
Select 1, N'ԱբԳդԵզ',N'աբգդեզ'
Union All Select 6, N'ԵլԲնՆն',N'ելբննն'
Union All Select 2, N'ԱգԳսԴԼ',N'ագգսդլ'
Union All Select 3, N'ԲաԴֆդԴ',N'բադֆդդ'
Union All Select 4, N'ԳԳԼասա',N'գգլասա'
Union All Select 5, N'ԴմմլօՏ',N'դմմլօտ'
Union All Select 9, N'ԸխԾդսՂ',N'ըխծդսղ'
Union All Select 7, N'ԶՎլուտ',N'զվլուտ'
Union All Select 10, N'ԹԶէըԿր',N'թզէըկր'
Union All Select 8,N'էԹփձջՐ',N'էթփձջր'

Select * From #Test Order by ProperSort
Select * From #Test Order by Lowercase
Select * From #Test Order by Lower(MixedCase)

.

Answer 2

?

Msg 448, Level 16, State 1, Line 1
Invalid collation 'Cyrillic_General_100_'.

Try:

ORDER BY Name COLLATE Cyrillic_General_100_CI_AS

, :

select * from fn_helpcollations()
where name like 'Cyrillic_General_100_%'