Tip Dynamics AX Batch Execution and Using the NOLOCK hint to read SQL Server uncommitted data

Dear All,

Hope you are doing well.

I thought to share here a quick handy tip which I have been using for quite a while now.

Scenario:

I have a batch job running in AX which loads thousands of rows into some AX tables based on a csv/txt file. Quite often, I would like to know how many rows have been inserted into the tables.
There are many ways to get this but a quick way without much development effort is to use SQL Server Query Hint NOLOCK

Explanation:

For e.g. I'm loading some data into smmActivites table via an AX Batch and I would like to know ant any point of time how many rows have been inserted via my batch.

So, I execute the below query via SQL Management Studio

select COUNT(*) from SMMACTIVITIES WITH (NOLOCK) where DATAAREAID = 'n000'


The above statement will show me the no. of rows inserted into the table

What is the role of NOLOCK?

NOLOCK is a SQL Table hint which reads uncommitted data from database - meaning

Specifies that dirty reads are allowed. No shared locks are issued to prevent other transactions from modifying data read by the current transaction, and exclusive locks set by other transactions do not block the current transaction from reading the locked data.

You can read more about NOLOCK hint from the MSDN Documentation here http://technet.microsoft.com/en-us/library/ms187373.aspx

Note - Support for use of the READUNCOMMITTED and NOLOCK hints in the FROM clause that apply to the target table of an UPDATE or DELETE statement will be removed in a future version of SQL Server. Avoid using these hints in this context in new development work, and plan to modify applications that currently use them.