Understanding Custom Index Behavior
Audience: Administrators Developers Skill Prerequisites: Basic familiarity with Entities and SQL Server Applies To: DNN installations running Plant an App Purpose: Explain how custom indexes behave when Entity fields are modified and how to safely remove fields that participate in custom indexes.
Overview
Indexes play an important role in the performance and behavior of Entities. They allow the database to quickly locate records when filtering, sorting, or joining data, which is especially important for applications with large datasets.
Plant an App supports both automatically generated indexes and custom indexes defined by developers or administrators. These indexes help optimize queries used by listings, searches, filters, and workflows.
In most cases, indexes operate transparently and do not interfere with normal entity operations. However, because indexes are implemented at the SQL Server database level, certain schema changes—such as deleting fields—can be affected by index dependencies.
This article explains how indexes behave when entity fields are renamed, edited, added, or deleted, and highlights the one scenario where additional steps may be required: removing a field that is part of a custom index.
What Are Custom Indexes
A custom index is a database structure that improves the speed of queries that search, sort, or join data within an Entity. Instead of scanning every row in a table, SQL Server can use the index to quickly locate the relevant records.
Plant an App allows administrators and developers to define custom indexes on Entity fields when additional performance optimization is needed.
Custom indexes can be created in two primary forms.
Single-field indexes
- The index is created on one field.
- Useful when queries frequently reference that single column.
- Example use cases include queries using Status, UserId, or CreatedDate.
Multi-field indexes
- The index includes multiple fields as part of the same index key.
- Useful when queries commonly reference a combination of columns.
- Example use cases include queries involving State and County, or AccountId and CreatedDate.
Indexes do not change the data stored in an Entity. Instead, they maintain a separate internal structure that helps SQL Server locate rows more efficiently.
Creating Custom Indexes
Custom indexes can be created to improve the performance of queries that frequently reference specific Entity fields.
Indexes are commonly added when:
- Queries frequently reference a field in a WHERE clause
- Results are frequently ordered using an ORDER BY clause
- Queries frequently join tables using a specific column
- Multiple columns are commonly used together in query conditions
Custom indexes can be defined as either single-field indexes or multi-field indexes, depending on the query patterns you want to optimize.
Example – Creating a single-field index
CREATE INDEX IX_Entity_FieldName
ON app.EntityTable (FieldName);
Example – Creating a multi-field index
CREATE INDEX IX_Entity_State_County
ON app.EntityTable (State, County);
Indexes can be created by executing a SQL statement in the SQL Console within Plant an App or by using SQL Server Management Studio (SSMS).
When designing indexes, focus on fields that are frequently referenced in WHERE clauses, ORDER BY clauses, or JOIN conditions, as these operations benefit most from indexing.
Indexes Created Automatically
In addition to custom indexes, Plant an App automatically creates indexes in certain situations to improve performance.
Most commonly, an index is created when a field is marked as Filterable or Sortable. These settings indicate that the field may frequently be used in queries generated by listings, searches, or other data operations.
When one of these options is enabled, the platform typically creates a single-field index on the corresponding column in the underlying SQL table. This allows SQL Server to efficiently locate or order records without scanning the entire dataset.
Automatic indexes are commonly used by:
- Listings that allow users to narrow results using field values
- Sorting operations performed by the user interface
- Queries generated by workflows or APIs
These automatically generated indexes are managed by the platform and typically do not create schema modification limitations when fields are removed.
****## Behavior When Modifying Entity Fields
Indexes generally work transparently and do not interfere with most Entity operations. In many cases, Plant an App and SQL Server automatically maintain index integrity when changes are made to fields.
The following summarizes how indexes behave during common Entity operations.
Renaming a field
When a field is renamed in the Entity Builder, any index that references that field is automatically updated to reflect the new column name.
Editing record data
Updating or editing the data stored in an indexed field does not produce errors. SQL Server automatically updates the index as records change.
Marking a field as Sortable or Filterable
When a field is marked Sortable or Filterable, the platform typically creates a new index for that field to support efficient queries.
Removing Sortable or Filterable settings
Removing the Sortable or Filterable designation from a field does not produce errors and does not interfere with the Entity structure.
In general, these operations are handled safely and require no manual intervention.
Important Limitation: Deleting Fields Used in Custom Indexes
One operation that requires special attention is deleting a field that is part of a custom index.
If a field is referenced by a custom index, SQL Server will prevent that field from being deleted. This occurs because the index depends on the column to function.
When this happens, the deletion attempt fails and an error similar to the following may appear:
“The index is dependent on column — ALTER TABLE DROP COLUMN failed.”
When this error occurs:
- The field is not deleted
- The data remains unchanged
- The index remains intact
It is important to note that indexes created automatically by Plant an App (such as those generated when a field is marked Filterable or Sortable) are handled by the platform and do not cause this limitation.
Recommended Workflow When Removing Indexed Fields
If you need to delete a field that is part of a custom index, the index dependency must be removed before the field can be deleted.
The recommended workflow is:
1. Identify indexes that reference the field
Review the Entity’s custom indexes and determine whether the field is part of any index definition.
2. Update or remove the index
Depending on the index structure, you have two options.
- Single-field index — Delete the index entirely.
- Multi-field index — Remove the field from the index definition, or delete the index if it is no longer needed.
Indexes can be modified or removed by executing a SQL statement in the SQL Console or by using SQL Server Management Studio (SSMS).
3. Delete the field
Once the index dependency has been removed, the field can be deleted normally through the Entity Builder.
Following this sequence ensures that SQL Server can safely apply the schema change without encountering index dependency errors.
What Operations Are Safe
Most common Entity operations work normally when indexes are present and do not require special handling.
The following operations are generally safe.
Renaming indexed fields
When a field that participates in a custom index is renamed in the Entity Builder, the underlying index definition is automatically updated to reference the new column name.
Editing data in indexed fields
Updating records in indexed fields works normally. SQL Server automatically maintains the index as data changes.
Adding Sortable or Filterable settings
When a field is marked Sortable or Filterable, Plant an App typically creates a supporting index automatically.
Removing Sortable or Filterable settings
Removing the Sortable or Filterable designation from a field does not cause errors and does not interfere with the Entity structure.
Best Practices
While indexes typically operate transparently, following a few best practices can help avoid issues when modifying Entity structures.
Review indexes before making schema changes
Before deleting or restructuring fields, review any custom indexes that reference those fields.
Plan index updates when modifying entities
If you anticipate removing or significantly changing fields that participate in custom indexes, plan to update or remove those indexes as part of the change.
Use multi-field indexes thoughtfully
Multi-field indexes can improve performance for queries that commonly reference multiple fields, but they also introduce additional schema dependencies.
Avoid unnecessary indexes
Indexes improve query performance but also add maintenance overhead. Only create custom indexes when they provide a clear benefit.
Key Takeaways
- Custom indexes improve query performance by allowing SQL Server to quickly locate records used in filtering, sorting, and joins.
- Fields marked Sortable or Filterable often receive indexes automatically, and these indexes are managed by the platform.
- Most Entity operations work normally with indexes, including renaming fields and editing data.
- Deleting a field that participates in a custom index is blocked by SQL Server to protect database integrity.
- To delete such a field, the custom index must first be updated or removed, which can be done through the SQL Console or SQL Server Management Studio (SSMS).
Revision Date 2026-03-17