Identifiers, Data Types, and Data Formats
This topic describes the identifiers, data types, and data formats that are used in the Microsoft Dynamics NAV 2013 R2.
Naming Identifiers
Identifiers for SQL Server tables and columns are based upon the table names and field names for the corresponding tables and fields of a Microsoft Dynamics NAV table definition. If you set a table’s DataPerCompany property to Yes, the SQL Server table name is prefixed by the company name. The two names are separated by the ($) symbol. For example, the SQL Server table name for the Customer table of the CRONUS International Ltd. company is CRONUS International Ltd_$Customer. If the DataPerCompany property of a table is set to No, there is no prefix.
The primary key of a Microsoft Dynamics NAV table is created in a SQL Server table as a primary key constraint. The name of the primary key is based on the table name with a suffix of $0, for example, CRONUS International Ltd_$Customer$0. Any secondary keys in a Microsoft Dynamics NAV table that must be created and maintained in SQL Server, that is, the MaintainSQLIndex key property is set to Yes, will have SQL Server indexes created that are named after an internal key ID with a $ prefix. Examples of this are $1 and $4.
If the database maintains SQL views for language IDs, the system creates a SQL view by prefixing the SQL Server table name with the Windows language ID. For example, if you want to refer to the Customer table in the CRONUS International Ltd. company in German (standard), the SQL view is DEU$CRONUS International Ltd_$Customer. For more information about multilanguage functionality, see Multilanguage Development.
If the database maintains relationships, Microsoft Dynamics NAV creates foreign key constraints using the SQL Server table name and TableRelation property information. The names of the constraints have the following format:
- <table name>$FK$T<referencing table ID>_F<referencing field ID>$T<referenced table ID>.
Here is an example using the Customer table:
- CRONUS International Ltd_$Customer$FK$T18_F107$T308.
When you create a Microsoft Dynamics NAV table with keys that contain SumIndexFields, indexed views are created to hold the precalculated sums. These views are named based on the company, table name, and key number. For example [CRONUS International Ltd_$G_L Entry$VSIFT$8].
Representation of Data Types
Every available Microsoft Dynamics NAV data type is mapped to an appropriate SQL Server data type in the tables for Microsoft Dynamics NAV. The following table shows which SQL Server data type is used for the corresponding Microsoft Dynamics NAV data type.
| Microsoft Dynamics NAV data type | SQL Server data type |
|---|---|
Integer |
INTEGER |
Option |
INTEGER |
Code(n) |
NVARCHAR(n) INTEGER SQL_VARIANT |
Text(n) |
NVARCHAR(n) |
Decimal |
DECIMAL(38,20) |
Date |
DATETIME |
Time |
DATETIME |
DateTime |
DATETIME |
Boolean |
TINYINT |
BLOB |
IMAGE |
DateFormula |
VARCHAR(32) |
TableFilter |
VARBINARY(252) |
BigInteger |
BIGINT |
Duration |
BIGINT |
GUID |
UNIQUEIDENTIFIER |
RecordID |
VARBINARY(n) |
Each of the SQL Server data types is created as NOT NULL except the IMAGE type, which allows NULL.
Compatibility of Data Types
Some of the SQL Server data types are compatible with other Microsoft Dynamics NAV data types. The following table shows the extended compatibility of SQL Server data types with Microsoft Dynamics NAV data types.
| SQL Server data type | Microsoft Dynamics NAV data type |
|---|---|
CHAR (n) |
Code(n) Text(n) DateFormula |
NCHAR (n) |
Text(n) |
NVARCHAR (n) |
Text(n) |
INTEGER |
Code |
TINYINT |
Integer Option |
SMALLINT |
Integer Option |
NUMERIC(p,s), MONEY, SMALLMONEY, REAL, FLOAT(n), DECIMAL |
Decimal Integer Option Boolean |
SMALLDATETIME |
Date |
BIT |
Integer Option Boolean |
TEXT |
BLOB |
NTEXT |
BLOB |