Create indexes without DATAAREAID in Dynamics 2009

The problem

As I had an issue with the performance I wanted to create indexes without DATAREAID in Dynamics AX 2009.

The indexes didn’t have the DATAAREAID column which is automatically added to the index as the first column on creation.

Because the newly created indexes shouldn’t have this column in the first position I had a problem.
The second problem is that every time an update or synchronization takes place in Dynamics AX 2009 the database objects which are not part of the software architecture are removed.

The solutions

I came up with two solutions:

1. Manually place the DATAAREAID column at the end of the index
2. Create a job that checks if the indexes still exist on the database in the database

I chose option 2 because of several reasons:

1. I don’t know the impact of the index change ( the index already existed but with the DATAAREAID column)
2. I want to be sure the queries benefit the new index for sure
3. AX 2009 doesn’t support included columns

The last reason was the decisive reason why the indexes weren’t created in AX 2009.

The indexes I wanted to create used included columns which isn’t supported in AX 2009. I created a job in SQL Server with the following code:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

USE [DATABASE NAME]

IF NOT EXISTS (
  SELECT name
  FROM sysindexes
  WHERE name = '<INDEX NAME>'
)
BEGIN
  <INDEX CODE>
END
GO

They looks all very simple and it is! In the new version, AX 2012, included columns are supported so maybe I have to adjust the solution until then.