The views and opinions expressed in this blog are those solely of the author(s) and do not necessarily reflect Microsoft’s current policy, position, or branding. For official announcements and guidance on Dynamics 365 apps and services, please visit the Microsoft Dynamics 365 Blog.
Now Available in Community - MBAS 2019 Presentation Videos
Catch the most popular sessions on demand and learn how Dynamics 365, Power BI, Power Apps, Power Automate, and Excel are powering major transformations around the globe. | View Gallery
2019 release wave 2 Discover the latest updates and new features to Dynamics 365 planned through March 2020
Release overview guides and videos Release Plan | Preview 2020 Release Wave 1 Timeline
Ace your Dynamics 365 deployment with packaged services delivered by expert consultants. | Explore service offerings
Connect with the ISV success team on the latest roadmap, developer tool for AppSource certification, and ISV community engagements | ISV self-service portal
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Program | Finance TechTalks | Customer Engagement TechTalks | Upcoming TechTalks
Included columns is a feature of SQL Serveru (since version 2005) related to indices.Thanks to it you can attach additional fields to an index that are not used for searching (so they don’t have to be maintained so expensively) but that can be used by database server to return data. If all columns being returned by a query are included in an index, database server can directly return data and it doesn’t have to touch the table itself (and that saves time, of course).
Included columns also have additional advantages:
The following (logical) restriction apply:
Of course, even included columns have impact to index size so they can slow donw operations with the index and consume disk space.
Support in Dynamics AX 2012
Creating an included column in AX2012 is very simple. Add a field to an index in the same way as usual and then change IncludedColumns property of the index column to Yes.
The image shows a concrete example in AX2012 – index TransIdIx on table CustInvoiceTrans. It has three “normal” fields useful for searching and two additional fields (Qty a QtyPhysical) to cover some queries for these fields.
In the similar way you can extend even unique indices, including primary keys. For example, you can create an index with a unique field Id and attach a field Name that you often ask for. Such an index can be still assigned as primary (it couldn’t be, if the second field was not an included column), nevertheless you can’t use it as ClusterIndex.
Although AX allows you to switch IncludedColumn property on any field, any invalid configuration is naturally refused by SQL Server during synchronization.
You may also benefit from support for included columns for reflection. You might want, for example, to list details of all indices in your module. You can use the following methods in DictIndex class:
I’ve found a problem here with the automatically generated RecId index. It’s also visible in the following image – AX returns correct number of included columns for other indices (1 and 0), but 35888 included columns in RecId index is palpable nonsense. Moreover every run returns different value – it looks like if AX reads a wrong memory address – but who knows?
Included columns are surely worth using – without them, you either have to do without covering some queries, or to make full-blown (and expensively maintained) indices. Sometimes included columns can help you to even reduce the number of needed indices.
But is doesn’t mean that you can begin to mindlessly add columns to indices – maintenance of included columns has its costs too.
Business Applications communities