What is the difference between a computed column and a virtual field on a view in Dynamics 365 Finance & Operations? How do you create a computed column using the SysComputedColumn class, and why is this better for performance than calculating in X++?

Question

Accepted Answer

D365 F&O • X++ INTERVIEW
     Computed & Virtual Columns in Views (Dynamics 365 Finance & Operations)
    Question 17 — Pushing calculations into SQL views for performance using SysComputedColumn.
  

  
   Interview Question
  
    What is the difference between a computed column and a virtual field on a view in
    Dynamics 365 Finance & Operations? How do you create a computed column using the
    SysComputedColumn class, and why is this better for performance than calculating in
    X++?
  

  
   Model Answer (Short)
  
    A computed column is a view column whose value is generated by a SQL expression
    that runs directly in the database — defined in X++ through a static method returning a string built with the
    SysComputedColumn helper class. A virtual field (an unbound/computed
    field) is not persisted and typically gets its value at runtime. The big advantage of computed columns is that the
    calculation is pushed down to SQL Server and executed as part of the set-based query, avoiding
    row-by-row processing in X++ and reducing AOS↔DB round trips — making them ideal for aggregations and derived
    values used in reports and forms.
  

  
   Detailed Explanation
  
     How computed columns work
    
      You add a computed column to a view and point its ViewMethod at a static method on the view.
      That method returns a SQL string which becomes part of the view definition when the view is synchronised.
      The SysComputedColumn class provides helpers to build safe, correct SQL (field references, literals, expressions, CASE logic).
      Because the logic lives in SQL, it runs set-based in the database, not row-by-row in X++.
    
  

  
    KEY IDEA
    
      Computed = SQL expression in the view  • 
      Virtual = unbound / runtime value
    
  

  
  
     When to use a computed column
    
      Derived values such as concatenations, arithmetic, or conditional (CASE) results.
      Aggregations that would otherwise require a nested loop or extra query in X++.
      Read-only display fields on forms and reports that must stay in sync with source data.
      Any scenario where you want the database to do the work instead of the AOS.
    
  

  
  
     Prerequisites (Rule 5)
    
      Visual Studio with the Dynamics 365 developer tools.
      A custom model / package for your objects.
      A view with one or more data sources already defined.
      The computed column's data type declared, and its ViewMethod bound to the static method.
    
  

  
   Code Example — Concatenation computed column
  A static method on the view returning a SQL expression that joins two fields:

// Static method on the view (e.g. AbcCustomerView)
private static server str computeFullName()
{
    DictView dictView = new DictView(tableNum(AbcCustomerView));
    str      firstName, lastName, expression;

    firstName = SysComputedColumn::returnField(
        tableNum(AbcCustomerView),
        identifierStr(CustTable),          // data source name
        fieldStr(CustTable, FirstName));

    lastName = SysComputedColumn::returnField(
        tableNum(AbcCustomerView),
        identifierStr(CustTable),
        fieldStr(CustTable, LastName));

    // Build:  FirstName + ' ' + LastName
    expression = SysComputedColumn::add(
        firstName,
        SysComputedColumn::add(
            SysComputedColumn::returnLiteral(' '),
            lastName));

    return expression;
}

   Conditional (CASE) computed column
private static server str computeStatusText()
{
    // CASE WHEN Status = 0 THEN 'Open' ELSE 'Closed' END
    return SysComputedColumn::caseFunction(
        SysComputedColumn::equalExpression(
            SysComputedColumn::returnField(
                tableNum(AbcOrderView),
                identifierStr(AbcOrderHeader),
                fieldStr(AbcOrderHeader, Status)),
            SysComputedColumn::returnLiteral(0)),
        SysComputedColumn::returnLiteral('Open'),
        SysComputedColumn::returnLiteral('Closed'));
}

   Arithmetic computed column (line total)
private static server str computeLineTotal()
{
    str qty   = SysComputedColumn::returnField(
        tableNum(AbcOrderView),
        identifierStr(AbcOrderLine),
        fieldStr(AbcOrderLine, Qty));

    str price = SysComputedColumn::returnField(
        tableNum(AbcOrderView),
        identifierStr(AbcOrderLine),
        fieldStr(AbcOrderLine, Price));

    // Qty * Price
    return SysComputedColumn::multiply(qty, price);
}

  
   Computed Column vs. Virtual Field vs. Display Method
  
    
      
        
          Aspect
          Computed Column
          Virtual / Unbound Field
          Display Method
        
      
      
        
          Where it runs
          SQL Server (in the view)
          Runtime, not persisted
          X++ on the AOS
        
        
          Performance
          Best (set-based)
          Depends on population
          Row-by-row; should be cached
        
        
          Can be used in WHERE/sort
          Yes
          Limited
          No
        
        
          Typical use
          Derived/aggregated view data
          Temporary runtime values
          Form/report display only
        
      
    
  

  
  
     Points the interviewer wants to hear
    
      Computed columns generate a SQL expression baked into the view.
      Built in X++ via a static method using SysComputedColumn helpers.
      They run set-based in SQL, avoiding row-by-row X++ processing.
      Unlike display methods, computed columns can be used in WHERE and ORDER BY.
      Great for concatenation, arithmetic, CASE logic and aggregation.
    
  

  
  
     Likely Follow-up Questions
    
      Why is a computed column faster than a display method?
      Can you filter or sort on a computed column? Why or why not?
      What happens to the computed column when the view is synchronised?
      When would a virtual/unbound field be more appropriate than a computed column?
    
  

  
  
     Key Takeaway
    
      Computed columns push derived logic into the view's SQL definition using the
      SysComputedColumn class, so calculations run set-based in the database and can be
      used in filters and sorts. Reach for them over display methods whenever performance and reusability matter.
    
  

  
  
     D365 F&O Interview Series — Question 17 of many

Aspect	Computed Column	Virtual / Unbound Field	Display Method
Where it runs	SQL Server (in the view)	Runtime, not persisted	X++ on the AOS
Performance	Best (set-based)	Depends on population	Row-by-row; should be cached
Can be used in WHERE/sort	Yes	Limited	No
Typical use	Derived/aggregated view data	Temporary runtime values	Form/report display only

What is the difference between a computed column and a virtual field on a view in Dynamics 365 Finance & Operations? How do you create a computed column using the SysComputedColumn class, and why is this better for performance than calculating in X++?

Answer with Explanation

Computed & Virtual Columns in Views (Dynamics 365 Finance & Operations)

Interview Question

Model Answer (Short)

Detailed Explanation

How computed columns work

When to use a computed column

Prerequisites (Rule 5)

Code Example — Concatenation computed column

Conditional (CASE) computed column

Arithmetic computed column (line total)

Computed Column vs. Virtual Field vs. Display Method

Points the interviewer wants to hear

Likely Follow-up Questions

Key Takeaway