COUNT Function Returns Incorrect Results

Please see attached details which came to light while investigating another issue (


OK, it seems that I panic too much. It would appear that the problem doesn’t lie with my code, but with Navision’s COUNT function. On further investigation I found a link between the tables regarding the records that weren’t updated… all of these records were at the end of the tables. Therefore the update was getting so far through the table, and then stopping. Again, looking through this further, I ran a SQL query against one of the tables to get a record count, then I ran a COUNT against the table in Navision… BINGO!!! Different results for some bizarre reason. Sticking within Navision I then found the following: 1) The following code does not return the correct record count: rBACSLedg.RESET; MESSAGE('Records: ’ + FORMAT(rBACSLedg.COUNT)); 2) The following code does return the correct record count: rBACSLedg.SETRANGE(rBACSLedg.“Entry No.”,0,9999999); MESSAGE('Records: ’ + FORMAT(rBACSLedg.COUNT)); So, in actual fact an unfiltered table has less records than a filtered table! Am I alone in thinking that this is a tiny bit unusual? Has anyone come across this before? Do you know what causes this? This does not appear to be an issue with every table in every database. Other tables within this database were fully updated. Also, my local database updated every table successfully. Any further help would be greatly appreciated. Thanks, Simon

It is an optimization in Navision for SQL, along these lines: It is possible to do a fast record count against a table, on SQL, by getting a stored count from the system catalog for the table you want which gives a very fast count but is not 100% accurate. The table itself is not visited directly. This requires that there is no filter been set on the table - i.e. it is a count of all records. The reason the inaccuracy is tolerated, is because Navision will only do this if the current transaction type is either Browse or is both UpdateNoLocks and the table is not locked yet. When you run AL code the default transaction type is UpdateNoLocks. In this mode - you are always reading data in a read-uncommitted (inacurate) fashion until you lock a table: i.e. all reads on a table can be inaccurate and this includes record counts. This is why the inaccurate omptization is employed - you are reading possibly inaccurate data anyway. To avoid this you should lock the table first with LOCKTABLE. Then do the count - it will no longer use the quick catalog but will have to go to the table and do a COUNT(*), and will be accurate. Hope this was clear.

Don’t we have the function COUNTAPPROX to get the guesstimate of the number of records and use COUNT do get the EXACT number of records ? This is new stuff to me also.

Yes, COUNTAPPROX gives an approximate count and respects the current filter set (becuase it is using an estimated execution plan), but the optimization I mentioned is only used for non-filtered counts, i.e. all records, because that is readily available.