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.
Queries enable you to retrieve records from one or more tables or data sources and then combine the data into rows and columns in a single dataset. Queries can also perform calculations on data, such as finding the sum or average of all values in a column of the dataset.
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.
There are two types of query objects: normal and API.
A normal query retrieves records from business data tables in the Dynamics 365 Business Central database, and can be used to display data in the user interface. This type of query is created by a query object. For more information, see Query Object.
An API query is used to generate web service endpoints and this type of page can't be displayed in the user interface. A query of the API type can be used to join data from different data sources. The data can only be viewed. For information about creating a query of the type API, see API Query Type.
Query usage scenarios
The following examples show how you can use queries in your Business Central application.
Allowing users to analyze data from a query using the data analysis mode. For more information, see Analyze list page and query data.
Exposing data as an OData web service. You can register and publish a query as a web service in the same way that you can register and publish pages or codeunits as web services. You use the Web Services page to register and publish pages, codeunits, or queries. After you expose a query as a web service, you can import it into other applications.
Using the query as a data source for a page. To do this, you have to copy the query resulting dataset into a temporary table and set it as the source table for the page.
Using the query as a data source for a report. To do this, create a global variable that points to the query. Then use the variable in the report dataset. For more information see, Defining a Report Dataset.
Performing calculations on data such as computing sums and averages. For more information, see Aggregating data in query objects.
Replacing nested loops that use record variables to retrieve or to detect duplicate records. For more information, see Using Queries Instead of Record Variables.
Creating charts that are based on a query instead of a table.
Saving the data from a query as an .xml or .csv file. For example, you can use the Query.SaveAsXml method to create an .xml file that contains the resulting dataset of a query. You can use the .xml file to integrate with external applications.