SIFT and Microsoft Dynamics NAV with Microsoft SQL Server

SIFT has also been implemented in the SQL Server Option for Microsoft Dynamics NAV. The way that SIFT is implemented on SQL Server has been changed with the introduction of the RoleTailored architecture. The following is a description of the way that SIFT is currently implemented in SQL Server.

Implementing SIFT in the SQL Server Option

A SumIndexField is always associated with a key and each key can have a maximum of 20 SumIndexFields associated with it. In this topic, a key that has at least one SumIndexField associated with it is a SIFT key. Any field of the Decimal data type can be associated with a key as a SumIndexField.

Microsoft Dynamics NAV uses "Indexed Views" to maintain SIFT totals. Indexed views are a standard SQL Server feature. An indexed view is similar to a SQL Server view except that the contents have been materialized (computed and stored) to speed up the retrieval of data. For more information about indexed views, see SQL Server Books Online.

Microsoft Dynamics NAV creates one indexed view for each SIFT key that is enabled. When you create a SIFT key for a table, you must set the MaintainSIFTIndex property for that key to Yes to enable the SIFT key and create the indexed view. After SQL Server has created the indexed view, it maintains the contents of the view when any changes are made to the base table. If you set the MaintainSIFTIndex property for that key to No, SQL Server drops the indexed view and stops maintaining the totals.

The indexed view that is used for a SIFT key is always created at the most finely-grained level. Therefore, if you create a SIFT key for AccountNo.,PostingDate, the database will store an aggregated value for each account for each date. This means that in the worst case scenario, 365 records must be summed to generate the total for each account for a year.

The following is an example of how Microsoft Dynamics NAV creates an indexed view for a SIFT key that consists of the "AccountNo., PostingDate" fields and generate the total for the Amount field.

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)*

The following C/AL code example retrieves a total.

GLEntry.SETCURRENTKEY("G/L Account No.","Posting Date");
GLEntry.SETRANGE(GLEntry."G/L Account No.",'1110');
GLEntry.SETRANGE(GLEntry."Posting
Date",DMY2DATE(1,1,2007),DMY2DATE(15,12,2007));
GLEntry.CALCSUMS(Amount);

The following code example shows how the same total is retrieved through an indexed view.

SELECT SUM(SUM$Amount) FROM GLEntry$VSIFT$1 WITH(NOEXPAND) WHERE
AccountNo=? AND PostingDate>=? AND PostingDate<=?

Note   These SQL statements are only shown to illustrate this feature and their implementation could be subject to change at any time.

See Also

Community Additions

ADD
Show: