SIFT Maintenance for NAV and SQL Server v1.06

When performing transactions in NAV so called SIFT (Sum Index Flowfield Technology ™) tables (SQL Server option only) are used to save aggregated values about Sum Index Fields to be used in FlowFields which are providing these values.

Usually many of these SIFT records could contain only "sums" of value zero. These records are not required to provide the FlowField feature in NAV and should be erased periodically to keep the number of records within the SIFT tables as small as possible, to increase the NAV system's performance.

With NAV this kind of maintenance could only be done by using the "Table Optimizer". Unfortunately, this feature could not be scheduled to run automatically, and as it additionally re-creates all indexes, the processing could be rather time consuming - and while running, the performance is remarkably decreased; the system is virtually offline, users will be blocked.

It a lso has t o be mentioned that the "Table Optimizer" also may reset all kind of index-optimizations which were implemented on SQL Server site!

The stored procedure usp_delzerosift deletes those SIFT records, where all sum values are equal to zero. As this procedure could be used within a SQL Server Agent job, it could be used for periodic and automatic SIFT maintenance.

usp_delzerosift determines the current structure of SIFT tables dynamically "on the fly" - no programming or scripting required.

Hence, usp_delzerosift is a simple but convenient utility to keep SIFT tables small and performing better!

--- Changes in 1.03 ---
Fixed some bugs regarding data-type overflow errors

--- Changes in 1.04 ---
An error could occur when tables were created which "look like" SIFT tables, but aren't. Fix: Enhanced validation of SIFT tables. (thank you Stephen!)

--- Changes in 1.06 ---
Several minor changes and fixes.