How unique index influences T-SQL statement

As it was mentioned in the previous post, T-SQL statement can contain additional fields which are not selected in X++ statement. Let's analyse on InventBatch table what exactly is added and why.

Problem description
Analyse how an unique index influences T-SQL statement.

Steps to reproduce
Run a test job:

static void Test_UniqueIndex_SQL(Args _args) { InventBatch inventBatch; select generateonly description from inventBatch; info (inventBatch.getSQLStatement()); }

Hints
Change index properties on the table and re-run test job.

Solution
1. CreateRecIdIndex property of InventBatch table is set to No, itemBatchIdx index is unique and consists of itemId and inventBatchId fields.
Select statement in T-SQL is:

SELECT T1.INVENTBATCHID, T1.ITEMID, T1.PARTITION, 101090, T1.DESCRIPTION FROM INVENTBATCH T1 WHERE ((PARTITION=?) AND (DATAAREAID=?))

Note: itemBatchIdx index in SQL database consists of PARTITION, DATAAREAID, ITEMID and INVENTBATCHID fields. It is not clear why DataAreaId field is not selected in T-SQL.

Conclusion1: if fields of an unique index are not selected in X++ statement, they are added into a field list of T-SQL statement.

2. Let's make itemBatchIdx index non-unique by setting index's AllowDuplicates property to Yes. After synchronisation batchIdx index is changed in SQL database only - RecId field is added and index is made unique. The reason is described in msdn.
T-SQL statement is:

SELECT T1.INVENTBATCHID, T1.PARTITION, T1.RECID, T1.DESCRIPTION FROM INVENTBATCH T1 WHERE ((PARTITION=?) AND (DATAAREAID=?))

Conclusion2: if a table has no unique index then an index with shortest definition is made unique by adding RecId field in SQL database, fields of such index are added into a field list of T-SQL statement.

3. Let's disable all table indexes in AX by setting Enabled property to No. After synchronisation unique index on RecId field is created in SQL database only. The reason is the same:
"If a table is not given a unique index (because there is no key in the application), the system will create a unique index to get a key".
T-SQL statement is:

SELECT T1.RECID, T1.DESCRIPTION FROM INVENTBATCH T1 WHERE ((PARTITION=?) AND (DATAAREAID=?))

Conclusion3: if a table has no index then an unique index on RecId field is created in SQL database and RecId field is added into a field list of T-SQL statement.

4. Let's restore standard AX properties and make batchIdx index unique by setting AllowDuplicates property on index to No.
T-SQL statement is:

SELECT T1.INVENTBATCHID, T1.PARTITION, 101090, T1.DESCRIPTION FROM INVENTBATCH T1 WHERE ((PARTITION=?) AND (DATAAREAID=?))

Conclusion4: if there are several unique indexes then fields from an unique index with the shortest definition are added into field list of T-SQL statement.

5. Let's create RecId index by setting CreateRecIdIndex property to Yes.
T-SQL statement is:

SELECT T1.RECID, T1.DESCRIPTION FROM INVENTBATCH T1 WHERE ((PARTITION=?) AND (DATAAREAID=?))

Note: same T-SQL statement is generated regardless of other unique indexes on a table, because index on RecId usually has the shortest definition.

Conclusion5: if RecId index is enabled then RecId field is added into a field list of T-SQL statement.