Collation

select name
      ,row_number() over(order by name collate Cyrillic_General_CI_AS) as result1
      ,row_number() over(order by name collate Cyrillic_General_BIN) as result2
  from (select 'Вася' union all 
        select 'вася' union all
        select 'Петя' union all
        select 'петя') as T(name)

name result1              result2
---- -------------------- --------------------
Вася 1                    1
Петя 3                    2
вася 2                    3
петя 4                    4

(4 row(s) affected)

select * from
  (select 'а'
  union all select 'А'
  union all select 'Б'
  union all select 'б'
  union all select 'в'
  union all select 'В'
  union all select 'ё'
  union all select 'Д'
  ) Q (F)
order by F collate Cyrillic_General_BIN