Navision Keys <> SQL Index's

Steve

Hello All,

In version 4.x we were told that creating sql index's independent from Navision. (That is we would have 5 keys in Navision that were created and managed inside Navision and those 5 plus lets say 2 others created specifically inside SQL manager.) that Navision could become "out of sync" and require an object to be removed and recreated to correct. ???

I can see how this maybe a correct statement within the Navi 4.x environment since it is maintaining the index as well as sift as separate tables.

Is this or will this be the same type of problem in Navi 5.x since sift tables are more less going away in replace of sql views?

I hope all understand.

Thanks

kine

There is no change in that. Still, whole database structure is handled by NAV itself, if you make a modification from outside, it is not good for NAV (with exceptions...).

DenSter

You should never modify any indexes for any NAV tables directly on SQL Server. This is especially important for indexes that are created by the NAV executables. If you modify the index that is associated with a NAV key, you can end up with uncompilable objects.

For NAV databases, you should always create the keys inside the NAV table designer, and manage the SQL Index by modifying the SQLIndex and MaintainSQLIndex properties of the key inside the NAV table designer. This is the best way to keep table definitions up to date, as it will always be managed by the NAV executables.

Steve

Just to toss this into the mix, but what about create index views and using included fields within sql?

I agree and would not change a index that is directly related to a navi key, but more over questioning creating additional ones. Above and beyond the ones listed in Navi.

bbrown

I can't think of a situation where you would want to do this. I see plenty of situations where you may want a NAV key without an underlying SQL index (turn off MaintSQLIndex). But I don't see a situation where I would want a SQL index that NAV is not aware of.

Steve

Hello BBRown,

I agree, but from a performance aspect I found increased perfromance indexing a view with included fields then just creating a Navi key and including the calculated field as a sumindex. The results were 150% faster calculations verse the standard Navi key method.

I don't disagree with maintaining everything within Navision, but I 'm expolrering why there is such a difference? This could be as simple as our modifications, setup or the developers code within. I'm justtrying to make sense of what I have seen so far.

kriki

[Topic moved from Navision forum to SQL Performance forum]

azerty74

There are a number of things to keep in mind when thinking about Keys in NAV and Indexes on Sql.

-Are they needed, or only creating overhead on IUD operations ?
-Are the Selective enough, are the fields on the correct order ?
-Statistics, Fragmentation,...
-Remember the Fill Factor, espacially for HOT tables.
-Should the "Entry No." be the clustered key in NAV Ledger tables ?
-...