With the introduction of the Microsoft SQL Server option for NAV the specific way of sorting Code fields (the Navision way) was no longer possible when running NAV on SQL Server. The Dynamics Book wiki gives a good description on the differences not in the least because of the filtering examples.
As Code fields are text fields the basic sorting mode on SQL Server is a text sorting. However to provide some sort of numerical sorting for Code fields the SQL Data Type property was introduced having the following three options:
- Varchar (default)
- Integer
- Variant
In all years those I never did get to explore these different options until a couple of weeks ago. For those, like me, who also did not, some examples.
Varchar
Design: table with one field (PK) Varchar of Data Type Code with SQL Data Type Varchar (default).
Notes
- sorting is purely text sorting
- you can change the SQL Data Type from Varchar to Variant and the data in the column is changed accordingly
- records 010, 0102 however will become duplicates of 10, 102 so an insert error will be thrown
Integer
Design: table with one field (PK) Integer of Data Type Code with SQL Data Type Integer.
Notes
- sorting is purely numerical sorting
- records 010, 0102, 0A, A, AS, SA cannot exist in this table as these are not integer values
- you can change the SQL Data Type from Integer to Variant and the data in the column is changed accordingly
Variant
Design: table with one field (PK) Variant of Data Type Code with SQL Data Type Variant.
Notes
- sorting is both text and numerical sorting and the data is split in these two ‘modes’
- records 010, 0102 cannot exist in this table as these are converted to integer values 10, 102 (which already exist)
- you cannot change the SQL Data Type from Variant to Varchar or Integer, unless there are no rows in the table or unless the field contains only an empty string in every row.