SQL ordering logic?

Business Central/NAV Technical Forum
1

Hi,

I’m quite ignorant about SQL so I haven’t a clue about what is happening to a customer of mine.

They have just migrated from a native NAV 2.60 database to a SQL 5.0. There is a table which in the native DB used to be ordered like this:

ARTI
ASIA2007
ASTECH
ASTER
AST-AFFIST
AST-AMM
AST-CDA
AST-CDG
AST-CTS
AST-EVENT
AST-RETI
AST-SEGR
ATEC-GENER
ATTORI
A-ALLATTAM
A-ASPETTAT
A-ASSEMBL
A-FERIE
A-INFORTUN
A-LUTTO
A-MALATT
A-MATERNIT
A-MATRIMON
A-MAT-ANT
A-MAT-FAC
A-PERMELET
A-PERMESSI
A-PERMSIND
A-PERM-NR
A-RECUPERO
A-SCIOPERO
A-VISITAM
BBAS

Now, in sql the order looks like this:

A-ALLATTAM
A-ASPETTAT
A-ASSEMBL
A-FERIE
A-INFORTUN
A-LUTTO
A-MALATT
A-MAT-ANT
A-MATERNIT
A-MAT-FAC
A-MATRIMON
A-PERMELET
A-PERMESSI
A-PERM-NR
A-PERMSIND
A-RECUPERO
ARTI
A-SCIOPERO
ASIA2007
AST-AFFIST
AST-AMM
AST-CDA
AST-CDG
AST-CTS
ASTECH
ASTER
AST-EVENT
AST-RETI
AST-SEGR
ATEC-GENER
ATTORI
A-VISITAM
BBAS

I’m aware that sql orders code fields with a different logic, but… which logic? There doesn’t seem to be any here! [^o)]

2

Looks to me like the ‘-’ character is being ignored for sorting purposes.

My (limited) understanding of NAV is sorting is based on the chosen key. Is it possible the key chosen for sorting is different than the column being shown in this example?

3

NAV native sorting is defined in fin.stx. You can take a look at that file to see native sorting. SQL sorting it’s based in collation. Sorting using collation will push some non alphanumeric chars to be before letters. Native sorting puts push some non alphanumeric and the end. So you will have 2 sort methods. This is one of the reasons to check before making upgrade from Native to SQL.