SQL Data Type Property in Dynamics NAV

Article
10/30/2017

Sets the data type that you want to allow in a code field.

Applies To

Code fields in Microsoft Dynamics NAV with Microsoft SQL Server.

Remarks

The values available are Varchar, Integer, BigInteger, and Variant. Leaving the value set at <Undefined>means that you accept the default setting, which is Varchar.

Note

The Variant option is represented by the SQL_VARIANT SQL data type that has been introduced in SQL Server 2000 and is not supported by SQL Server 7.0. This means that selecting Variant as the SQL Data Type property when creating or modifying a table on SQL Server 7.0 results in an error.

Varchar

If you set SQL Data Type to Varchar, all the values in the field are compared and sorted as character data, including numeric values.

Integer

If you set SQL Data Type to Integer, then the following are true:

All the values in the field are compared and sorted as integers. No alphanumeric values can be stored in the field.
If you enter negative values in the column outside Microsoft Dynamics NAV using external tools, they cannot be read into Microsoft Dynamics NAV.
The value "0" (zero) is used to represent an empty string in Microsoft Dynamics NAV.
Non-numeric code values or any numeric values beginning with "0" (zero) cannot be entered in the code field.

Variant

If you set SQL Data Type to Variant, then the following are true:

The values in the field are compared and sorted according to their base data type. Numeric values are sorted after alphanumeric values.
Data that is entered into the code field in Microsoft Dynamics NAV is stored as either the Varchar or Integer base data type, depending on the value that has been entered.
Any value beginning with "0" (zero) can be entered in the code field and is stored as an Integer base data type.

Note

You can change the SQL Data Type of a code field from Varchar or Integer to Variant and the data in the column is changed accordingly. You cannot change the SQL Data Type of a code field 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.

Partager via