Table Record size

pvar

I am working on an upgrade from 3.6 to 2009 and I know there is a record size limit of 4 kb on the tables.

I am seeing the record size of sales header table in base 2009 is 2591 bytes. We have a number of custom fields totaling to 1398 bytes so the total is 3989 bytes below the size limit of 4 kb. But for some reason when I tried to add the custom fields I am getting an error saying 'the active fields fields cannot take up more than 4000 bytes' but it is less than 4000 bytes.

Does any one know if the record size calculation includes anything other than the field size or what could be the issue here?

Thanks

David_Singleton

pvar wrote:

I am working on an upgrade from 3.6 to 2009 and I know there is a record size limit of 4 kb on the tables.

I am seeing the record size of sales header table in base 2009 is 2591 bytes. We have a number of custom fields totaling to 1398 bytes so the total is 3989 bytes below the size limit of 4 kb. But for some reason when I tried to add the custom fields I am getting an error saying 'the active fields fields cannot take up more than 4000 bytes' but it is less than 4000 bytes.

Does any one know if the record size calculation includes anything other than the field size or what could be the issue here?

Thanks

How are you calculating the total field size. Most of the time the error is forgetting the 4 byte boundary. So for example a CODE 3 field actually takes up 8 bytes. Where as a TEXT 3 field takes 4 bytes.

pvar

You run the virtual table 'field' for table 36 and copy & paste the records to excel and add up the len column and that is how I found the total size.

Further testing revealed that Navision accepts only upto 3782 bytes and then give the error for 4000 bytes. Is it a bug?

David_Singleton

pvar wrote:

You run the virtual table 'field' for table 36 and copy & paste the records to excel and add up the len column and that is how I found the total size.

Which (as I said in my post above) is not the total record size. You are adding up the numbers wrong.

pvar

So how do you find out the actual total record size?

David_Singleton

pvar wrote:

So how do you find out the actual total record size?

Basically in the C/AL user guide there is an explanation of how to calculate each field. What I do is as you did, copy paste the data into excel. Then sort the columns and sort by Type (text, boolean,option, code etc). Then by length. The easy ones (boolean, option integer) just put "4" into a column, then create formulas for all the rest.

I suppose you could modify the Form in Navision with a nice complex case statement and some MOD 4 commands, but excel works fine.

David_Singleton

pvar wrote:

So how do you find out the actual total record size?

Basically in the C/AL user guide there is an explanation of how to calculate each field. What I do is as you did, copy paste the data into excel. Then sort the columns and sort by Type (text, boolean,option, code etc). Then by length. The easy ones (boolean, option integer) just put "4" into a column, then create formulas for all the rest.

I suppose you could modify the Form in Navision with a nice complex case statement and some MOD 4 commands, but excel works fine.

pvar

Is the 4K limit only for C/SIDE database? I saw in Application designer's guide that SQL database can have 8K record size.

kriki

[Topic moved from 'NAV 2009' forum to 'NAV/Navision' forum]

kine

There is official calculation for row size on MS SQL (step 1-5):

http://msdn.microsoft.com/en-us/library/aa933068(SQL.80).aspx

I have found one big problem: if you use SQL Data Type = Variant somewhere (e.g. on field No. of table Item or Customer), it is reported as size 8016 on MS SQL, thus leading to some problems...

David_Singleton

kine wrote:

There is official calculation for row size on MS SQL (step 1-5):

http://msdn.microsoft.com/en-us/library/aa933068(SQL.80).aspx

I have found one big problem: if you use SQL Data Type = Variant somewhere (e.g. on field No. of table Item or Customer), it is reported as size 8016 on MS SQL, thus leading to some problems...

But is that the same formula that Navision uses?

kine

That's the question... :-)

jversusj

pvar wrote:

Is the 4K limit only for C/SIDE database? I saw in Application designer's guide that SQL database can have 8K record size.

i asked this question at convergence and the answer was that it did not matter that SQL allowed for 8k record size because NAV was the bottleneck. The core of NAV is built with a 4k limit and the MSFT guy i spoke to said it would be that way until MSFT gets around to dismantling the true core of NAV and rebuilding it, which he said would probably be a version 8 thing. so, even if you have NAV2009 and SQL you are still limited to the 4k record size. i can't remember his name, but he was one of the tech guys presenting some of the sessions.

frustrating for us because we have so many obsolete add-ons. we can't just strip them out because there is code somewhere that may still call them. means that our next upgrade will actually need to be a bit of a reimplementation to clean it out as much as possible.

pvar

Thanks for the explanation. It's really frustrating that Microsoft didn't remove this Navision bottleneck even in 2009 while they keep adding more and more fields to the table :x

Because of this we are facing a big challenge in our upgrade and we are forced to remove a number of custom fields we added and reduce the size of a bunch of other fields and cleaning up the code in every other object that references these fields so that the application won't break after we upgrade. Huge task ](*,)

David_Singleton

Cleaning up old code is THE FIRST STEP in any upgrade. So in reality this is a blessing in disguise. Keep in mind that Navision is doing a SELECT * every time it retrieves data form SQL, so the more fields you have the slower Navision will be.