✏️ Explanatory Question

What is the difference between set-based and row-based database operations in Dynamics 365 Finance & Operations? Which X++ statements are set-based, in what situations do they silently fall back to row-by-row processing, and how do the skip methods help?

👁 4 Views
📘 Detailed Answer
🟢 Easy
No previous question
No next question
💡

Answer with Explanation

D365 F&O • X++ INTERVIEW

Set-based vs. Row-based Operations in Dynamics 365 Finance & Operations

Question 5 — Bulk DML operators, when they fall back to row-by-row, and the skip methods that keep them fast.

Interview Question

What is the difference between set-based and row-based database operations in Dynamics 365 Finance & Operations? Which X++ statements are set-based, in what situations do they silently fall back to row-by-row processing, and how do the skip methods help?

Model Answer (Short)

Row-based operations process one record per round trip between the AOS and SQL Server — a while select loop calling insert(), update() or delete() on each record. Set-based operations push the whole operation to SQL Server in a single statement, dramatically reducing round trips. The three set-based operators are insert_recordset, update_recordset and delete_from, plus the RecordInsertList / RecordSortedList classes for batched inserts. The catch: these operators automatically fall back to row-by-row when the table has overridden insert/update/delete methods, database logging, alerts, change tracking, or valid-time-state — unless you explicitly call the relevant skip methods.

Detailed Explanation

The set-based operators

  • insert_recordset — copies data from one or more source tables into a target table in one server trip.
  • update_recordset — updates many records that match a where clause in a single trip.
  • delete_from — deletes many records matching a where clause in a single trip.
  • RecordInsertList — batches multiple insert() calls into fewer round trips (useful when the insert method is overridden and must still be called).

When set-based operations fall back to row-based

The kernel silently switches to row-by-row processing when any of the following apply to the table:

  • The insert(), update() or delete() method is overridden.
  • Database logging is enabled on the table.
  • Alerts (change-based) are configured.
  • Change tracking is enabled.
  • The table uses ValidTimeState (date-effective) framework.
  • For delete_from: the table has DeleteActions defined.

The skip methods — forcing true set-based execution

Call these on the table buffer before the set-based statement to prevent the fallback. Use them only when it is safe to bypass the corresponding logic:

  • skipDataMethods(true) — bypasses overridden insert() / update() / delete() methods.
  • skipDataMethods also skips the aosValidate* hooks tied to those methods.
  • skipDatabaseLog(true) — skips database logging for that operation.
  • skipEvents(true) — skips the table's data events (e.g., OnInserting, OnUpdated).
  • skipAosValidation() — skips AOS-side validations such as validateField / validateWrite.
GOLDEN RULE
Bulk data → set-based → call skip methods to avoid fallback

Prerequisites (Rule 5)

  • Visual Studio with the Dynamics 365 developer tools.
  • A custom model / package for your objects.
  • Awareness of the target table's properties (overridden methods, DB log, DeleteActions) to know if a fallback will occur.
  • A transaction scope (ttsbegin / ttscommit) around update/delete operations.

Code Example — update_recordset

Update many rows in one server trip instead of looping:

SalesLine salesLine;

ttsbegin;
update_recordset salesLine
    setting SalesStatus = SalesStatus::Delivered
    where salesLine.SalesId == 'SO-0001'
       && salesLine.SalesStatus == SalesStatus::Backorder;
ttscommit;

insert_recordset — copy from source to target

AbcTmpSummary  tmpSummary;   // target
SalesLine      salesLine;    // source

insert_recordset tmpSummary (SalesId, LineAmount)
    select SalesId, LineAmount
        from salesLine
        where salesLine.SalesStatus == SalesStatus::Invoiced;

delete_from

AbcStagingTable staging;

ttsbegin;
delete_from staging
    where staging.Processed == NoYes::Yes;
ttscommit;

Forcing set-based with skip methods

AbcStagingTable staging;

staging.skipDataMethods(true);     // ignore overridden update() method
staging.skipDatabaseLog(true);     // do not write to the DB log
staging.skipEvents(true);          // do not raise data events

ttsbegin;
update_recordset staging
    setting Processed = NoYes::Yes
    where staging.Processed == NoYes::No;
ttscommit;

RecordInsertList — batched inserts in a loop

AbcStagingTable  staging;
RecordInsertList insertList = new RecordInsertList(tableNum(AbcStagingTable));

ttsbegin;
for (int i = 1; i <= 500; i++)
{
    staging.clear();
    staging.LineNum  = i;
    staging.Processed = NoYes::No;
    insertList.add(staging);   // buffered, not yet sent
}
insertList.insertDatabase();   // one (or few) round trip(s)
ttscommit;

Set-based vs. Row-based

Aspect Set-based Row-based
Round trips to SQL Single statement One per record
Performance on bulk data High Degrades quickly with volume
X++ constructs insert_recordset, update_recordset, delete_from while select + insert/update/delete
Runs per-record business logic No (unless it falls back) Yes
Best when Large volumes, no per-row logic needed Complex per-row logic or validations required

Points the interviewer wants to hear

  • Set-based = one round trip; row-based = one trip per record.
  • The three operators are insert_recordset, update_recordset, delete_from.
  • Set-based silently falls back to row-by-row for overridden methods, DB log, alerts, change tracking, valid-time-state, or delete actions.
  • Use skipDataMethods, skipDatabaseLog, skipEvents, skipAosValidation to force true set-based execution — carefully.
  • Use RecordInsertList when insert() is overridden but you still want batching.

Likely Follow-up Questions

  • What is the risk of calling skipDataMethods(true) on a table with important validation logic?
  • How is RecordInsertList different from insert_recordset?
  • Why might an update_recordset you wrote still execute row by row in production?

Key Takeaway

Prefer set-based operations for bulk data to minimise AOS↔SQL round trips, but always know the conditions that trigger a row-by-row fallback. The skip methods restore true set-based speed — use them only when bypassing that per-row logic is genuinely safe.

D365 F&O Interview Series — Question 5 of many
No previous question
No next question