Maximum number of fields / Max. record size

DenSterDenSter Member Posts: 8,304
edited 2010-08-10 in Navision Attain
We're about to add like 30 fields to the Sales Header table, and someone asked me what the maximum is, which is kind of an important thing to know :) . I know there is a size limit peer record, but I can't find the document that described this.

I need to know the limit of the number of fields, or the maximum record size of a table. Any help would be appreciated.

Thanks in advance.

Comments

  • Luc_VanDyckLuc_VanDyck Member, Moderator, Administrator Posts: 3,633
    The max. recordsize in Attain is 4KB (Financials has a max. recordsize of 2 KB).
    No support using PM or e-mail - Please use this forum. BC TechDays 2024: 13 & 14 June 2024, Antwerp (Belgium)
  • reemareema Member Posts: 255
    Thank U very Much Luc Van Dyck,


    i need to know the limit of the number of fields, or the maximum record size of a table. Any help would be appreciated

    For above question u provided one answer. That's fine.
    Why dont u put next question answer?
    What is limitation of the number of fields in table?
    Thanks and regards
    reema
  • ara3nara3n Member Posts: 9,255
    reema wrote:
    Thank U very Much Luc Van Dyck,


    i need to know the limit of the number of fields, or the maximum record size of a table. Any help would be appreciated

    For above question u provided one answer. That's fine.
    Why dont u put next question answer?
    What is limitation of the number of fields in table?
    Thanks and regards

    the number of fields depends on the size of the fields.

    You could add
    5 text field of length 20. or 1 field of length 100.

    both will take same size.

    So the answer also depends on the size of the fields, not on the number of fields.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • DenSterDenSter Member Posts: 8,304
    reema wrote:
    Thank U very Much Luc Van Dyck,


    i need to know the limit of the number of fields, or the maximum record size of a table. Any help would be appreciated

    For above question u provided one answer. That's fine.
    Why dont u put next question answer?
    What is limitation of the number of fields in table?
    Thanks and regards
    The answer is: there is no limit in number of records
    The limitation is the size of the record, which is determined by the number of fields times the field size. You're going to be able to add many more Boolean type fields as Code20 fields.
  • reemareema Member Posts: 255
    Thank U Ara3n & Denster,

    Ara3n wrote
    " the number of fields depends on the size of the fields.

    You could add
    5 text field of length 20. or 1 field of length 100.

    both will take same size.

    So the answer also depends on the size of the fields, not on the number of fields
    "
    I could not understand u r answer properly.
    For eample I created One table
    I add 50 text fields of length 100,..........I want add some more text fields of length 100.
    like this how many fields can i add to that table . :-k

    thanks and regards
    reema
  • ara3nara3n Member Posts: 9,255
    zero.

    You can only add 39 fields of length 100.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • Luc_VanDyckLuc_VanDyck Member, Moderator, Administrator Posts: 3,633
    Or 78 fields of length 50 ;-)
    No support using PM or e-mail - Please use this forum. BC TechDays 2024: 13 & 14 June 2024, Antwerp (Belgium)
  • reemareema Member Posts: 255
    Thank U ara3n,


    Not zero. :lol:


    What u told Record Size should not cross above 4 KB.

    But what about company information Table record Size = 81290,
    Rounding method table record size= 8112

    this is a small example.
    Like above u have many tables . what is logic? :-k

    could u throw explanation
    reema
  • ara3nara3n Member Posts: 9,255
    What you are mentioning is how much data is taking up space on server.

    What I've been talking and you've asked for is the how many fields can you have on a table.

    Two different things.

    I feel like I'm lost in translation.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • Luc_VanDyckLuc_VanDyck Member, Moderator, Administrator Posts: 3,633
    Here is some info from the w1w1adg.pdf (available on the productCD):
    A.2 Specifications for C/SIDE Application Objects
    
    This section lists specifications for the five types of application objects in a C/SIDE
    database.
    
    Specifications for Tables
    
    Range for table object ID numbers                     1 -999,999,999 (A)
    Maximum number of characters in a table name          30
    Maximum table size                                    Infinite
    Maximum number of records in a table                  Infinite
    Maximum record size                                   4KB (C/SIDE Database Server), 8KB (SQL Server)
    Maximum number of fields in a record                  500
    Range for field numbers                               1 - 999,999,999
    Maximum number of keys for a table                    40
    Maximum number of distinct fields per key             20 for a primary key. The number of fields in the
                                                          primary key + the number of fields in a
                                                          secondary key which do not occur in the primary
                                                          key must always be less than or equal to 20.
    Maximum number of SumIndexFields per key              20
    Maximum number of characters in a text or code field  250
    Maximum size of a BLOB field                          2 GB
    Maximum number of characters in a field name          30
    
    (A) all application objects are identified by an ID number. there are restrictions, however, on the
    numbers you can use when you create your own application objects. Please contact your NTR for
    more information.
    
    No support using PM or e-mail - Please use this forum. BC TechDays 2024: 13 & 14 June 2024, Antwerp (Belgium)
  • reemareema Member Posts: 255
    Thank U Ara3n,

    I feel like I'm lost in translation. :?

    I understand what u told regarding howmany fields can i add in a table.

    I think that u told this information based on the record size . Yes.

    If it is Yes then what about that company information table record size.......... #-o

    I hope u can understand my problem now

    thanks and regards
    reema
  • David_SingletonDavid_Singleton Member Posts: 5,479
    You also need to take into account the actual length of the fields.

    So a boolean field is 4 bytes, a CODE field of 8 Characters uses 12 bytes, a text field of length 8 is 8 bytes.

    It really is quite complex to know exactly how many fields you can have.

    This information is extremely critical when designing high performance databases, since the incorrect design of Field type and length in Keys has a major performance impact that most developers these days just don't care about.
    David Singleton
  • jversusjjversusj Member Posts: 489
    Here is some info from the w1w1adg.pdf (available on the productCD):
    Maximum record size                                   4KB (C/SIDE Database Server), 8KB (SQL Server)
    

    I was just curious about this blurb - we are sitting on a SQL server and are still limited to 4KB max record size, even though the above suggests we should be good up to 8KB. What version was Luc referring to in his post?

    We have 3.7B objects, but are using 5.0 executables, and as i mentioned, a SQL backend. thanks!
    kind of fell into this...
  • ara3nara3n Member Posts: 9,255
    I've looked at the $ndo$dbproperty table and there is a maxrecsize field at is set at 4000. I'm thinking that field controls the limitation.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • DenSterDenSter Member Posts: 8,304
    Cool, 6 years later, almost to the day. That was probably one of my first posts :mrgreen:
  • ara3nara3n Member Posts: 9,255
    ara3n wrote:
    I've looked at the $ndo$dbproperty table and there is a maxrecsize field at is set at 4000. I'm thinking that field controls the limitation.


    I just checked and nope, it's controlled in the application.


    My guess they kept it the same for compatibility reasons.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • garakgarak Member Posts: 3,263
    as i know it's controlled in the application self.
    The native can only handle ~4000bytes.
    To be compatible with the native server the max. record size is ~4000bytes.
    The SQL Server self allow 8060 bytes.
    Do you make it right, it works too!
  • jversusjjversusj Member Posts: 489
    Thanks all,
    does anyone know if NAV2009 (which has no Native db if i am not mistaken) has been updated to allow for 8KB record size? i am curious because the system i am working in has several tables that are simply out-of-room. the sales header being one of them... yikes!

    ...and good posts are timeless! :)
    kind of fell into this...
  • ara3nara3n Member Posts: 9,255
    in 2009 it still has 4kb limit.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • David_SingletonDavid_Singleton Member Posts: 5,479
    jversusj wrote:
    Thanks all,
    does anyone know if NAV2009 (which has no Native db if i am not mistaken)

    Yes you are mistaken :mrgreen: NAV 2009 does have a native DB.
    David Singleton
  • David_SingletonDavid_Singleton Member Posts: 5,479
    jversusj wrote:
    Thanks all,
    does anyone know if NAV2009 (which has no Native db if i am not mistaken) has been updated to allow for 8KB record size? i am curious because the system i am working in has several tables that are simply out-of-room. the sales header being one of them... yikes!

    ...and good posts are timeless! :)

    in terms of out of space for tables, it might be time to take a serious look at the design of the table, and maybe a clean up is over due.
    David Singleton
  • jversusjjversusj Member Posts: 489
    jversusj wrote:
    Thanks all,
    does anyone know if NAV2009 (which has no Native db if i am not mistaken) has been updated to allow for 8KB record size? i am curious because the system i am working in has several tables that are simply out-of-room. the sales header being one of them... yikes!

    ...and good posts are timeless! :)

    in terms of out of space for tables, it might be time to take a serious look at the design of the table, and maybe a clean up is over due.

    obviously we need to take a hard look at what is in there and what can be done.

    this is what happens when you let people make any all mods they can think of.
    kind of fell into this...
  • MigMig Member Posts: 2
    If the criterias mentioned above are not met, the maximum number of fields in a table are[500].
  • DynamicsDaveDynamicsDave Member Posts: 6
    With regards to the record size, is there any way to find out what space is still available in a table in NAV i.e. how much of the 4K/8K(in newer versions) has been used?

    I know I can add Dummy Fields until I get the error message when saving the table, but I was wondering if there was a more efficient way of finding this information?

    I thought the Record Size field in the Table Information virtual table may have been the value I was looking for but when I filter to Table 37 in this table, there is a different Record Size value for the table in each of the companies in the database, and some of the other tables seem to have a Record Size greater than 8K.

    Any pointer would be great.

    Thanks in advance,

    Dave
Sign In or Register to comment.