What is IncludedFields Property

In Microsoft Dynamics 365 Business Central (formerly Dynamics NAV), the “IncludedFields” property is a feature used in conjunction with non-clustered indexes to include additional fields in the index structure without adding them as key columns.

Here’s a breakdown of the IncludedFields property:

1. Non-Clustered Indexes: Non-clustered indexes are secondary indexes that improve the performance of queries by providing a quick lookup path for specific columns in a table.
2. Key Columns: Key columns are the columns that define the index key and determine the order of data storage in the index. Queries that include the key columns can efficiently utilize the index for data retrieval.
3. Included Fields: Included fields, also known as non-key columns, are additional fields that are stored in the index leaf nodes for efficiency but are not part of the index key. They are included in the index structure to cover more query scenarios without increasing the size of the index or affecting the index’s ordering.
4. Performance Optimization: By specifying IncludedFields in a non-clustered index, you can improve query performance for queries that require additional fields beyond the key columns. Including these fields in the index structure allows the database engine to retrieve the necessary data directly from the index without having to access the underlying table, thereby reducing the need for key lookups or table scans.
5. Considerations: When deciding which fields to include as IncludedFields, consider the access patterns of your queries and include fields that are frequently used in SELECT, JOIN, or WHERE clauses but are not part of the index key. However, be mindful of the trade-offs, as including too many fields as IncludedFields can increase the size of the index and impact performance during data modification operations.

In summary, the IncludedFields property allows you to include additional fields in the index structure to optimize query performance without adding them as key columns, thus striking a balance between query optimization and index size.

Practical example when to use IncludedFields and when to not use IncludedFields

Let’s consider practical scenarios for when to use the “IncludedFields” property and when not to use it:

When to Use IncludedFields:

Scenario: Order Processing System

• Requirement: You have an order processing system in Microsoft Dynamics 365 Business Central that frequently queries orders based on OrderDate and CustomerID.
• Index Creation: You create a non-clustered index on the OrderDate column (as a key column) to support queries filtering by date. Additionally, you include the CustomerID column as an “IncludedField.”
• Reasoning: Including the CustomerID field as an IncludedField allows the index to cover queries that filter by both OrderDate and CustomerID without having to perform additional key lookups. This optimization improves query performance for queries that filter by both fields simultaneously.

When Not to Use IncludedFields:

Scenario: Employee Directory

• Requirement: You have an employee directory application in Microsoft Dynamics 365 Business Central that allows users to search for employees based on their Name and Department.
• Index Creation: You create a non-clustered index on the Name column (as a key column) to support queries filtering by employee names. However, you decide not to include the Department column as an IncludedField.
• Reasoning: In this scenario, including the Department column as an IncludedField might not be necessary because the queries typically filter by Name only. Including additional fields as IncludedFields unnecessarily increases the size of the index without providing significant benefits for the predominant query patterns. Therefore, it’s more efficient to keep the index lean and focused on the key column (Name) for optimal performance.

In summary, you should use the IncludedFields property when additional fields are frequently used in queries but are not part of the index key, optimizing query performance without increasing the size of the index unnecessarily. However, avoid including fields as IncludedFields when they are not essential for the predominant query patterns, as this can lead to increased index size and potential performance overhead.

Explanation of  “predominant query patterns” and a query example of a “for the predominant query pattern”

“Predominant query patterns” refer to the most common types of queries executed against a database table. These are the queries that occur frequently and are typically optimized for performance. When designing indexes, it’s essential to consider these predominant query patterns to ensure that the indexes effectively support them.

Let’s consider another scenario:

Scenario: Product Inventory Management

• Requirement: You have a product inventory management system in Microsoft Dynamics 365 Business Central where users frequently query products based on their Category and Availability.
• Predominant Query Pattern: The most common queries involve filtering products by Category, and less frequently, by Availability.

In this scenario, the predominant query pattern involves filtering products by Category. Therefore, you would create a non-clustered index on the Category column (as a key column) to optimize the performance of these queries. However, you might choose not to include the Availability column as an IncludedField in the index because queries filtering by Availability occur less frequently.

Here’s an example of index creation for the predominant query pattern:

CREATE NONCLUSTERED INDEX ix_products_category
  ON products (category)

In this index, the Category column is the key column, which efficiently supports queries filtering by Category, the most common query pattern in the system.

This approach ensures that the index is tailored to the predominant query patterns, optimizing query performance for the most frequent types of queries while keeping the index lean and focused on the key columns relevant to those patterns

Explanation of an “non predominant query patterns” and a query example of a “non predominant query pattern”

Certainly! “Non-predominant query patterns” refer to less common types of queries executed against a database table. These are queries that occur less frequently compared to predominant query patterns but still need to be supported by indexes for optimal performance.

Let’s consider another scenario:

Scenario: Employee Time Tracking

• Requirement: You have an employee time tracking system in Microsoft Dynamics 365 Business Central where users frequently query employee time entries based on Date and EmployeeID. However, occasionally, users need to search for time entries based on ProjectID as well.
• Predominant Query Pattern: The most common queries involve filtering time entries by Date and EmployeeID.
• Non-Predominant Query Pattern: Less frequently, users may need to filter time entries by ProjectID in addition to Date and EmployeeID.

In this scenario, the predominant query pattern involves filtering time entries by Date and EmployeeID. Therefore, you would create a non-clustered index on the Date and EmployeeID columns to optimize the performance of these queries.

Here’s an example of index creation for the non-predominant query pattern:

CREATE NONCLUSTERED INDEX IX_TimeEntries_Date_EmployeeID_ProjectID
ON TimeEntries
(
    Date,
    EmployeeID
)
INCLUDE (ProjectID)

In this index, the Date and EmployeeID columns are the key columns, which efficiently support queries filtering by Date and EmployeeID, the most common query pattern in the system. Additionally, ProjectID is included as an IncludedField to support the less frequent queries filtering by ProjectID in addition to Date and EmployeeID.

This approach ensures that the index is tailored to both the predominant and non-predominant query patterns, optimizing query performance for all types of queries while keeping the index lean and focused on the key columns relevant to those patterns.

Summary

Here’s a summary of the document:

Main ideas:

• The IncludedFields property is a feature that allows adding additional fields to non-clustered indexes without making them key columns.
• Non-clustered indexes are secondary indexes that improve query performance by providing a quick lookup path for specific columns in a table. Key columns are the columns that define the index key and determine the order of data storage in the index.
• Including fields in non-clustered indexes allows the database engine to retrieve the necessary data directly from the index without having to access the underlying table, thereby reducing the need for key lookups or table scans.
• When choosing which fields to include in non-clustered indexes, one should consider the access patterns of the queries and include fields that are frequently used in SELECT, JOIN, or WHERE clauses but are not part of the index key. However, one should also avoid including too many fields as this can increase the size of the index and impact performance during data modification operations.
• An example of using the IncludedFields property for order processing queries is creating a non-clustered index on the OrderDate column as a key column and including the CustomerID column as an IncludedField. This way, the index can cover queries that filter by both OrderDate and CustomerID without having to perform additional key lookups.
• An example of not using the IncludedFields property for employee directory queries is creating a non-clustered index on the Name column as a key column and not including the Department column as an IncludedField. This is because the queries typically filter by Name only and including the Department column would unnecessarily increase the size of the index without providing significant benefits.
• The summary of the IncludedFields property is that it allows one to include additional fields in the index structure to optimize query performance without adding them as key columns, thus striking a balance between query optimization and index size.