Numbering in a code field in calssic and sql

iqbalmad
iqbalmad Member Posts: 180
in NAV/Navision Classic Client
Hi everyne,

In navision classic (local database), if you put a number in the code field, it looks like this:

1, 2, 3, 4, 5, 6 , 7 , 8, 9, 10, 11,......

In navision classic (sql database), if you put a number in the code field, it looks like this:

1, 11, 2, 3, 4, 5, 6 , 7 , 8, 9, 10, ......

why is that?

Answers

  • Slawek_Guzek
    Slawek_Guzek Member Posts: 1,692
    This is the difference between SQL Server sorting and classic NAV database sorting.

    NAV sorts text/code fields having a numbers only in them like a numbers. SQL sorts them as text.
    Slawek Guzek - www.yitron.co.uk
    Business Central, MS SQL Server, Wherescape RED;
  • iqbalmad
    iqbalmad Member Posts: 180
    is there a way to change this sql server sorting?
  • NavNab
    NavNab Member Posts: 181
    Usually we use codes like 001, 002, 003, 004..., 010, 011... to have the correct sorting in SQL.
  • Slawek_Guzek
    Slawek_Guzek Member Posts: 1,692
    There is, but there are also negative consequences of doing that

    To change the sorting order go to field's property, find SQL Data Type property, and change it to an integer. However, you will only be able to do so if all current data stored in the column can be converted to an integer. Also once done you will not be able to store in the field anything but an integer - that's the first, immediately visible drawback.

    Another potential impact may be the performance, this may not surface straight away.
    Slawek Guzek - www.yitron.co.uk
    Business Central, MS SQL Server, Wherescape RED;

Categories