VSift - The day after...

Some time ago a new technology for SIFT was introduced.

SIFT is a technology that makes it possible in NAV to use the flowfields with the powerfull flowfilter technology. It stands for Sum Index Flow Techology. In the classic database the flowfield data was kept at index level. SQL does not have these capabilities so they had to work their way around it.

As most of you know, the claccic way to do tis was with secondary tables which were updated though SQL triggers. These triggers where created and maintained by the finsql.exe and there have been some different versions. Maybe I will write more about that in a later blog.

But with the introduction of Service Pack 1 for Dynamics NAV 5.0 this changed. The sift tables are no longer maintaned and the triggers are abandoned.

Instead SQL maintains a View for each index on tables with SumIndexFields. SQL Views represent data from tables and are updated when ''viewew'. But when you add an index to a view, that information get's actually stored into the database. Microsoft has decided to use this built in technology instead of the old triggers and tables.

So the question is: is this a bad thing?

The answer is no. But don't expect very much performance improvements either.

Those of you who went to Convergence EMEA last year know that I did some performance comparissons between version 4, 5 and NAV2009. The results where never published becuase of political reasons but in some of my next blog posts I will use some information from those tests.

So how does it work:

Let's have a look at the table G/L Entry. It has 5 indexes where SumIndexFields are maintained

So let's look in the SQL Management Studio. Use the filter (http://dynamicsuser.net/blogs/mark_brummel/archive/2009/04/12/tip-3-filter-in-sql-management-studio.aspx) on the views to see the G/L Entry views


Notice that there are 5 VSift's numbered 1 to 4 and 8. They follow the Key numbering in NAV. This is different from the old SIFT tables which used to be numbered in order.


Expanding the Columns and Indexes learns us that all fields in the index are there including the SumIndexFields and there actualy is a clustered index.

And when reading the data

Voila, it is there

More VSIft in the next Blog posts.

Comment List