Microsoft Dynamics NAV - C/SIDE Solution Development -

Microsoft Dynamics NAV – C/SIDE Solution Development

Locus IT ServicesMicrosoft Dynamics NAV – C/SIDE Solution Development

Lesson 2: Representation of NAV Tables and Indexes in SQL Server

Representation of Microsoft Dynamics NAV Tables and Indexes in SQL Server

Representation of NAV Tables and Indexes in SQL Server : By default, Microsoft Dynamics NAV provides unique data for each company in its database. On SQL Server, each company in the development environment has its own copy of each table.

Representation of Microsoft Dynamics NAV Tables and Indexes in SQL Server

Each table in the Development Environment has a corresponding table in SQL Server for every company in the database, with a name in the following format:

Table Name Format Example
<Company Name>$< Table

Name>

CRONUS International Ltd_$G_L

Entry

However, you can share data across companies by setting the DataPerCompany table property to FALSE. In Microsoft Dynamics NAV terms, this is known as data common to all companies. When the DataPerCompany property is turned off, there is just one table in SQL Server that is accessed from every company in the database. The naming convention for these common tables on SQL Server is the same, but without the <Company Name>$ portion.

The Microsoft Dynamics NAV Development Environment uses naming conventions that comply with SQL Server, such as not using special characters. Some special characters are available in the table designer, and they are translated to comply with the character set that is used on SQL Server.

The table has several indexes that represent the keys that are designed and enabled in the table designer. The indexes have generic names in the following format.

Index name format Example
$<Index Number> $1, $2, and so on

However, the primary key index uses the following name format.

Primary key name format Example
<Company Name>$< Table

Name>$0

CRONUS International Ltd_$G_L

Entry$0

By default, Microsoft Dynamics NAV clusters the primary key. Also by default, Microsoft Dynamics NAV adds the rest of the primary key to every secondary index. This makes the indexes unique and complies with the best practices that are defined for SQL Server. Developers can make additional changes to the way indexes are defined on SQL Server by using the MaintainSQLIndex, SQLIndex, and Clustered properties on the keys that are defined in the table designer. 

To obtain a list of indexes and their definition in SQL Server, run the sp_helpindex stored procedure in a query window, as follows.

Code Example

sp_helpindex “CRONUS International Ltd_$G_L Entry” GO

Locus IT Project Management Office
What’s it?