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 .
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:
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 . 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 .
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":
That's it, guys! Remarks are always welcome!
A new way to get familiar with the product ... and not a bad one.
A few months ago, we could read about the "Web Services Competition for NAV 2009" on the Microsoft Dynamics NAV UK Blog. If this is a serious competition or not, I don't know. But recently, I noticed Microsoft is organizing another contest: the Microsoft Dynamics NAV 2009 Demo Contest on Directions 2008 (Atlanta).
This contest seems a little bit more serious. There are even "official rules", prizes, guidelines, ... . As "cherry on the pie" or "nose of the salmon" or ... (whatever you English speaking people use as expression for something like "biggest opportunity") the top 3 finalists may present their demos at one of the keynote sessions during Directions 2008 in Atlanta (October 21st).
Not only partners are going to invest time to get familiar with the product, there will be 3 demo's where other partners can see and learn "how to sell NAV2009" . That's a double win for Microsoft.
Anyway, I'm not really into contests like this, but I sure like to be a spectator. It's may be the one reason that is going to get me out of bed to follow the (usually very early-in-the-morning-scheduled) Keynote sessions (as you might have figured - I'm not a Keynote fan either).
Besides having the honor to present in one of the Keynote sessions, there is also something to bring home - prizes!
Above that, the top three finalists will have a free entrance ticket to Directions 2008.
If you want to participate and qualify, you'll have to provide Microsoft with a demo script and PowerPoint slides by 11:59PM Pacific Time, September 14, 2008. Since it's only for partners (obviously), I'll keep the necessary information to participate confidential. Here is the link where you can read all about it.
I'm sure many of you will be "teased" by this opportunity ... and are competitive enough to participate. Well ... good luck and see you in Atlanta [J]!
Normally, I would have done an article about Sumindexfields on versions previous to 5.0 SP1, but I decided not to do that. The Belgian version of Microsoft Dynamics NAV 5.0 SP1 is just released, and the Belgian release is always pretty late. In fact ... I just checked ... every localized version is available right now. That's why I decided to dig a little bit into this version's new SIFT-technology on SQL Server first. Who knows I'm in the mood to write about the SIFT-technology in the previous versions, when I'm in the hospital in a few days (yes, I'm waiting for an operation on my knee ) ... .
What is SIFT?
SIFT is an abbreviation for "Sum Index Flow Technology". Simply described: it's a technology that stores sums in the background to be able to quickly get summarized amounts. For the developers: SIFT tables contain records with summed values grouped by the fields of the key where you defined the SumIndexField on the table with the amountfield that should be summed (wow). For sales: SIFT is a technology that distinguishes NAV from any ERP system. For functional consultants: SIFT are sums. For the somewhat more experienced technical consultant who cares about performance: SIFT is a technology for speeding up the reading performance of summed fields, but should be carefully looked at because speeding up reading performance usually means slowing down writing performance.
Main difference with old SIFT
The biggest change they did in 5.0 SP1 is the fact that instead of SIFT tables, there are now SIFT views, which are indexed. This means, there is an efficient way to get results from the view (because of the fact that the results are 'stored' in a certain way), but it also means that it's a cost to maintain the view. May be not comparable with the dedicated SIFT table in the previous versions, but still ... . This also means that the triggers that existed to maintain the SIFT tables ... are gone ... which is a good thing! Now, there has been written quite some posts about this by the Sustained Engeneering Team on their blog and also, there is a whitepaper available on partnersource. Definitely worth reading it.
Creating SIFT view
A SIFT view is created when you configure a SumIndexfield on a key ... indeed, just the same as before. With the property "MaintainSIFTIndex", you can disable or enable the SIFT view. If this property is FALSE, the SIFT view will disappear.
This example shows how NAV creates an indexed view:
CREATE VIEW GLEntry$VSIFT$1 AS
SELECT SUM(Amount) as SUM$Amount, AccountNo, PostingDate FROM GLEntry GROUP BY AccountNo,PostingDate*
CREATE UNIQUE CLUSTERED INDEX VSIFTIDX ON GLEntry$VSIFT$1(AccountNo,PostingDate)*
(pasted from the whitepaper)
In SQL Server Management Studio, a similar view looks like this:
It's very clear, isn't it? You can read it right away. It's just grouping by G/L Account and Posting date, and summing up all requested Amounts (SumIndexFields). We even have a 'Count' field, which indicates how many records exist for that grouping. But...
Hey, where are my SIFT buckets?
Gone! As described in the white paper: "The indexed view that is used for a SIFT key is always created at the most finely-grained level." That means: No SIFTLevels window in C/SIDE anymore, no buckets anymore, ... . Simplicity!
But what if you would like to create another level, which may be not as detailed. This could be beneficiary! Well, again, simple:
You see? It's just creating a new SIFT view on a new key. When you do this for the SIFT above, but only on "G/L Account No"-level, you get a SIFT view like this:
Now, when and how is it used in C/SIDE
In Flowfields, off course. How dare you ask that question?Everybody knows the Chart Of Accounts. You know contains some flowfields that are totalling the "G/L Entry"-records on this form. If you monitor it, you'll see SQL Server receives statements like this:
SELECT SUM("SUM$Amount"),SUM("SUM$Amount") FROM dbo."EN DEFAULT COMPANY$G_L Entry$VSIFT$1" WITH(NOEXPAND) WHERE (("G_L Account No_"='705000'))
So, it's just querying the SIFT view for getting the sum. Because of the fact that this view already has summed amounts, means that it's going to sum less values. But keep in mind the levels. Only the most detailed level is maintained. The statement above is going to sum only on G/L Account. This is the least detailed level. This level is not maintained anymore in 5.00 SP1, which means that (by default) it's going to sum up more records then before.
What if I disable "MaintainSIFTIndex"?
If we disable the MaintainSIFTIndex of the SIFT above, we expect C/SIDE to query the G/L Entry table directly. And indeed, this is the statement it's sending:
SELECT SUM("Amount"),SUM("Amount") FROM "EN DEFAULT COMPANY$G_L Entry" WHERE (("G_L Account No_"='705000'))
You can imagine this statement will have to sum more amounts then the statement above.
Is there another way where the SIFT views will be used?
Yep, it's still there: A statement that is often forgotten is the CALCSUMS statement. It's a way to use SumIndexFields without using (or creating) a flowfield on a table. To keep the same logic as above:
CLEAR(recGLEntry); recGLEntry.SETCURRENTKEY("G/L Account No."); recGLEntry.SETRANGE("G/L Account No.",'705000'); recGLEntry.CALCSUMS(Amount);
You see I'm not using a flowfield, but I am using the SumIndexField of my G/L Entry table. This is the resulting statement in SQL Server:
SELECT SUM("SUM$Amount") FROM dbo."EN DEFAULT COMPANY$G_L Entry$VSIFT$1" WITH (READUNCOMMITTED, NOEXPAND ) WHERE (("G_L Account No_"='705000'))
So, What about my ZeroSiftRecords?
Probably everyone has heard of the ZeroSiftRecords-phenomenon. In the old days (version prior to 5.0 SP1 ... so really not that old ) it could happen that SIFT tables contained records with summed values all equal to 0. The reason behind this was that every record in the main tables was deleted for that particular summed group. Sums of non existing records is 0.
Now, we're working with views. These views are maintained by SQL Server automagically. It's actually some kind of projection of the main table in some way (a summed way). SQL Server is clever enough to exclude the zero-records. So, no ZeroSiftRecords anymore from now on. This is one maintenance job less .
So what's that $Cnt-field for?
As you can see, there is also a $Cnt-field present in the SIFT View. It counts the number of "summing" records. But where is it used for. I tested a "Count"-flowfield without result:
SELECT COUNT(*)FROM "EN DEFAULT COMPANY$G_L Entry" WHERE (("G_L Account No_"='640100'))
A normal "COUNT" in C/AL code:
An "Avarage" flowfield:
No use of the $Cnt-field whatsoever. May be it's used internally, I don't know. I couldn't think of any functionality or way to see where it's used (may be you got a clue?).
SQL Server 2000
My opionion about SQL2000 users is: "hey guys, we're in the year 2008"... . But that's only my opionion.
Seriously, in this article it's stated very clear that maintaining an indexed view on SQL Server 2000 can be a time consuming process (because of the clustered index scans etc. ...) . Therefore, their recommendation is, when upgrading to 5.0 SP1, you should always consider upgrading to SQL Server 2005 as well.
Do I need SQL Server 2005 Enterprise edition?
If you read the comparison between the SQL Server 2005 Editions, you could conclude you need the expensive Enterprise edition to run Microsoft Dynamics 50 SP1. This is a wrong conclusion!
Because of the fact the "Indexed View Matching"-functionality is not used by Dynamics NAV, this is no issue. This was beautifully explained in this blogpost by (once again) the Sustained Engineering Team as a response to my question a week earlier .
Ok, that's it, guys. I hope you enjoyed it. Please feel free to drop a comment with .. any .. uhm .. Comment .
Ps, after this article was finished, I noticed another very explanary blogpost from the UK Team. You can find it here.