It has been almost two years since I presented a successfull session at Directions USA about migrating from the Navision Native (C/Side or Classic) database to SQL Server. This session was repeated in EMEA half a year later.
You can download the slides here.
It was only recently that I found another thing you have to realise when you migrate. It was related to table 49 Invoice Post. Buffer.
Some of you might know that it is used in both codeunit 80 and 90 to combine G/L entries by (VAT) posting groups and some other stuff. This is done by doing a trick in the uniqueness of the records. If you want to add a criteria you must first add the field to the table and then to the keys.
A classic example is to add the posting description to the G/L Entries.
A native database can have up to 20 fields in the primairy key. However, the SQL database only allows up to 16 key fields in the clustered index. If you try to add more you will get this message:
Even though this table wil never be populated on SQL Server NAV maintains the table definition there.
As far as I know there is no solution for this other than reducing the number of fields.
As you know, this posting buffer table is used for grouping amounts and quantities, not for its sorting order. Because of that, there are some ways to solve this quickly and easily:
1. If not all fields are filled to the max, you could concatenate some of them into one combined field, replace the fields in the PK by the combined field, and modify the posting procedures (i.e. codeunits 80 and 90 in std. NAV) to work accordingly.
2. If this does not suffice, you could use a cryptographic hash algorithm on the combined field to transform it into a compressed representation of it (e.g. by using MD5, which is conveniently available by using the Navision Attain Hash component :-) ).
3. Alternatively, you should theoretically be able to use a lossless compression algorithm instead, but I haven't tried this yet.
Granted, the hashing method may not be waterproof, but the probability that the same hash value is generated by two different sets of field contents is ridiculously low, which probably makes it usable for Posting Descriptions, etc. (And then, of course, you could add e.g. two different combinations and resp. hashes to the equation; the probability of them both colliding at the same time will be even lower.)
Note that the hashing/compression method also works around another possible issue (both native and SQL); if you need to add more than 252 bytes of data into your primary key (which is the max. in NAV), hashing/compressing (part of) the fields will also be a way out.