-
Module 1: DATA AND PROCESS MODEL
-
Module 2: MASTER TABLES AND PAGES
-
Module 3: DOCUMENTS
-
Module 4: POSTING
-
Module 5: FEATURE INTEGRATION
-
Module 6: REPORTING
-
Module 7: STATISTICS
-
Module 8: DIMENSIONS
-
Module 9: ROLE TAILORING
-
Module 10: INTERFACES
-
Module 11: WEB SERVICES
-
Module 12: TESTING AND DEBUGGING
-
Module 13: SQL SERVER OPTIMIZATION
- Lesson 1: SQL Server for Microsoft Dynamics NAV
- Lesson 2: Representation of NAV Tables and Indexes in SQL Server
- Lesson 3: Collation Options
- Lesson 4: SQL Server Query Optimizer
- Lesson 5: SQL Server Query Optimizer
- Lesson 6: Data Access Redesign
- Lesson 7: C/AL Database Functions and Performance on SQL Server
- Lesson 8: Bulk Inserts
- Lesson 9: Locking, Blocking, and Deadlocks
- Lesson 10: SIFT Data Storage in SQL Server
- Lesson 11: SQL Server Profiler
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
