NAV2013R2 | SQL Integer Issues

It’s been a while since I had time to blog, and still I do not really have the luxury of time but there are at least two things important enough to share.

At the moment I am involved in a large upgrade project from NAV 5.0 to 2013R2 with a Multi-country NAV database of 100+ GB.

This entry will be about the issues NAV2013R2 has with using the SQL Integer option for NAV Code fields and the next entry will be about SIFT on primairy key fields.

Both will lead to performance issues if treated with the knowledge of previous versions.

SQL Server performance tuning for Dynamics NAV changes with each release of the product. This is why it is so hard to make easy rules to remember for developers. Most of the times the changes are good and the product performs faster if used correctly, as is the case with this release.

In Dynamics NAV it is possible to change the SQL DataType for a Code field from the default value VarChar to Integer.

This was done a lot by customers moving from the Classic Database to SQL Server for sorting purposes and it was never an issue.

Until this release.

For those of you who attended one of my sessions at Directions last year you might remember that I told that rather than reading flowfields row-by-row in a listpage NAV is now performing an OUTER APPLY returning a complete dataset using SQL Server power.

The issue in this scenario is when you change for example the properties of the No. field in the Customer table to Integer and you do not change it on all referenced tables SQL cannot handle the conversion.

This is a SQL Server issue more than a NAV issue and it will not be solved by Microsoft.

Here is a link to the issue on Mibuso.

http://mibuso.com/forum/viewtopic.php?f=32&t=58700&start=0&hilit=NAV2013+and+performance

The solutions are to either change all the fields to Integer which is close to impossible since you end up in tables like Default Dimensions or to just reverse the change back to the default value.

I did the latter and implemented a SQL script to add filler characters to solve the sorting issues.

Contact me if you want more information about the solution.

Advertisements

3 thoughts on “NAV2013R2 | SQL Integer Issues

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s