Select statement on field

A field select is a special select statement in X++. The description is available on msdn, but what is actually executed on SQL Server? Can the performance be improved? Let's analyse it.

Hints
Run field select statement on several tables and track actual T-SQL statements in SQL Server Profiler. You can also jump to the conclusion section.

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.

How joins in X++ select statement are translated into T-SQL

It is not always obvious what request is actually executed on SQL Server. The most confusing is probably exists join in X++. Let's analyze how joins in select statement in X++ are translated into T-SQL statement sent to SQL Server.