Hi, I’ve seen here in the forum the new SQLIndex property being mentioned, and I’ve been playing around with this a little. For instance, I went in to table 357 - Document dimension, and on the primary key I added Document No.,Table ID,Document Type,Line No.,Dimension Code in the SQLIndex field. Now, if I open the table in SQL I can see it being sorted in Document No. order first, but if I open the same table in Navision it is still sorted in Table No order first. Does this means that the SQLIndex field actually creates a second key in SQL? How could the sorting be different otherwise depending on where it is opened? My real question is this though: would adding the SQLIndex as mentioned above solve the locking/performance problem that we see in regard to this table (issue described in topic http://www.mbsonline.org/forum/topic.asp?TOPIC_ID=16272). Thanks Daniel
when you in navision set currentkey function navision interprets it to sql statement of sortitby. So SQL sorts it on the fly.
The intention with the property is to create better indexes on SQL Server without changing Navision application code. Many (most) composite Navision keys do not have a good layout on SQL Server, especially those with low cardinality values as the first field, e.g. Type in “Type, Document No., Line No.”. Any Option or Boolean is a bad choice at the begining of a key. This Key property allows you to re-order the fields of the SQL Index that corresponds to this Navision key - or change the Index completely. Navision can continue to use SETCURRENTKEY specifying the fields in the Key list as normal even though the SQL Index for that key may be different. The ORDER BY of SQL statements still follows the Navision key - so if the SQL Index has been altered there may be a sort necessary in SQL Server if it chooses that SQL Index for data retrieval. That is part of SQL Server’s plan choice (The ORDER BY issue is likely to change in future versions of Navision). The point is that indexes are created in SQL that are optimal for SQL Server, and not for Navision Server which is what the original keys are based on and developed against (where cardinality is not an issue for example, because there are no statistics used and no plans - no cost based optimizer). The SQL Index fields you specify for a Key will be used directly for the SQL Index in SQL Server - they will not be interspersed with primary key fields, as Navision keys are. If the fields you specify contain all primary key fields, the SQL Index can be created as UNIQUE because it will contain combined unique values. If not, it will be created as NONUNIQUE. Setting this property does not implicitly solve performance problems unless the problem is down to a bad Navision key which is replaced with a better index. It depends what your performance problem is caused by. Changing an index may still leave you with blocking, deadlocks or bad execution plans etc, if it does not address that problem. The sorting you see outside Navision depends on the ORDER BY used in the SELECT you or a tool issued. If no ORDER BY it is undefined but will normally follow the clustered index. Within Navision the sorting continues to be the Navision key, not the SQL Index fields.
Or in easier words: SQL server is using the SQL Index (if possible) to fetch the records and the result set for Navision will again be sorted by the selected Navision Index. I hope that I do not talk garbage, Robert. You ARE the guru.
Those were easier words. I try to be less verbose, but it never works. I think the Guru is David though 