Get started with queries


Many situations occur in which iterative data access is incorrect. Business Central has access to the powerful relational database management engine of Microsoft SQL Server to select complex, multi-table sets of data in a fast and efficient way.

The Query object of Business Central enables you to define relational data models that are translated into efficient SELECT statements. SQL Server can run these statements as single data retrieval operations. This approach helps ensure optimal performance and minimal pressure on system resources.

Queries specify datasets from the Business Central database for retrieval in a fast and efficient way. You can use queries to retrieve data from one or more tables as a single, flat result set. You can specify how to join multiple tables in the result set and how to order, group, aggregate, and filter the resulting data.

Queries retrieve data efficiently because they are always translated into a single SELECT statement and are run against the underlying SQL Server database. Data is selected, joined, grouped, ordered, aggregated, and filtered at the SQL Server level, which ensures a minimum effect on performance.

You can perform the following tasks with queries:

  • Use them as sources of charts in Business Central.

  • Save them as XML or CSV files (by using streams).

  • Access them from AL code.

  • Publish them as OData web services for later consumption from other clients, such as Power BI or other applications in Microsoft Power Platform.

A query describes a dataset of Business Central data. Queries retrieve records from one or more tables and combine the records into rows and columns in a single dataset. You can create queries in the development environment.

Queries have the following capabilities:

  • Select subsets of fields from multiple tables.

  • Join tables with different linking criteria.

  • Filter tables by specifying filtering criteria.

  • Group and aggregate data.

  • Order data.

  • Limit the number of rows to retrieve.

  • Apply date methods.