Use a Query Object if you have an urge for SQL in Business Central

The Query Object is often overlooked in Business Central, but it does give SQL’ish access to creating queries, check it out:

https://youtu.be/v609PWAUnxg

In this video, Erik explores the Query object in Business Central — one of the least-used object types in AL development, rivaling only the XMLport. He demonstrates how to build a Query object that works similarly to SQL SELECT statements, how to join multiple tables, and how to consume the query results in AL code. If you’ve ever felt the urge to write raw SQL against your Business Central data, the Query object is the supported, AL-native way to achieve similar results.

Working with Data in Business Central vs. SQL

When developing in Business Central, we typically work with data at the table level through AL code. This is quite different from how you’d work with data in SQL, where you write SELECT statements that can join tables, filter columns, and flatten complex relational structures into a single result set.

However, Business Central does offer something that bridges this gap: the Query object. It looks like a blend of AL and SQL, and its purpose is to let the SQL Server behind the scenes do all the heavy lifting — combining tables, filtering, and returning a flat data structure — so your AL code can simply consume the results.

Building a Basic Query Object

The structure of a Query object is similar to how you define a dataset in a Report object. You start by declaring data items (which correspond to tables) and then specify which columns (fields) you want to include.

Here’s a basic query that selects a handful of fields from the Sales Header table:

query 50100 "Query Test"
{
    Caption = 'Query Test';
    QueryType = Normal;

    elements
    {
        dataitem(SalesHeader; "Sales Header")
        {
            column(Document_Type; "Document Type") { }
            column(Number; "No.") { }
            column(Document_Date; "Document Date") { }
            column(Sell_to_Customer_No; "Sell-to Customer No.") { }
            column(Sell_to_Customer_Name; "Sell-to Customer Name") { }
        }
    }
}

This is conceptually similar to a SQL statement like SELECT "Document Type", "No.", "Document Date", "Sell-to Customer No.", "Sell-to Customer Name" FROM "Sales Header".

Partial Records Before Partial Records

An interesting historical note: before the partial records feature was introduced in AL, the Query object was one of the only ways to work with a subset of fields from a table. The idea is simple — if a table has dozens of fields and you only need three, there’s no reason to ask SQL Server for everything. Today you can do this with SetLoadFields on regular records, but the Query object was the original way to achieve this optimization.

Joining Tables with Data Item Links

Where the Query object really shines is when you need to combine multiple tables. Let’s add Sales Lines to our query:

query 50100 "Query Test"
{
    Caption = 'Query Test';
    QueryType = Normal;

    elements
    {
        dataitem(SalesHeader; "Sales Header")
        {
            column(Document_Type; "Document Type") { }
            column(Number; "No.") { }
            column(Document_Date; "Document Date") { }
            column(Sell_to_Customer_No; "Sell-to Customer No.") { }
            column(Sell_to_Customer_Name; "Sell-to Customer Name") { }

            dataitem(SalesLine; "Sales Line")
            {
                DataItemLink = "Document Type" = SalesHeader."Document Type",
                               "Document No." = SalesHeader."No.";
                SqlJoinType = LeftOuterJoin;

                column(Type; Type) { }
                column(Type_Number; "No.") { }
                column(Description; Description) { }
                column(Quantity; Quantity) { }
                column(Unit_Price; "Unit Price") { }
                column(Amount; Amount) { }
            }
        }
    }
}

A few important things to note here:

  • Column naming: Both Sales Header and Sales Line have a “No.” field. You can’t have two columns with the same name, so the line’s “No.” field is renamed to Type_Number in the query.
  • DataItemLink syntax: The syntax is different from what you’d use in reports. The equal sign links the child data item’s field to the parent’s field, and it must be directly translatable to a SQL JOIN clause.

Understanding SQL Join Types

The SqlJoinType property controls how the two data items are combined, and this maps directly to SQL JOIN semantics. The documentation uses the terms “upper” and “lower” data items, where upper = left (the parent) and lower = right (the child).

Left Outer Join

The default. The resulting data set contains every record from the upper (parent) data item, even if there’s no matching record in the lower (child) data item. For example, a Sales Header with no Sales Lines will still appear in the results, but the line fields will be blank. This is equivalent to LEFT OUTER JOIN in SQL.

Right Outer Join

The opposite — every record from the lower data item is included, even without a match in the upper data item. In our example, a Sales Header with order number 1005 that had no lines disappeared from the results entirely, because the Sales Line table is now driving which records appear.

Inner Join

Only records where a match is found between both tables are included. Records with no matching counterpart on either side are excluded. This is usually the most common join type when you only want complete, matching data.

Cross Join

Every record in the upper table is combined with every record in the lower table — a Cartesian product. There is no DataItemLink with a Cross Join. In most cases this doesn’t make practical sense, but it can be useful in rare scenarios involving matrix calculations.

Consuming a Query in AL Code

Beyond displaying query results directly (you can run a query as a page), you can also consume a Query object programmatically in AL. The pattern is straightforward: open, read in a loop, close.

page 50101 "Test Page"
{
    Caption = 'Test Page';
    UsageCategory = Lists;
    ApplicationArea = All;

    layout
    {
        area(Content)
        {
            label(Lbl)
            {
                Caption = 'Query Demo';
                ApplicationArea = All;
            }
        }
    }

    trigger OnOpenPage()
    var
        q: Query "Query Test";
    begin
        q.Open();
        while q.Read() do
            Message('%1 - %2 - %3', q.Document_Type, q.Number, q.Type_Number);
        q.Close();
    end;
}

The key methods on a Query variable are:

  • Open() — Initializes and opens the query for reading.
  • Read() — Returns true and advances to the next row, or false when there are no more rows. This is the equivalent of iterating through a result set.
  • Close() — Closes the query and releases resources.
  • SetFilter() — Allows you to apply filters before opening/reading.
  • SaveAsCsv() / SaveAsXml() — Export the query results directly to a file.

All the fields you defined as columns in the query object are available as properties on the query variable, named exactly as you named them in the query definition.

Additional Query Properties Worth Knowing

Publishing as an API

You can set QueryType = API to publish a query as an API endpoint. This is particularly useful when you need a complex, flattened data structure exposed to Power BI, Excel, or any other external tool. Instead of building a custom API page with complex logic, you let the query handle the joins and just expose the result.

Data Access Intent

The DataAccessIntent property is an interesting performance option. When set to ReadOnly, Microsoft’s hosting infrastructure may route the query to a read-only replica of your database. In Business Central’s cloud architecture, there’s a primary database and a hot-standby replica that’s continuously synchronized (typically a couple of seconds behind). Since the replica doesn’t handle write transactions, it has more capacity for read operations. Directing heavy reporting queries to the replica can improve overall system performance.

Top Number of Rows

You can use the TopNumberOfRows property to limit how many rows the query returns, similar to TOP in SQL.

Summary

The Query object is the closest thing to writing SQL that you’ll find in AL development for Business Central. It lets you define SELECT-like statements with proper JOIN semantics, allows the SQL Server to do the heavy lifting of combining and filtering data, and returns a clean, flat result set for your AL code to consume. Key takeaways:

  • Query objects are ideal when you need to combine multiple tables into a flat structure.
  • They support Left Outer Join, Right Outer Join, Inner Join, and Cross Join — just like SQL.
  • The query translates directly into optimized SQL without the overhead that sometimes comes with BC’s standard record handling.
  • Queries can be published as APIs for external consumption by Power BI, Excel, and other tools.
  • The DataAccessIntent property can route read-only queries to a database replica for better performance.
  • While it’s one of the least-used object types in AL, the Query object has genuinely useful capabilities that are worth understanding and leveraging when the situation calls for it.
Picture of hougaard

hougaard