The main difference between VSIFT (Very Large Field Index Table) and SIFT (SumIndexFieldTable) indexes in Microsoft Dynamics NAV (Navision) lies in their purpose and usage

VSIFT (Virtual Sum Index Field Technology): VSIFT is an evolution of SIFT. Instead of storing the aggregate values in separate tables, VSIFT dynamically calculates these values on demand using SQL Server’s indexing and querying capabilities. This reduces the storage overhead and can be more flexible and efficient in certain scenarios.

SIFT (SumIndexField Technology): SIFT is a technology used to maintain aggregate sums for fields in indexed tables. It pre-calculates and stores the aggregate values (such as sums, counts, etc.) in separate tables to speed up queries involving these aggregates.

• VSIFT Indexes:
  • VSIFT indexes are specifically designed to index large text fields, such as Text and Code fields, in SQL Server.
  • They are used to improve the performance of queries involving these large text fields by allowing the database engine to quickly locate relevant rows.
  • VSIFT indexes are stored in separate tables in the SQL Server database.
• SIFT Indexes:
  • SIFT indexes, on the other hand, are used for storing aggregated data in a separate table to improve the performance of certain queries, particularly those involving SUM calculations.
  • They are typically created on fields that are frequently used in aggregate calculations, such as Sales Amount or Quantity fields.
  • SIFT indexes help accelerate queries that involve aggregating data, such as calculating totals or averages over large datasets.
  • SIFT indexes are also stored in separate tables in the SQL Server database.

In summary, while both VSIFT and SIFT indexes are used to improve query performance in Microsoft Dynamics NAV, they serve different purposes: VSIFT indexes are for indexing large text fields, while SIFT indexes are for aggregating data to speed up certain types of queries.

Difference between VSIFT and SIFT in BusinessCentral with a real example

Scenario: Tracking Sales Totals by Customer

Using SIFT (SumIndexField Technology)
Scenario: You need to frequently report on the total sales amounts by customer.

1. Table Definition: Define a table to store sales orders.
2. SIFT Index Setup: Enable SIFT on the SalesAmount field.

table 50000 "Sales Order SIFT"
{
    DataClassification = ToBeClassified;
    fields
    {
        field(1; "Entry No."; Integer)
        {
            DataClassification = ToBeClassified;
        }
        field(2; "Customer No."; Code[20])
        {
            DataClassification = ToBeClassified;
        }
        field(3; "Order Date"; Date)
        {
            DataClassification = ToBeClassified;
        }
        field(4; "Sales Amount"; Decimal)
        {
            DataClassification = ToBeClassified;
        }
    }

    keys
    {
        key(PK; "Entry No.")
        {
            Clustered = true;
        }
        key(CustomerKey; "Customer No.", "Order Date")
        {
            SumIndexFields = "Sales Amount"; // Enabling SIFT
        }
    }
}

Report/Query to Utilize SIFT:

page 50000 "Customer Sales Report"
{
    PageType = Report;
    ApplicationArea = All;

    dataset
    {
        dataitem("Sales Order"; "Sales Order")
        {
            setfilter("Order Date", '%1..%2', StartDate, EndDate);
            group("Customer No.");
            column("Customer No."; "Customer No.")
            {
            }
            column("Total Sales Amount"; Sum("Sales Amount"))
            {
            }
        }
    }

    requestpage
    {
        layout
        {
            area(content)
            {
                field("Start Date"; StartDate)
                {
                }
                field("End Date"; EndDate)
                {
                }
            }
        }
    }

    var
    {
        StartDate: Date;
        EndDate: Date;
    }
}

In this example, the SumIndexFields property on the CustomerKey key enables SIFT, which pre-calculates and stores the sum of the Sales Amount field for each customer.

Using VSIFT (Virtual SumIndexField Technology)
Scenario: You need to dynamically calculate total sales amounts by customer on demand, utilizing SQL Server capabilities.

table 50000 "Sales Order VSIFT"
{
    DataClassification = ToBeClassified;
    fields
    {
        field(1; "Entry No."; Integer)
        {
            DataClassification = ToBeClassified;
        }
        field(2; "Customer No."; Code[20])
        {
            DataClassification = ToBeClassified;
        }
        field(3; "Order Date"; Date)
        {
            DataClassification = ToBeClassified;
        }
        field(4; "Sales Amount"; Decimal)
        {
            DataClassification = ToBeClassified;
        }
    }

    keys
    {
        key(PK; "Entry No.")
        {
            Clustered = true;
        }
        key(CustomerKey; "Customer No.", "Order Date")
        {
        }
    }
}

Report/Query to Utilize VSIFT:

page 50000 "Customer Sales Report"
{
    PageType = Report;
    ApplicationArea = All;

    dataset
    {
        dataitem("Sales Order"; "Sales Order")
        {
            setfilter("Order Date", '%1..%2', StartDate, EndDate);
            group("Customer No.");
            column("Customer No."; "Customer No.")
            {
            }
            column("Total Sales Amount"; Sum("Sales Amount"))
            {
            }
        }
    }

    requestpage
    {
        layout
        {
            area(content)
            {
                field("Start Date"; StartDate)
                {
                }
                field("End Date"; EndDate)
                {
                }
            }
        }
    }

    var
    {
        StartDate: Date;
        EndDate: Date;
    }
}

In this example, the CalcTotalSalesAmount procedure dynamically calculates the total sales amount for a customer by using Business Central’s CalcSums method. This approach leverages VSIFT, where the sum is calculated on demand without pre-storing it in separate tables.

Summary

• SIFT: Uses pre-calculated and stored aggregate values for fast retrieval, suitable for frequent and predictable aggregate queries.
• VSIFT: Calculates aggregate values dynamically on demand, suitable for flexible and less predictable aggregate queries.

By understanding these examples, you can decide which technology to use based on the specific needs and query patterns of your Business Central application.