New resources available on Microsoft Learn
Did you know that Microsoft Learn offers free training modules to assist you on your path to mastering Dynamics 365 for Finance and Operations? Become an expert at your own pace or share with your team to foster growth.
Dynamics 365 2019 release wave 2 plan Discover the latest updates to Dynamics 365.Release Plan | Weekly Deployment Notes
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 and Operations TechTalks | Customer Engagement TechTalks | Talent 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