Change which index is clustered – directly on the SQL server

Todays post is not exactly Dynamics AX relevant. However, in some cases where you need to do some immediate testing og hot fixing and you are not able to change it on a running AX environment you could end up making temporary index modifications directly on the SQL server.

The thing about this is that when you later on synchronise from Dynamics AX changes often are overwritten in that process. That can easily be ok as long as you are aware of it.

One of the things that needs a bit more work than just adding/removing indexes or fields is to change which index is clustered. For demonstration purposes I have created a small database called DemoDB with a table called PersonTbl.

It contains 3 fields and 2 indexes as shown here:

TableWithWrongClusteredIndex

The thing is that we in this case for some reason need to have the IdIdx as the clustered index and it is not an option to wait for the change to be released through the normal procedure in AX.

If we look at the clustered index NameIdx we cannot un-cluster it:

OldClusteredIndex

And clustering the IdIdx index is not possible from the properties form either:

NewClusteredIndex

To make this happen we need to modify the table through design mode. Right click on the table and select Design:

DesignTable

Right click anywhere on the columns overview and select Indexes/Keys:

SelectIndexesKeys

Now we can uncluster the NameIdx table like this:

UnclusterIndex

And make IdIdx clustered:

ClusterIndex

Click Close and save the table modifications.

Voila! The indexes have been changed. You might need to refresh to make it visible and should anyone be using the table and indexes while you make your customisations the saving part would fail.

TableWithCorrectClusteredIndex

 

Please remember that this procedure is not exactly best practice and should only be used as a last exit.

Advertisements