Create RecID index on tables with Created/Modified DateTime fields

  • Article

In Dynamics AX 2009, if you use Microsoft SQL Server 2005 as the database, you should create RecID index on a table if the table has CreatedDateTime field and/or ModifiedDateTime field.

When you Insert into a table with CreatedDateTime field on SQL 2005, the following SQL statement will be issued immediately following the Insert:

    SELECT CreatedDateTime From <table> WHERE RecID=<recid> AND DataAreaId=<dataareaid>

When you Update a table with ModifiedDateTime field on SQL 2005, the following SQL statement will be issued immediately  following the Update:

    SELECT ModifiedDateTime From <table> WHERE RecID=<recid> AND DataAreaId=<dataareaid>

If you don't have RecID index on the table, the SELECT will result in a table scan. If the table being inserted into or updated is big, a seemingly innocent Insert or Update operation can take a long time to return and put unnecesary load on the SQL server.

If your backend database is Microsoft SQL Server 2008 or Oracle, there's no issue since the second SELECT statement will not be issued. The kernel will use an OUTPUT clause on the INSERT/UPDATE statement to retrieve the CreatedDateTime/ModifiedDateTime values.