Table Keys and SQL Indexes in Business Central
AL table keys and SQL Server indexes are not the same thing — and understanding the difference is critical for writing performant Business Central code. In this stream, we used Azure Data Studio to look directly at the SQL query execution plans generated by BC, comparing clustered index scans against index seeks, and measuring the real I/O cost difference between having a key and not having one.
We covered SumIndexFields and how they create indexed SQL views (SIFT), IncludedFields for covering indexes and why you can never fully cover a query in AL due to the timestamp column, and what happens when SQL Server uses multiple single-column indexes together in a nested loops join. We also ran write-performance benchmarks that show how piling on keys can turn a 4.5-second bulk modify into a 20-second one.
The stream wraps up with Database.AlterKey, the on-premises-only AL method for temporarily disabling indexes during bulk operations, and a set of rules of thumb for deciding when a key is actually worth adding.
Streaming all kinds of coding for Microsoft Dynamics 365 Business Central.
Join me to just hang out or tell me what I am doing wrong ;)
Join me live and let's build this together.
📝 Blog post: https://stefanmaron.com/posts/table-k...
https://github.com/waldo1001/waldo.BC...
====== Content of this Video ======
0:00 - Introduction and setup
2:50 - Exploring AL table keys and SQL indexes
15:53 - Examining SQL query execution plans
25:51 - Analyzing SQL statements generated by AL code
34:22 - Demonstrating the impact of indexes on query performance
42:53 - Creating and testing complex queries with multiple filters
1:03:00 - Building and testing composite keys
1:24:49 - Measuring performance impact of modifying records
1:34:44 - Disabling and enabling keys for bulk operations
1:49:05 - Demonstrating the drawbacks of excessive indexing
1:53:44 - Summary and key takeaways
1:56:17 - Closing remarks and channel information
About Me:
https://stefanmaron.com/
Music by:
/ @streambeatsbyharrisheller
https://open.spotify.com/artist/6GTRL...