SQL Engine Overview

The ToolBox for Business Central includes a built-in SQL workbench that allows you to query, insert, update, and delete data using familiar SQL syntax. If you come from a database background, you will feel right at home writing queries against your Business Central data without needing to leave the BC environment.

What Is the SQL Engine?

The SQL engine is a custom implementation built entirely within AL. It is not a direct connection to SQL Server. Instead, it translates your SQL statements into Business Central record operations under the hood. This means that all standard BC security filters, permission sets, and record-level security are fully respected when you run a query.

The engine lives in a single SQL Engine codeunit (in the AL Compiler and Interpreter app) and is exposed through the SQL codeunit in the ToolBox app. It is composed of three core stages:

Stage	Role
Lexer	Tokenizer -- breaks your raw SQL text into a stream of tokens
Parser	AST builder -- parses the token stream into an abstract syntax tree
Runner	Executor -- walks the syntax tree and performs the corresponding BC record operations

When you submit a query, the text flows through each stage in sequence: lexer, parser, runner. The runner interacts with the standard BC record API (Record.FindSet, Record.Insert, Record.Modify, Record.Delete, and so on) to carry out the requested operation.

Supported Statement Types

The SQL engine supports the four fundamental SQL statement types:

SELECT -- Retrieve records and fields from any table you have permission to read.
INSERT -- Create new records in a table.
UPDATE -- Modify field values on existing records.
DELETE -- Remove records from a table.

Each statement type follows standard SQL syntax conventions, adapted where necessary for Business Central naming rules. You can find full syntax documentation and examples in the SQL Syntax Reference.

Key Concepts

Table and Field Name Resolution

The SQL engine resolves table and field names using their Business Central names, not their underlying SQL Server names. For example, you would write Customer rather than dbo.Customer$abc123. Field names follow the same rule -- use the BC caption or name as it appears in the AL object definition.

When a table or field name contains spaces or special characters, you must wrap it in square brackets:

SELECT [No.], Name, [Country/Region Code] FROM Customer

If a name contains no spaces or special characters, brackets are optional:

SELECT Name FROM Customer

Tables and fields can also be referenced by their caption or by their numeric ID (FROM 18 queries the Customer table).

Row Limiting

SELECT results are capped at 1000 rows by default. Use TOP n in the query to raise (or lower) the cap, or call the SetMaxRows() function on the SQL codeunit when using the engine programmatically. This is especially important in production environments where tables may contain hundreds of thousands of records.

Results Format

In the SQL Workbench, query results are displayed in a results grid -- one row per record, one column per selected field -- and can be downloaded with the Export to Excel action. When you call the SQL engine programmatically from AL code, results are returned as JSON: each record appears as a JSON object with field names as keys, which is easy to process in your own code.

Security and Permissions

Because the SQL engine operates through the standard BC record API, all of the following apply:

Table permissions -- You can only query tables that your user has read (or write) access to.
Field-level security -- Restricted fields are not accessible.
Record-level security filters -- Any security filters applied to your user are respected automatically.
Company boundaries -- Queries run against the current company context.

You do not need to configure anything special. The same permissions that govern your normal BC operations govern your SQL queries.

Available Functions

The SQL codeunit exposes the following public functions:

Function	Description
`ParseSQL()`	Parses a SQL string into an internal AST representation without executing it. Useful for validation.
`ExecuteSQL()`	Parses and executes a SQL statement, returning results as a structured object.
`ExecuteSQLToText()`	Parses and executes a SQL statement, returning results as formatted text output.
`RunSQL()`	Runs a SQL statement through the full pipeline (lex, parse, execute).
`RunAndExecuteSQL()`	Combines parsing and execution in a single call with additional context handling.
`GetTableNo()`	Resolves a BC table name to its corresponding table number.
`GetFieldNo()`	Resolves a field name within a table to its corresponding field number.
`SetMaxRows()`	Sets the maximum number of rows the engine will return from a SELECT query.
`TokenizeSQL()`	Runs only the lexer stage, returning the raw token stream. Useful for debugging.
`ParseSQLToText()`	Parses a SQL statement and returns a text representation of the AST. Useful for debugging.

The underlying SQL Engine codeunit additionally offers an ExecuteSQL() overload and an Execute() function that take the SELECT row cap directly as a parameter (0 or less uses the default of 1000).

Common Use Cases

The SQL workbench is designed for interactive, ad-hoc work. Here are some of the most common scenarios where you will find it useful:

Quick data queries -- Look up a specific customer, vendor, or item without navigating through pages and filters. For example, you can retrieve a customer's balance, address, and contact information in a single query rather than opening the Customer Card page and scrolling through FastTabs.
Ad-hoc reporting -- Pull a filtered list of records matching certain criteria for a quick check or export. When a manager asks "how many open sales orders do we have for customer X?", you can answer that question in seconds with a targeted SELECT.
Data verification -- Confirm that a posting routine created the expected ledger entries, or verify field values before and after a process runs. This is particularly valuable during testing and go-live phases when you need to validate that business logic is producing the correct results.
Cross-table lookups -- Query multiple tables in succession to trace data relationships. For example, you might first query the Sales Header table to find an order number, then query Sales Line to see the line items, and finally check Item Ledger Entry to verify that the posting created the expected entries.
Development and debugging -- Inspect table contents during AL development without switching to a separate database tool. When you are writing AL code that modifies records, the workbench gives you a fast way to check the state of the data at any point.
Test data management -- Insert test records or clean up temporary data during development and QA cycles. The INSERT and DELETE statements let you set up and tear down test scenarios quickly without writing throwaway AL code.

How It Works Under the Hood

When you type a SQL statement and execute it, the following steps occur:

Lexing -- The lexer stage scans your input character by character, producing a sequence of tokens (keywords, identifiers, operators, literals, and so on).
Parsing -- The parser stage reads the token stream and constructs an abstract syntax tree (AST) that represents the structure of your statement.
Execution -- The runner stage walks the AST, resolves table and field names to their BC object IDs, constructs the appropriate record filters and operations, and executes them against the BC database through the record API.
Result formatting -- For SELECT statements, the returned records are serialized into JSON and shown in the workbench results grid.

This architecture means that the SQL engine is fully extensible and testable. You can call individual stages (tokenize only, parse only) for debugging purposes, or call the full pipeline through a single function.

Limitations to Be Aware Of

The SQL engine supports joins, aggregates with GROUP BY/HAVING, uncorrelated subqueries, and set operations -- see the SQL Syntax Reference for the full feature surface. A few SQL features remain unsupported:

Lookup joins -- Joins pair each main row with the first matching joined record rather than multiplying rows like a full SQL join (CROSS JOIN is the exception). RIGHT JOIN and FULL JOIN are not supported.
No correlated subqueries -- Subqueries run once and cannot reference the outer query's tables.
No derived tables -- FROM (SELECT ...) is not supported.
No scalar functions -- String/date functions like UPPER or DATEADD do not exist; only the five aggregates (COUNT, SUM, AVG, MIN, MAX) are available.
No stored procedures -- The engine executes individual statements only.
FlowFilters -- FlowFields are calculated automatically, but FlowFilter fields cannot be queried.

For full details on supported syntax, operators, and data type handling, see the SQL Syntax Reference.

Comparing the SQL Engine to Direct SQL Server Access

If you are accustomed to querying the BC database directly through SQL Server Management Studio or similar tools, it is important to understand the differences when using the ToolBox SQL engine:

Aspect	Direct SQL Server	ToolBox SQL Engine
Connection	Direct database connection	AL record API
Table names	SQL Server object names with prefixes	BC table names as defined in AL
Security	SQL Server permissions	BC permission sets and security filters
JOINs	Row-multiplying joins	Lookup joins (first match); INNER/LEFT/CROSS
Aggregation	GROUP BY, SUM, COUNT, etc.	COUNT, SUM, AVG, MIN, MAX with GROUP BY/HAVING
Triggers	Bypassed	Respected (OnInsert, OnModify, OnDelete)
FlowFields	Computed at SQL level	Calculated automatically
Company isolation	Requires company filter	Automatic

The key advantage of the ToolBox SQL engine is that it operates within the BC application layer. Your writes go through the standard trigger chain, your reads respect security filters, and you never risk bypassing business logic. The trade-off is the lookup-join model and the absence of correlated subqueries, derived tables, and scalar functions.

Getting Started

The fastest way to get started is to open the SQL Workbench page and run a simple SELECT query. See the SQL Workbench Guide for step-by-step instructions on using the interactive interface.

If you prefer to call the SQL engine programmatically from your own AL code, you can reference the SQL codeunit directly and use the functions listed above.

A good first exercise is to run a few SELECT queries against familiar tables like Customer, Item, or Vendor. This will help you get comfortable with the syntax and the JSON output format before moving on to INSERT, UPDATE, or DELETE operations.