Problem Statement

Business Central currently automatically includes all primary key fields in every secondary index, a design inherited from Navision. While this made sense in the original Navision database engine, it creates significant problems on modern SQL Server implementations:

Storage Impact

• Excessive disk space consumption: Secondary indexes can be 2-5x larger than necessary
• Increased cloud storage costs: Particularly problematic in BC SaaS where storage is a billable metric
• Memory pressure: Larger indexes consume more buffer pool memory, reducing cache effectiveness
• Backup size inflation: Impacts backup windows, storage costs, and recovery time objectives

Performance Impact

• Slower index maintenance: Larger indexes take longer to rebuild and reorganize
• Increased I/O overhead: More disk reads required for index scans and seeks
• Poor query optimizer decisions: SQL Server may avoid using beneficial indexes due to their excessive width
• Page splits and fragmentation: Wider indexes fragment faster, requiring more frequent maintenance
• Reduced concurrent users: Higher memory and I/O consumption limits scalability

Real-World Example

Consider a table with a composite primary key of 3 fields (20 bytes total). A secondary index on a single 4-byte field becomes 24 bytes wide instead of 4 bytes - a 6x increase. With millions of rows, this translates to gigabytes of wasted space per index.

Proposed Solution

Implement intelligent index design that follows SQL Server best practices:

1. Remove automatic primary key inclusion from secondary indexes
2. SQL Server already maintains the clustered index key in non-clustered indexes when needed for lookups - this is built into the engine
3. Use INCLUDE columns when additional columns are needed for covering indexes
4. Preserve uniqueness where semantically required by the AL key definition

Technical Implementation

• Secondary indexes should contain only the explicitly defined key fields
• SQL Server will automatically add the clustered key pointer (row locator) as needed
• For unique secondary keys, maintain uniqueness constraint without redundant key fields
• Allow developers to optionally specify INCLUDE columns in AL for covering indexes

Benefits

For Microsoft/Partners

• Reduced cloud infrastructure costs (storage, I/O, backup)
• Improved scalability and performance benchmarks
• Alignment with SQL Server best practices
• Competitive advantage versus other ERP platforms

For Customers

• Lower total cost of ownership (reduced storage/backup costs)
• Faster queries and improved user experience
• Better scalability as data volumes grow
• Reduced maintenance windows for index optimization

Quantifiable Improvements (Estimated)

• 30-60% reduction in secondary index storage requirements
• 20-40% improvement in index maintenance operations
• 10-25% reduction in query execution time for index-heavy workloads
• Significant reduction in monthly cloud storage costs for large databases

Backward Compatibility Considerations

This change could be implemented with minimal disruption:

1. Phased approach: Apply to new indexes first, migrate existing indexes over time
2. Compatibility mode: Provide a flag to maintain legacy behavior if needed
3. Automatic migration: Provide tools to analyze and rebuild indexes with new structure
4. Testing period: Enable for preview/sandbox environments before production

Precedents

This is standard practice in modern database platforms:

• SQL Server native applications follow this pattern
• Other ERP systems (SAP, Oracle ERP) use optimal index structures
• Microsoft's own guidance recommends against redundant index key columns

Conclusion

This architectural debt from the Navision era creates unnecessary costs and performance limitations in modern Business Central implementations. As BC continues to evolve as a cloud-native platform, aligning with SQL Server best practices is essential for competitiveness, scalability, and customer satisfaction.

This change would represent a significant quality-of-life improvement for partners and customers while reducing Microsoft's cloud infrastructure costs.