SQL Workbench Guide

The SQL Workbench is the interactive page in ToolBox where you write and execute SQL queries against your Business Central data. This guide walks you through opening the workbench, running queries, interpreting results, and getting the most out of the tool.

Opening the SQL Workbench

You can open the SQL Workbench from the ToolBox menu within Business Central. The page is called SQL Workbench and can also be found through the BC search bar by typing "SQL Workbench."

Once the page opens, you will see two main areas:

SQL statement editor -- A code editor at the top where you type your SQL statements, with context-aware IntelliSense.
Results grid -- The area below where query results are displayed as rows and columns after execution.

When an AI provider is configured for the ToolBox agents, a third area appears: the SQL Agent FactBox on the right, a chat panel whose agent writes and runs statements for you. See The SQL Agent below.

The Workbench Interface

The workbench is designed to be straightforward. The editor accepts standard SQL statements as documented in the SQL Syntax Reference. You type your query, execute it, and the results appear in the grid.

The page actions include:

Execute SQL -- Runs the statement against the current company's data. The editor's save shortcut executes as well.
Export to Excel -- Executes the statement and downloads the results as an Excel file.
Parse SQL and Show Tokens -- Debugging aids that show how the engine parses your statement without executing it.
Load, Save, and Save As ... -- Work with saved statements; see Saved Statements below.
AI Providers -- Opens the AI Providers page where the provider for the SQL Agent is chosen. Reopen the workbench afterwards to activate the agent.
Clear -- Clears the editor and the result grid to start fresh.

All operations run within the context of your current BC company and respect your user permissions. You will not see data from tables or fields that your permission set does not grant access to.

IntelliSense

The editor suggests as you type, and only suggests constructs the engine supports:

Tables after FROM, JOIN, INSERT INTO, and UPDATE -- matching both table names and captions.
Fields after an alias prefix (c.), in the SELECT list, in WHERE/ON/HAVING/GROUP BY/ORDER BY clauses, in INSERT column lists, and in UPDATE SET -- covering fields from all joined tables.
Keywords and aggregates appropriate to the clause you are in.

Saved Statements

Statements you use repeatedly can be saved by name. Saved statements are shared across companies in the environment, like saved programs.

Action	Effect
Load	Opens the list of saved statements. Selecting one loads its text into the editor and makes it the current statement, so the page caption shows its name.
Save	Saves the editor text under the current statement name. Available only after a statement has been loaded or saved under a name.
Save As ...	Prompts for a name (up to 100 characters) and an optional description and saves the editor text as a new statement, or overwrites the statement with that name.

Each saved statement records its name, description, the statement text, and when it was last modified. The SQL Agent can list, read, load, and save statements too, and it is instructed to reuse your saved statements as examples of house style.

Running Your First Query

Follow these steps to run your first query in the workbench:

Step 1: Open the SQL Workbench from the ToolBox menu or by searching for "SQL Workbench" in the Business Central search bar.

Step 2: In the editor, type the following SELECT statement:

SELECT [No.], Name FROM Customer

Step 3: Run Execute SQL.

Step 4: View the results in the grid below the editor -- one row per customer, with a column for each field you selected.

For non-SELECT statements (INSERT, UPDATE, DELETE), the result reports how many records were affected (rowsAffected).

Limiting Results

SELECT results are capped at 1000 rows by default. Use TOP n to override the cap -- it applies after ORDER BY, so you get a true top-N:

SELECT TOP 10 [No.], Name, [Balance (LCY)] FROM Customer ORDER BY [Balance (LCY)] DESC

For paging through larger result sets, combine ORDER BY with OFFSET/FETCH:

SELECT [No.], Name FROM Customer
ORDER BY Name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY

As a best practice, start with a small TOP value (10-25) when exploring an unfamiliar table, and increase it only when you need more data.

Running Multiple Queries

You can run queries one at a time in the workbench. To execute a different query, simply clear or replace the text in the editor and execute again. Each execution is independent -- the grid updates with the output of the most recent query.

A common workflow when investigating data is to run a series of related queries:

-- First, find the customer
SELECT [No.], Name FROM Customer WHERE Name LIKE 'Adatum%'

After reviewing the result, replace the query and run the next one:

-- Then, look at their sales orders
SELECT [No.], [Sell-to Customer No.], [Order Date], [Amount Including VAT]
FROM [Sales Header]
WHERE [Sell-to Customer No.] = '10000' AND [Document Type] = 'Order'

And follow up with another:

-- Check the lines for a specific order
SELECT [Line No.], [No.], Description, Quantity, [Line Amount]
FROM [Sales Line]
WHERE [Document No.] = 'S-ORD101001' AND [Document Type] = 'Order'

With join support, you can also collapse such an investigation into a single query:

SELECT h.[No.], h.[Order Date], l.Description, l.Quantity, l.[Line Amount]
FROM [Sales Header] h
JOIN [Sales Line] l ON h.[No.] = l.[Document No.]
WHERE h.[Sell-to Customer No.] = '10000' AND h.[Document Type] = 'Order'

Practical Examples

Quick Record Lookup

When you need to find a specific record and you know part of the key or a distinguishing field value, a targeted SELECT is the fastest approach:

SELECT [No.], Name, Address, City, [Phone No.]
FROM Customer
WHERE [No.] = '10000'

This returns the full detail of a single customer without navigating through the Customer Card page.

Finding Records Matching Criteria

To find all items in a specific category with inventory above zero:

SELECT [No.], Description, [Unit Price], Inventory
FROM Item
WHERE [Item Category Code] = 'FURNITURE' AND Inventory > 0

This gives you a quick filtered list that might otherwise require setting up multiple filters on the Item List page.

Aggregating Data

Summarize directly in the workbench instead of exporting raw rows:

SELECT [Salesperson Code], COUNT(*) AS Customers, SUM([Balance (LCY)]) AS Total
FROM Customer
GROUP BY [Salesperson Code]
ORDER BY Total DESC

ORDER BY accepts the alias (Total), the column position (ORDER BY 3 DESC), or the aggregate expression itself (ORDER BY SUM([Balance (LCY)]) DESC).

Data Verification Before and After Changes

When testing a process or posting routine, you can verify the state of data before and after the operation.

Before running the process:

SELECT [Entry No.], [Customer No.], Amount, [Posting Date]
FROM [Cust. Ledger Entry]
WHERE [Customer No.] = '10000'
ORDER BY [Entry No.] DESC

After the process completes, run the same query again and compare the results. New entries will appear at the top of the list (ordered by descending entry number).

Inserting Test Data

During development or testing, you can quickly insert test records -- multi-row VALUES lets you set up several at once:

INSERT INTO [My Custom Table] (Code, Description, Amount)
VALUES ('TEST001', 'Test Record One', 100.00),
       ('TEST002', 'Test Record Two', 200.00)

Verify the insert was successful:

SELECT * FROM [My Custom Table] WHERE Code LIKE 'TEST%'

Cleaning Up Temporary Records

After testing, clean up any temporary or test data you created:

DELETE FROM [My Custom Table] WHERE Code LIKE 'TEST%'

Always run a SELECT first to confirm which records will be affected:

SELECT Code, Description FROM [My Custom Table] WHERE Code LIKE 'TEST%'

Then, once you have confirmed the list is correct, run the DELETE statement.

Performance Tips

The SQL workbench is designed for interactive use. To keep your experience smooth, follow these guidelines:

Use Filters

Always include a WHERE clause when querying tables that you know are large. Unfiltered queries against tables like "G/L Entry," "Value Entry," or "Item Ledger Entry" can return millions of records and will be slow.

-- Good: filtered query
SELECT [Entry No.], [G/L Account No.], Amount
FROM [G/L Entry]
WHERE [G/L Account No.] = '6100' AND [Posting Date] >= '2026-01-01'

-- Avoid: unfiltered query on a large table
SELECT * FROM [G/L Entry]

Limit Rows

Use TOP to keep result sets small. For most interactive work, 25-100 rows is sufficient. Also note that ORDER BY on a column without a matching table key reads all matching rows before sorting, so sorting very large unfiltered tables can be slow.

Avoid SELECT * on Large Tables

Retrieving all fields from a table with many columns increases the amount of data processed and returned. Specify only the fields you need:

-- Better: request only the fields you need
SELECT [No.], Name, [Balance (LCY)] FROM Customer

-- Heavier: all fields from the table
SELECT * FROM Customer

This makes a noticeable difference on tables with dozens of fields.

Use Specific Key Filters When Possible

If you know the primary key value of the record you are looking for, filter by it directly. This allows the BC record API to do an efficient direct lookup rather than scanning the table:

SELECT * FROM Customer WHERE [No.] = '10000'

Error Handling

When a statement fails, the workbench displays an error message instead of results. The engine reports errors rather than silently returning wrong data -- unknown tables, unknown columns (including in WHERE, ORDER BY, and INSERT column lists), unsupported functions, and unsupported joins all produce a message. Here are common errors and how to resolve them.

Table Not Found

Error: The table name you specified does not match any BC table.

Solution: Verify the table name. Matching is case-insensitive and works on names, captions, and numeric IDs -- but spaces and punctuation must be exact, and names containing them need brackets.

-- Wrong: missing space in table name
SELECT * FROM SalesHeader

-- Correct: exact BC table name in brackets
SELECT * FROM [Sales Header]

Unknown Column

Error: A field name in your query does not exist on the specified table.

Solution: Verify the exact field name -- the IntelliSense suggestions are the fastest way. Use brackets for field names that contain spaces or special characters. Check that the field exists on the table (it may be a field from a related table or a page-only field that is not in the underlying table). Note that a bare true or false is also reported as an unknown column -- boolean values must be written as 'true' / 'false' or 1 / 0.

-- Wrong: field name without required brackets
SELECT No., Name FROM Customer

-- Correct: bracketed field names
SELECT [No.], Name FROM Customer

Permission Errors

Error: You do not have permission to access the specified table or perform the requested operation.

Solution: Contact your BC administrator to verify your permission set includes the necessary table data permissions. For SELECT queries you need read access; for INSERT, UPDATE, and DELETE you need the corresponding write permissions.

Syntax Errors

Error: The SQL parser could not understand your statement.

Solution: Review the SQL Syntax Reference to verify your statement follows the supported syntax. Common issues include:

Missing quotes around text values (use single quotes: 'value').
Missing brackets around identifiers with spaces or special characters.
Misspelled SQL keywords.
Missing commas between field names in the SELECT list.

-- Syntax error: missing quotes around text value
SELECT * FROM Customer WHERE [No.] = 10000X

-- Correct: single quotes around text value
SELECT * FROM Customer WHERE [No.] = '10000'

No Records Returned

This is not an error, but it may be unexpected. If your query returns an empty result, verify that:

The table contains data in the current company.
Your WHERE clause filters are not too restrictive.
You are using the correct field values (check for leading/trailing spaces in text comparisons, and remember that LIKE on Text fields is case-sensitive).

Try broadening your filter or removing the WHERE clause with a small TOP to see if the table has any data at all:

SELECT TOP 10 [No.], Name FROM Customer

The SQL Agent

When a provider row on the AI Providers page has the ToolBox Agent toggle on, the workbench shows a SQL Agent FactBox. Describe what you want in plain language and the agent:

reads the statement in the editor and the outcome of your last execution;
looks up tables, fields, option values, keys, and sample rows so it uses names that exist;
writes the statement into the editor, where you can run, tweak, and export it;
executes it on request and shows the result in the grid, or runs a probe query to answer a question without touching the editor;
explains engine errors and fixes the statement;
lists, opens, and saves your saved statements.

The agent knows the ToolBox SQL dialect, including the lookup-join semantics and the row cap, and it is instructed to run a SELECT and get your confirmation before any INSERT, UPDATE, or DELETE. Everything it executes runs with your permissions.

See SQL Agent for details and example requests, and AI Agents Setup to configure a provider.

Calling the SQL Engine from AL Code

While the workbench is the primary interactive interface, you can also call the SQL engine directly from your own AL code by referencing the SQL codeunit. This is useful for building custom tools, test automation, or dynamic query execution.

A typical pattern in AL code would use RunSQL() or ExecuteSQLToText() to run a query string and capture the results. You can use SetMaxRows() before execution to change the default 1000-row cap.

For details on the available functions and their signatures, see the SQL Engine Overview.

Summary

The SQL Workbench gives you a fast, interactive way to query and manipulate Business Central data using SQL syntax. By combining SELECT (with joins, aggregates, and subqueries), INSERT, UPDATE, and DELETE statements with proper filtering and row limits, you can accomplish in seconds what might otherwise require navigating multiple pages and setting up filters manually.

Key takeaways:

Always include WHERE clauses to filter your queries, especially on large tables.
Use TOP to keep result sets small when exploring unfamiliar tables.
Verify your data with a SELECT before running UPDATE or DELETE statements.
Use Export to Excel when you want to analyze or share the results.
Refer to the SQL Syntax Reference for the full list of supported syntax and operators.