The Key virtual table

The "Key" virtual table contains information about the keys that are defined in each table in the database. At least, that's what the bible (in other, less popular words: The Application Designer's Guide) says. Actually, that's about all it explains about the "Key" virtual table ... and I'm talking about the latest w1w1adg.pdf (5.0) you can download from partnersource here. It only explains that you can get info from the table ... .

But there are also other, undocumented possibilities (at least, I didn't find any documentation about it). Well, let's document what we can Wink.

What's in it?

The easiest way to look at the contents of a virtual table, is to create a new tabular form with the form wizard. This is because you can't see virtual tables in the object designer, and therefore, you can't "Run" them. Well, if you create a form, based on the Key table, put every field in the form, you get this:

These are the fields we're looking at:

  • TableNo
  • No.
  • Tablename
  • Key
  • SumIndexFields
  • SQLIndex
  • Enabled
  • MaintainSIFTIndex
  • MaintainSQLIndex
  • Clustered

The ones in bold are the interesting ones. You know them as properties of a key. Besides the "Keygroups" property, every property is present in the table. If you're working with versions below 5.0 SP1 (and chances are still high, off course), you're missing the SiftLevels property. But we'll talk about this in a seperate section.

So, can we only look at it?

No! You can edit the data in the table ... which means, you can change the properties of the keys by using this table. You can try this out in the form (see above) by just editing a property (like MaintainSQLIndex) and saving it by going to the next record.
One remark: you'll see that the object-properties have changed as well. This is no surprise. You changed the object's definition, so the object Date, Time and Modify-flag has to be changed as well.

Where could it be useful for?

Well, there are a number of things it could be useful for. Especially when you're doing SQL Tuning for customers. Let me try to line out a number of scenario's.

Scenario 1: MaintainSQLIndex

When I'm doing SQL Index Tuning, there are multiple ways to monitor "Index Usage". Out of these results, you can conclude which indexes are not used. It is quite easy to write some kind of codeunit to uncheck the checkmark in the MaintainSQLIndex field of a certain key. I did it once in a database for disabling about 160 indexes. The impact was quite heavy (in the positive sense...).

Scenario 2: Rebuild SIFT (versions below 5.0SP1)

In this post on mibuso, megawavez wanted to rebuild SIFT in some way. Mauddib went deeper into it in this post.

It comes down on playing around with the MaintainSIFTIndex property. If you uncheck it, the SIFT table is deleted. If you check it again, the SIFT table is created again, which means that it's there in the most "optimal" way (unfragmented, no zerosiftrecords, ...).

Scenario 3: Creating Clustered Indexes

In this download, kriki (Alain Krikilion) is using the key-table to fix the clustered key problem in 4.0Sp1. What was the problem: it could be that there were no clustered key's at all Indifferent. Believe me, you don't want that. (I hope I'm not telling rubbish here. I downloaded it a long time ago and didn't doublecheck it when writing this piece).


This is another pack of sleeves (like they say in dutch and probably makes no sense in English).

Up until version 5.0 (without service pack), "SiftLevels" was a fundamental part of SIFT. A snippet from the bible:

Understanding the relationship between buckets and SIFT levels is crucial to understanding the way that SIFT is implemented in the SQL Server Option for Dynamics NAV. The precalculated totals or sums for each SumIndexField column are stored in buckets in SIFT tables. The buckets correspond to the SIFT levels that are maintained and each SIFT level can generate many records that are stored with the same bucket number in the SIFT tables on SQL Server. The higher the bucket number the more detailed the SIFT level. The buckets and their corresponding SIFT levels can also be seen from within Dynamics NAV, even though they only exist in the SQL Server tables that are created to support SIFT: The precalculated totals from the different buckets are retrieved and aggregated to generate the sums or totals that are stored in the SumIndexFields.

In 5.0 Sp1 with the indexed views, there are no levels anymore! If you need more info, I handled this in one of my previous posts: What impact does my C/AL have on SQL - SIFT on 5.0 Sp1.

Back to topic: What does it have to do with the Key table? Well, the SiftLevels-property is not present in this table. You might wonder "hey, isn't it risky to play with the Key properties when you already tuned the SiftLevels? Isn't this going to "reset" the levels"? If you're working with the Key table, it is not!

I concluded this by disabling and enabling the "MaintainSIFTIndex". Both in the table designer and in the Key virtual table. Only with the key table, my SiftLevels remained intact. When you're tuning, this can be a big advantage, off course.

Is this the only virtual table where we can edit the data?

No, it is not. In my opinion, it's the most interesting one, but it definitely is not the only one. I did some brief tests with the field and object table, and there, it was also possible to change the content.

I have one side-remark with this. When you use the Key table, you notice that the object properties (date, time) are changed whenever you change a Key-property. You can extend your functionality by using the Object table as well: temorarily save the object properties, make the changes in the Key table, save it, and then, restore the Object properties by using the saved set. Easy, isn't it Smile.

Final remarks

It's good to know undocumented possibilities. It can give you more flexibility in your daily work ... and you might be more "productive". I only have two "buts":

  • But you have to know about its limitations as well.
    • For example: inserting in virtual tables is still not possible. You'll not be able to add keys, add fields ... . For the key table, it would have been nice to have had this possibility. Some of you might have noticed a bug in NDT. After you merge, some tables do not have any key anymore. Dynamically creating keys with the Key table might have been a workaround for this.
  • But it's never a good idea to rely on undocumented features, in my opinion. I don't want to be the one that recommends to built business critical functionality on things like this, when the possibility disappears in the next release.

That's it, guys! Remarks are always welcome!

Comment List