Having a string array field as an index, good or bad?

Question

My gut feel is that setting a string (with array elements) field as an index on a table will be bad for performance (where the bulk of the operations done on a table are inserts and updates - the table holds transactional data and its current size is approximately 20 mil records).

The string extends a type with 4 array elements, where all of them aren’t always populated. I need to justify why not to set this field as one of the indexes. I’ve tried searching for answers, reading Kimberley Tripps blog, going through best practises re indexes on MSDN (which only mentions indexes are best on numerics first, then string fields), etc. But none of these mention indexing the table on a field that is of an array type. What reasons can I give to justify not indexing on the string-array field. And if my gut feel is totally wrong and indexes work well on array fields, why so?

Answer 1

A Memo or Container field cannot be part of an index in AX.

Furthermore, columns consisting of the ntext, text, or image data types cannot be specified as columns for an index in SQL Server.

Answer 2

Let's say you have an extended data type ArrElement with 3 additional array elements ArrElement2, ArrElement3, ArrElement4. Creating an index with a field of the ArrElement type in AX will effectively create an index with 4 fields (ArrElement, ArrElement2, ArrElement3, and ArrElement4 - in that order) in SQL Server. You cannot change the order of the array elements in the index, but in my opinion there's really nothing wrong in having such an index if it really serves your purpose. Hope that answers your question.

Answer 3

As @10p noted adding say Dimension as the only field, will create an index of all the array elements: Dimension, Dimension2_, Dimension3_ (which are the names of the SQL table fields).

The value of such an index will depend on the queries performed. If only Dimension[3] is queried, then the index is of no value because Dimension[1] and Dimension[2] is not known.

This could be solved by creating an index for each of the array elements, for example:

• Dim1Idx: Dimension[1] (maybe append more fields)
• Dim2Idx: Dimension[2] (maybe append more fields)
• Dim3Idx: Dimension[3] (maybe append more fields)

Individual array elements can be selected by using the combo-box on the index field.

The value of such indexes should be weighted against the added cost of insertion (and update, if the array values are changed).