creating recId indexes on SQL 2005

hi,

The Dynamics Ax Performance Team Blog gives a pretty good description why you want to add a recID-index for tables having created/modifiedDateTime property activated (on a SQL 2005 DB backend).

But you don't want to do this manually, do you?

Thought so. You can find the script I used to add these indexes here.

In short, here's what the script does:

- on each table in the AOT it checks whether the created/modifiedDateTime fields are active and the createRecIdIndex property is set not set

- for tables that meet these conditions: it creates a brand new index on recId

When a new index is created I'm also adding the modified/createdDateTime field. The Performance team blog states

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%

The same goes for the modifiedDateTime field with an update statement.

So my reasoning is to include the created/modifiedDateTime field right away in the newly created index.

This should enable SQL to fetch all required data using just this single index.

More and wider indexes slow down insert/update/delete operations: true ... but if this can speed up each and every insert/update, this may be a well-considered decision.