NAV2016 internal record size nearly double that of 2009?

(0) Share

Report

Posted on by rsaritzky

Hi all,

I'm testing an upgrade from NAV2009 to NAV2016. We have 2 addons - Lanham E-Ship and ChargeLogic EFT. Both of these addons add a number of custom fields to Table 112 "Sales Invoice Header". In addition we have about 6 of our own custom fields.

All of these fields fit within the 4000-byte limit of NAV2009.

In NAV2016, the standard NAV Table 112 adds 4 fields:

710	Document Exchange Identifier	Text	50
711	Document Exchange Status	Option	
712	Doc. Exch. Original Identifier	Text	50
720	Coupled to CRM	                Boolean

So I would expect the length of the record to be still somewhere around 4000 bytes - perhaps a bit more. BUT, when I merged the Lanham and Chargelogic fields (and our handful of custom fields) into NAV2016, I received this error:

The active fields in a record cannot take up more than 8000 bytes. The active fields in the Sales Invoice Header table take up 8056 bytes. You must reduce the number or the length of the active fields.

So, I'm wondering how/why this record is now double the size? All the other standard NAV fields are the same size. Is it the conversion of data to Unicode that makes the same record take nearly twice the space? I'm baffled by this, especially since we are planning to add another Chargelogic addon which adds even more fields.

Thanks,

Ron

*This post is locked for comments

I have the same question (0)

All responses (4)

Answers (3)

Verified answer

Alexander Ermakov 28,096 on at

Like (0)

Report

The record length depends on the version of SQL server. As far as I remember, it has been 8 kbyte limitation since ver 2000. If you compare the length of the fields and manually sum them up in both versions, would it give the same result?

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (0)

Report

Hi Ron,

It's really surprising...

From NAV 2013 onwards, records size has been doubled to 8000 bytes, however as you mentioned rightly, we are not able to add the similar fields, which we were able to manage in NAV 2009 with 4000 bytes, in NAV 2013 version onwards even with 8000 bytes.

https://msdn.microsoft.com/en-us/library/dd338980(v=nav.70).aspx

This is bit strange at the moment..

The only solution to overcome this problem would be to reduce the length of the fields (which are not in use)

or

create separate tables to accommodate the customization need. However the second option would certainly increase lot of work, in terms of changing the logic in posting routines, fields display in various pages or even in reporting.. However I really don't see any other possibilities at this point in time.

Best Regards,

Vishal Salot

Was this reply helpful? Yes No
Verified answer

rsaritzky on at

Like (2)

Report
Thanks Vishal,

After replies from a couple of other knowledgeable people (thanks to all of them), I found an MSDN article that explains the reason. In NAV2013 and later, all text and code fields now DO take up twice the space - instead of

(length of field + 1)

the fields take

1 + ((length of field +1) * 2)

Here's the MSDN article:

https://msdn.microsoft.com/en-us/library/dd301350(v=nav.90).aspx

I also received from another NAV expert (thanks, Rick Williams from Liberty Grove Software) a SQL script that provides the length (pretty close). There are some differences, such as adding the timestamp field and other minor differences, but I think that most people just want to know if they are close the 8000 character limit:

SELECT OBJECT_NAME (id) tablename , COUNT (1) nr_columns , SUM (length) maxrowlength FROM syscolumns GROUP BY OBJECT_NAME (id) ORDER BY OBJECT_NAME (id)

Your last suggestion of adding a separate table is what we had to do in NAV2009 for Sales Header. But we are hoping to simplify the code by combining our custom fields into Table 36 for our upgrade. Because we want to take advantage of the new ability to subscribe to events, we are taking the opportunity to streamline and fine-tune our customizations.

Thanks again for the feedback.

Ron

Was this reply helpful? Yes No
Suggested answer

David Singleton 325 on at

Like (0)

Report

This is because Unicode needs 16 bits per character compared to 8 bits for ASCII

Was this reply helpful? Yes No