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.
Only if the view definition uses an aggregate function or GROUP BY, the SELECT must include COUNT_BIG(*). The name of the column is no matter. Here it's called $Cnt but you can call the column what you want.
@Bjarne: Ok, thx. Does this mean that every indexed view has a $Cnt-field?
@David: Thanks, David. Also for the knee. It's something I'm not looking forward too. In 9 months, I will be able to play basketball again ... . Not really a 'fast recovery', is it?
Hey well done Eric,
There is a ton of info out there about Indexed views, but you have done a great job of finding the important stuff, and summarizing it all in one place.
PS I wish you a fast recovery on the knee.
$Cnt is used internal by the SQL Server to find out when the record can be removed from the indexed view again.