How to create a new index that supports the "Include" option on an X3 table?

1 minute read time.

It's always the way isn't it?  You don't see any articles about creating indexes then several come all at once!   Chris Hann recently wrote the article "How to use the Database Optimization function" but what if you want to create a new index which uses the "include" option when building the index?

The good news is that this is supported within X3 and has been for many years.  Of course it's not well known how to achieve this objective, as the documentation is not all that good in this area.   Even better news is that the online help is in the process of being updated to give more details about creating indexes with the "Include" option, although it may not be available for a little while...

If you can't wait, then here are some details for you:

Firstly I need to say that with ANY custom index you are considering to add, do not add it before considering it's full impact and also performing adequate testing on a TEST system with representative data, testing ALL your critical business functions.   For example, you may have found a great index to add to speed up a report which is using lots of SELECTs, but how does this impact your month end processing where the same table may have lots of INSERTs and UPDATEs!

Once you are ready to create that new index, creating custom indexes with "advanced" options can be implemented in the X3 front end (and preserved over upgrades) by using a "configuration file" in Development, Data and Parameters, Tables, Tables   The "Tables" online help then leads you to configuration file specific help page.   Sadly this help page does not give many examples relating to indexes.  Luckily, although very old, there is already a blog article which describes setting up an index with the include option at "Supporting Advanced Index Hints From SQL Server Within Sage ERP X3" The section "How to Create a New Index That Supports the Include Option on an X3 Table" describes the process in good detail and is probably all you need to know!

Keep an eye out for that updated online help, but in the meantime I hope the above gives you enough information to be getting on with it.