Ho provato ad aggiungere il collate e funziona nel caso di una select semplice. Io precisamente ho una query ricorsiva di questo genere

with c(path_ids,path_descr, path_cod, id, lvl) as
( select '/'+cast(id as varchar(49)) as path_ids,
'/'+cast(descrizione as varchar(max)) as path_descr,
cast(codice as nvarchar(max)) as path_cod,
id, 1 as lvl

from dbo.PDC b
where b.ID_PDC_PADRE is null
union all
select cast(c.path_ids+'/'+CAST(b.id as varchar(4)) as varchar(50)),
cast(c.path_descr+'/'+CAST(b.descrizione as varchar(50)) as varchar(max)),
cast(c.path_cod+'.'+CAST(b.codice as nvarchar(50)) as nvarchar(max)),

b.id, (c.lvl + 1) as lvl

from dbo.PDC b join c on b.ID_PDC_PADRE = c.id)

select c.lvl,(c.path_ids +'/') as path_ids, (c.path_descr +'/') as path_descr, (c.path_cod) as path_cod,
CASE WHEN EXISTS (SELECT * FROM dbo.PDC c2 WHERE c2.ID_PDC_PADRE = b.ID) THEN 0 ELSE 1 END AS is_foglia,
b.ID,b.CODICE, b.DESCRIZIONE, b.ID_PDC_SEZIONE, b.COD_PDC_TIPO_SEZIONE, b.COD_PDC_CLASS_SOTTOC, b.DATA_INIZIO_VALIDITA,
b.DATA_FINE_VALIDITA, b.ID_PDC_PADRE, b.IS_SOTTOCONTO, b.NOTE, b.CC_PDC, b.RIF_ID_CONT_CLIFOR

from c join dbo.PDC b on c.id = b.ID
WHERE b.ID_PDC_SEZIONE IN (SELECT ID FROM dbo.PDC_SEZIONI WHERE ID_ATTIVITA>0)
order by c.path_cod COLLATE SQL_EBCDIC037_CP1_CS_AS;


In tal caso il sort che gli impongo sembra fregarsene del collate e mi ordina i dati secondo l'ordine ASCII di default. Non capisco perchè