Mysterious 101090 in T-SQL statement

Sometimes Transact-SQL (T-SQL) statement sent form AOS to SQL Server contains mysterious 101090 in a field list, even though select statement in X++ does not contain it. Also curious why?

Problem description
Find out what is 101090 in T-SQL statement.

Steps to reproduce
Run job like:

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

Hints
Try to select different system fields.


Solution
1. Original select statement in X++:

select generateonly description from inventBatch;

is converted into T-SQL statement:

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

Only description field is selected in X++, however additional fields are selected in T-SQL. InventBatchId and ItemId are part of primary itemBatchIdx index, Partition is added by AOS for data isolation (more details in msdn).
101090 is a mysterious value.
Note: if generateonly keyword is removed from select statement in X++ and job is run, then RecId field in table variable gets value 101090.

2. Let's select RecId instead of description field:

select generateonly RecId from inventBatch;

The following T-SQL statement is generated:

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

101090 disappeared. RecId is expected to form an unique index. As RecId index is not enabled on InventBatch table, fields from primary itemBatchIdx index are selected in T-SQL.

3. Let's select TableId instead of description field:

select generateonly TableId from inventBatch;

in T-SQL:

SELECT 101090 FROM INVENTBATCH T1 WHERE ((PARTITION=?) AND (DATAAREAID=?))

As TableId is not a table field in database, such select returns fixed constant value. Without generateonly keyword RecId field in table variable gets value 101090.

4. Let's select a field from a table with RecId index enabled, for example InventItemGroup in X++:

select generateonly Name from inventItemGroup;

in T-SQL:

SELECT T1.NAME, T1.RECID FROM INVENTITEMGROUP T1 WHERE ((PARTITION=?) AND (DATAAREAID=?))

101090 is not in T-SQL statement.

5. Let's select TableId from InventItemGroup table:

select generateonly TableId from inventItemGroup;

in T-SQL:

SELECT 101090 FROM INVENTITEMGROUP T1 WHERE ((PARTITION=?) AND (DATAAREAID=?))

Conclusion
101090 is used by AOS as fixed constant value for RecId when:
1. table has no RecId index enabled and RecId is not in a field list of a select statement
or
2. no field is actually fetched from a table in SQL Server.