Why does Navision maintain each index as unique?

mmaxgon

When I create an index in Navision which is not intended to be unique, primary key fields as added to the index and it is created as unique in SQL Server.

DenSter

Who knows why they did that. If I were to speculate I'd say they added the PK fields to each secondary key in the Native NAV database for sorting purposes. The NAV database is an ISAM based database system, so I guess for that system it is necessary to do this.

In the native database you don't see those fields, so you wouldn't have asked the question. Now that we have access to SQL Server we get a glimpse of how they did certain things, or at least WHAT they did. You can't change it anyway.

kriki

With Navision 4.00SP1 and up, you can tell SQL (with the properties on the keys) to
-maintain the Navision-key as a SQL-index or not (MaintainSQLIndex)
-maintain the Navision-SIFT in SQL or not(MaintainSIFTIndex)
-the SIFT-levels to maintain (SIFTLevels)
-to create another index in SQL (SQLIndex).
With this last one, you can remove the fields you don't need in SQL.

amer

There could be a many design reasons to attach primary key to every index. For example, Nav has excellent grid paging feature that dynamically gets data. So, when you open form with some huge table, data read response is very fast whatever sorting key you choose. When you insert some new record with sorting key chosen other than primary(eg. Entry No.) , it will immediately be positioned according to selected sorting order. That is possible because every index has primary key characteristic (by definition that it is unique).

Best Regards,
Amer