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)

Suggested answer

Martin Dráb 237,801 Most Valuable Professional on at

Like (0)

Report

Yes, AX 2012 supports included columns.

Was this reply helpful? Yes No
André Arnaud de Cal... 300,911 Super User 2025 Season 2 on at

Like (0)

Report

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

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... 300,911 Super User 2025 Season 2 on at

Like (0)

Report

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