Create Non clusterindex in Ax with include keyword.

Create non clustered index with include keyword suggestion given by query plan

 Index with Included Columns
You can extend the functionality of nonclustered indexes by adding nonkey columns to the leaf level of the nonclustered index. By including nonkey columns, you can create nonclustered indexes that cover more queries. This is because the nonkey columns have the following benefits:
•    They can be data types not allowed as index key columns.
•    They are not considered by the Database Engine when calculating the number of index key columns or index key size.
An index with included nonkey columns can significantly improve query performance when all columns in the query are included in the index either as key or nonkey columns. Performance gains are achieved because the query optimizer can locate all the column values within the index; table or clustered index data is not accessed resulting in fewer disk I/O operations.


Issue

Whenever a synchronization is done, AX overwrites the indexes which we put through SQL

Resolution

a. Created the index via AX as a table method


public static server void createSqlIndexes()
{

SqlStatementExecutePermission SqlStatementExecutePermission;
str createIndexSql;

Connection connection=new Connection(); Statement statement=connection.createStatement();
createIndexSql=
@"IF EXISTS(select * from sys.indexes where object_id=OBJECT_ID(N'[dbo].[InventDIM]')

AND NAME=N'sonIdx' )
DROP INDEX [sonIdx] on [dbo].[InventDIM] WITH (ONLINE=OFF)
CREATE NONCLUSTERED INDEX [sonIdx] ON [dbo].[INVENTDIM] ([INVENTBATCHID],[WMSLOCATIONID],[INVENTSERIALID],[INVENTSIZEID],[INVENTCOLORID],[INVENTGTDID_RU],[INVENTPROFILEID_RU],[INVENTOWNERID_RU],[INVENTSTYLEID],[LICENSEPLATEID],[INVENTSTATUSID],[DATAAREAID],[PARTITION],[INVENTLOCATIONID],[CONFIGID],[INVENTSITEID])
INCLUDE ([INVENTDIMID])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)";


SqlStatementExecutePermission=new SqlStatementExecutePermission(createIndexSql);
SqlStatementExecutePermission.assert();
statement.executeUpdate(createIndexSql);
CodeAccessPermission::revertAssert();
}

Execute this code in the application class dbsynchronize method at the end of the method after standard dbsynch done. so that we will have an sql index.