Field Data Types

After you select an identification number and name for a field, you have to select an appropriate data type. You can use many different types of fields in the C/SIDE database system. Each type is designed to hold a specific kind of information, such as text, numbers, dates, and so on.

Fields in a record can be of the following types.

Data type Description Size

BigInteger Data Type

A 64-bit integer.

8 bytes

Binary Data Type

Contains binary data. The binary data is stored in the record. The size of the corresponding SQL data type, VARBINARY, is the number of bytes in the field’s value. (A)(B)

Maximum length is 250 bytes

BLOB Data Type

Binary Large Object. Used to store bitmaps and memos. Notice that the BLOB is not stored in the record, but in the BLOB area of the table.

The size of the corresponding SQL data type, IMAGE, is the number of bytes in the field’s value. (A)(B)

8 bytes in the record + size of BLOB data (maximum 2 GB)

Boolean Data Type

Assumes the values TRUE or FALSE. When formatted, a Boolean field is shown as "Yes" or "No". The size of the corresponding SQL data type, TINYINT, is 1 byte. (A)(B)

4 bytes

Code Data Type

An alphanumeric string, which is right-justified if the contents are numbers only. If letters or blanks occur among the numbers, the contents are left-aligned. All letters are converted to uppercase upon entry.

The field must be defined to be between 1 and 250 characters. The space used by a code field equals the maximum length of the text plus two bytes. The first of the extra bytes holds information about the length of the string, and the second byte stores alignment information.

In the SQL Server Option for Microsoft Dynamics NAV, code fields work in a different way. You can use the SQL Data Type property to indicate whether code fields can contain integers or text strings.

For information about the SQL Data Type property, see SQL Data Type Property. For more information about the sorting of numeric values in code fields, see Sorting Numerical Values.

The size of the corresponding SQL data type, VARCHAR, is 1 byte per character in the field’s value. (A)(B)

Maximum string length + 2 bytes

Date Data Type

A date value in the range from January 1, 0 to December 31, 9999. An undefined date is expressed as 0. All dates have a corresponding closing date. The system regards the closing date for a given date as a period that follows the given date but comes before the next normal date; that is, a closing date is sorted immediately after the corresponding normal date but before the next normal date.

The size of the corresponding SQL data type, DATETIME, is 8 bytes. (A)(B)

4 bytes

DateFormula Data Type

Used to verify the date entered by the user. The syntax is for example:

30D (=30 days)
CM+1M (=current month plus one month)
D15 (=on the 15th of each month)

4 bytes

DateTime Data Type

Represents a point in time as a combined date and time. The datetime is stored in the database as Coordinated Universal Time (UTC) and is always displayed as local time in Microsoft Dynamics NAV.

Local time is determined by the time zone regional settings used by your computer.

You must always enter datetimes as local time. When you enter a datetime as local time, it is converted to UTC using the current settings for the time zone and daylight saving time.

The DateTime data type does not support closing dates.

Stored as two 4 byte integers

Decimal Data Type

A decimal number between -10^63 and 10^63. The exponent ranges from -63 to 63. Decimal numbers are held in memory with 18 significant digits. The representation of a decimal number is a Binary Coded Decimal (BCD). The size of the corresponding SQL data type, DECIMAL(38,20), is 17 bytes. We recommend that you construct decimals that operate on numbers within the range of +/- 999,999,999,999,999.99. You can construct larger numbers in some cases, but overflow, truncation or loss of precision can occur.

(A)(B)

12 bytes

Duration Data Type

Represents the difference between two points in time, in milliseconds. This value can be negative.

8 bytes

GUID Data Type

Globally unique identifier (GUID).

16 bytes

Integer Data Type

Denotes an integer between -2,147,483,647 and 2,147,483,647. The size of the corresponding SQL data type, INTEGER, is 4 bytes. (A)(B)

4 bytes

Option Data Type

An option field is defined with an option string, which is a comma-separated list of strings representing each valid value of the field. This string is used when a field of type Option is formatted and its value is converted into a string.

For example, the Option field "Color" is defined with the option string "Red,Green,Blue". Valid values of the field are then 0, 1, and 2, with 0 representing "Red" and so on. When the "Color" field is formatted, 0 is converted into the string "Red", 1 into "Green", and 2 into "Blue".

The size of the corresponding SQL data type, INTEGER, is 4 bytes. (A)(B)

4 bytes

RecordID Data Type

Unique record identifier.

 

TableFilter Data Type

This data type is used to apply a filter to another table.

Currently, this can only be used to apply security filters from the Permission table.

 

Text Data Type

Any alphanumeric string. The field must be defined to be between 1 and 250 characters. The space used by a text field equals the maximum length of the text plus one byte. This extra byte is a used to hold the length of the string. An empty text string has the length zero.

The size of the corresponding SQL data type, VARCHAR, is 1 byte per character in the field’s value. (A)(B)

Maximum string length + 1 byte

Time Data Type

Any time in the range 00:00:00 to 23:59:59.999. A time field contains 1 plus the number of milliseconds since 00:00:00 o'clock, or 0 (zero), an undefined time. A time value is calculated in the following way:

Time = 1 + (number of milliseconds since 00:00:00). 

The size of the corresponding SQL data type, DATETIME, is 8 bytes. (A)(B)

A time field is stored as an integer (four bytes)

(A) The calculation of the size of a specific SQL Server record requires more than simply summing the sizes of the field values. Refer to Microsoft SQL Server documentation for further information.

(B) This is the SQL Server data type that Microsoft Dynamics NAV uses when it creates the Microsoft Dynamics NAV data type. For more information, see Identifiers, Data Types and Data Formats in the SQL Server Option for Microsoft Dynamics NAV.

NoteNote

In Classic Database Server, data is stored with a four byte alignment for performance reasons. The sizes of text, code, and binary fields (that can have variable lengths) are rounded up to the nearest value that is a multiple of four. For example, a 10-character text string occupies 12 bytes.

In addition to the fields discussed in this section, the C/SIDE database system also includes the following special types of fields that are used to retrieve data:

  • FlowField

  • FlowFilter

For more information, see FlowFields Overview and FlowFilter Overview.

Community Additions

ADD
Show: