Microsoft Dynamics NAV - C/SIDE Solution Development -

Microsoft Dynamics NAV – C/SIDE Solution Development

Locus IT ServicesMicrosoft Dynamics NAV – C/SIDE Solution Development

Lesson 10: SIFT Data Storage in SQL Server

SIFT Data Storage in SQL Server

SIFT Data Storage in SQL Server : Use SIFT tables in Microsoft Dynamics NAV version 5.0 and older, to implement SIFT on SQL Server, and to store aggregate values for SumIdexFields for keys in the source tables. Starting with version 5.0 Service Pack 1, indexed views replace these SIFT tables. SIFT tables are no longer part of Microsoft Dynamics NAV. This section is preserved because developers who work with Microsoft Dynamics NAV are likely to encounter issues about SIFT tables in implementations of older versions of Microsoft Dynamics NAV. You must have a good understanding of how they work.

A SumIndexField is always associated with a key, and each key can have no more than 20 SumIndexFields associated with it. When the MaintainSIFTIndex property of a key is set to Yes, Microsoft Dynamics NAV regards this key as a SIFT key and creates the SIFT structures that are needed to support it.

You can associate any field of the Decimal data type with a key as a

SumIndexField. Microsoft Dynamics NAV then creates and maintains a structure that stores the calculated totals that are required for the fast calculation of aggregated totals.

In the SQL Server Option for Microsoft Dynamics NAV, this maintained structure is a typical table, but is called a SIFT table. These SIFT tables exist on SQL Server, but are not visible in the table designer in C/SIDE. As soon as you create the first SIFT table for a base table, a dedicated SQL Server trigger is also created and then is maintained automatically by Microsoft Dynamics NAV. This is known as a SIFT trigger. A base table is also a standard Microsoft Dynamics NAV table, instead of an additional SQL Server table that is created to support Microsoft Dynamics NAV functionality.

One SIFT trigger is created for each base table that contains SumIndexFields. This dedicated SQL Server trigger supports all the SIFT tables that you create to support this base table. The SIFT trigger implements all modifications that are made on the base table when a SIFT table is affected. This means that the SIFT trigger automatically updates the information in all existing SIFT tables after every modification of the records in the base table.

The name of the SIFT trigger has the following format: <base Table Name>_TG. For example, the SIFT trigger for table 17, G/L Entry is named CRONUS International Ltd_$G/L Entry_TG. Regardless of the number of SIFT keys that are defined for a base table, only one SIFT trigger is created. You create a SIFT table for every base table key that has at least one SumIndexField associated with it. Regardless of how many SumIndexFields are associated with a key, you can create only one SIFT table for that key.

Locus IT Project Management Office
What’s it?