create index in table with include columns

(0) Share

Report

Posted on by Daniele Ferraretto

Hi everybody, in your experience if possible to create in the AOT structure an index in a table like this SQL script:

CREATE NONCLUSTERED INDEX [<name of your index>]
ON [dbo].[REQTRANS] ([REFTYPE])
INCLUDE ([DATAAREAID],[PARTITION],[RECID]) -- this is the point of discussion
GO

the AX version is AX2012R3 no CU

thanks, Daniele.

*This post is locked for comments

I have the same question (0)

All responses (4)

Answers (1)

Sort by

Suggested answer

Martin Dráb 239,084 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Yes, AX 2012 supports included columns.

Was this reply helpful? Yes No
André Arnaud de Cal... 304,038 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi Daniele,

Like Martin said it is possible. Note that DataAreaId and Partition are part of the index anyway...

Was this reply helpful? Yes No
Daniele Ferraretto on at

Like (0)

Report
Copy link

Link copied!

thanks to all for the answer, my reply arrives late because I was busy for long time.

Unfortunately from AOT when I create a index I was able to insert fields like "recid" and setup the includecolumn to YES but where I insert field "dataareaid" and setup the includecolumns "yes" and save index, AX give me an error.

I understand that AX automatically add dataareaid and partition if I setup the table with savedatapercompany and savedatapartition but I want to understand if it's possible setup this column like include column in SQL index.

Thanks anyway for your answer.

Was this reply helpful? Yes No
Verified answer

André Arnaud de Cal... 304,038 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi Daniele,

That's why I made my comment. When these fields are added by AX automatically, they contain the data anyway. An included column or a real index field always contains data. However an included column is not sorted. So when there is an Index suggestion like you described above, you can't add the columns as included column as it would be duplicated data in the index.

When you just create the index like AX wants to, the suggestion to have included columns for it would disappear anyway in the missing index reports from SQL.

Was this reply helpful? Yes No