Category Archives: Database

D365/AX7:Data Entity Staging Tables Clean Using Direct SQL(Fastest Way)

Requirement & Issue

Data Entity Staging Tables clean up is one of the most important maintenance activities.The amount of data stored in a database has a great impact on its performance.Huge and unnecessary data can lead to slowness & decrease performance of the D365 Finance and operation.

There is no standard functionality & fast way to clean unnecessary data & records from Data Entity Staging Tables Using Direct SQL.

Sample Program

Below program is fastest way to truncate all the Data Entity Staging Tables in one go using direct SQL.

**** you can modify the below program as per your need***

[code language=”cpp”]
class ACXDataStagingCleanUpTableClass
public void operation()
DMFEntity DMFEntity;
RecId oldRecId;
while select * from DMFEntity
order by RecId asc
where DMFEntity.RecId > oldRecId
oldRecId = DMFEntity.RecId;
if (DMFEntity.EntityTable like ‘*Staging’)
public void cleanUp(int _tableName)


Connection connection = new Connection();
Statement statement;
int resultSet;
int rowCount;
str sql = strFmt(‘TRUNCATE TABLE %1;’, new DictTable(_tableName).name(DbBackend::Sql));

statement = connection.createStatement();
new SqlStatementExecutePermission(sql).assert();
resultSet = statement.executeUpdate(sql);




D365/AX7/SQL:ColumnStore VS Index Simple-Talk

Purpose & Explanation

In older version of Microsoft Dynamics AX , there is no option to use concept of ‘Column Store’.But in Microsoft Dynamics 365 Finance & Operations you can set the “IndexType” property of a index in a table.There are 2 values available – 1) Index 2)ColumnStore.

A columnstore index stores data in a column-wise or in columnar format where as index stores data in a row -wise format.

Columnstore index is to reduce I/O, which can have a direct impact on query performance as comparison to index.

Columnstore is designed to speed up data-warehouse queries & read-Only operations.

Columnstore is better for OLAP whereas index is better for OLTP.