Category Archives: SQL

Field “Version List” in the Object-Table

Starting with NAV2013, Microsoft increased the size of the “Version List” field after having been hammered for years to increase it but this is not the cause of this topic.

I wanted to check how long the field was (bad memory!) so I checked the NAV virtual Field-table and found something strange (actually 2 things).

First the size was not 250 chars but only 248. Why? I don’t know. Well, 2 chars on 250 won’t make the difference anyway.

BUT what drew my attention was the data type of the field: It wasn’t “Text” like I expected but “OemText”!

I never saw this datatype (ahum … … Remember the bad memory!). So I checked if I could create a field in a new table using this datatype but I couldn’t. It didn’t show up in the list.

Good. Why do they use “OemText” and not “Text”. What is the difference. There must be one. So check the table in SSMS to see if it shows up.

What are the differences between a “OemText” and “Text” in SQL Server?

Well, we know that starting with NAV2013, text-fields are Unicode. That means the nvarchar-datatype in SQL.

But “OemText” is NOT using nvarchar but varchar in SQL! So the versionlist is NOT Unicode!

Oh. Also fields “Company Name”,”Name”,”Locked By” in the Object-table are “OemText”.


Timestamp field in SQL and NAV2016

In SQL you have the timestamp field. Each table in NAV has it, but it is not shown in NAV. In NAV2016 you have the possibility to show it as you probably already found out.

Important to know is that the timestamp field does NOT contain a time in it (talking about a bad name for a field…). It is just some versioning ID. Each time a record is inserted/updated, it the value is increased by 1. But not necessary for the same record. I have noticed that a value “x” is used only once for a record in the whole database! This means that there is max 1 record in the whole database with a certain timestamp value (if you update the record, it will get a new timestamp value and the old timestamp value will disappear from the database).

So when you need to know the records that where changed, get the records with a timestamp larger than the largest one that you found last time (you will need to save that value somewhere).

I did a small test:

I created a key on the timestamp-field (I gave it a different name (not “timestamp”)) and that was not a problem.

Not having a key on the field will mean that SQL has to scan the whole table to find the records you are searching for.