Included columns is a feature of SQL Server (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:

• can be added to a unique index without influencing uniqueness
• support even (some) types that can’t be used in normal indices
• don’t count to the limit of number of index columns  (16)
• don’t count to the limit of index size (900 bytes)

The following (logical) restriction apply:

• are always at the end of index
• the index must contain at least one “normal” (key) column
• can’t be used in clustered indices

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. For example, you can create an index with a unique field Id and attach a field Name that you often ask for. Although such an index can still be assigned as primary (it couldn’t be, if the second field was not an included column), but database synchronization fails. So – unique indices works, primary ones don’t.

Although AX allows you to switch IncludedColumn property on any field, any invalid configuration is naturally refused by SQL Server during synchronization.

Reflection

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:

• int numberOfIncludedColumns()
• FieldId includedColumns(int _inclColumnIdx)

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.