Recently I have had several conversations about SQL vs AL. Lots of new talent is coming into the channel, often with SQL knowledge, but how to apply that in an AL-driven world? In this video, I discuss AL vs SQL:
In this video, Erik discusses a crucial concept for developers entering the Business Central ecosystem: AL is not SQL. While Business Central runs on SQL Server, it is fundamentally not a “SQL application.” Erik explains the layers of abstraction between AL code and the underlying database, highlights how table design patterns differ from traditional SQL conventions, and uses the Item Attributes table as an example of what happens when SQL-style patterns are introduced into Business Central — often with problematic results.
Business Central Is Not a SQL Application
Business Central is experiencing significant growth, bringing in new partners, consultants, and developers from a wide range of backgrounds. Many of these developers come with SQL experience, and while that knowledge is certainly valuable, it can also lead to some fundamental misunderstandings about how Business Central works.
The key point Erik emphasizes is: Business Central is not a SQL application. Yes, it uses SQL Server as its database engine, but SQL sits behind multiple layers of abstraction. It’s a distant technology that the developer rarely interacts with directly.
Triggers Are Not SQL Triggers
A common misconception among developers coming from SQL backgrounds involves triggers. In Business Central, we have OnInsert, OnModify, and OnDelete triggers on tables. A SQL developer might see these and think, “I know what that is — when we insert a record in SQL, a trigger fires a stored procedure and things happen.”
That is not how Business Central works. While there might be stored procedures under the hood for technical purposes, there is not a single stored procedure that holds business logic, and there is not a single SQL trigger that fires business logic. When you write code in an OnInsert trigger in AL, that code executes in the service tier. It might ultimately result in some SQL operations, but the business logic lives entirely in the AL layer.
The Historical Context: AL Was Designed for a Non-SQL Database
Understanding the history of Business Central helps explain why things work the way they do. The AL language (and its predecessor C/AL) was originally designed for a non-SQL database — the native Navision database. The entire application was written with the intent of running on that proprietary database engine.
When SQL Server was introduced as an option, things were adapted — table locking mechanisms were changed, SQL-specific features were added — but the overall structure of how the system works was designed for a non-SQL world. Microsoft now spends enormous resources translating and transforming what developers write in AL into SQL statements that are as efficient as possible.
This creates an interesting tension. There are likely a handful of engineers at Microsoft who could instantly name four or five things they’d love to remove from AL, because developers can do things in AL that make SQL optimization incredibly complicated. For example, you can set a filter, execute a FindNext, then change the filters on the fly — all sorts of operations that made perfect sense with the original database but create headaches for SQL translation.
Table Design Patterns in Business Central
One of the most evident differences between Business Central and traditional SQL development is how tables are designed. In Business Central, the typical pattern looks like the Item table:
- The primary key is a Code field (e.g., “Item No.”)
- Every table has a primary key that must be unique
- The Code field type is essentially a text field — all uppercase, with white spaces truncated
- Even though the field is called “No.” (number), it’s still a text field that accepts alphanumeric characters
This is the pattern you would follow when creating something new in Business Central: a Code primary key, a Description field, and whatever other fields you need. When referencing this table from elsewhere, you reference the Code primary key. This pattern is consistent across hundreds of tables in the base application.
When Patterns Break: The Item Attributes Example
Erik then demonstrates what happens when this pattern is broken, using the Item Attributes feature as an example. Looking at the Item Attribute table definition, we can see something unusual:
- Field number one is an ID with AutoIncrement
- The primary key is just the ID field — an auto-incrementing integer
- Instead of a Code field with a Description, it uses a “Name” field
- The Name field allows mixed case (not uppercase like Code fields)
- There’s no sorting by name visible in the UI
Using the page inspector, Erik shows that newly created attributes are simply assigned sequential IDs (7, 8, etc.). This auto-increment integer pattern is perfectly common in SQL development, but it’s very unusual in Business Central.
The Problems This Creates
Because the primary key is an auto-incrementing integer rather than a meaningful Code value, the system needs special logic to handle lookups. Looking at the source code, there are functions like FindItemAttributeCaseSensitive and FindItemAttributeCaseInsensitive — code that has to filter and look up records by name to find the corresponding ID. This is standard fare in SQL applications but awkward and unusual in AL.
More critically, this design causes real data issues in multi-company scenarios. If you have multiple companies where inventory should be the same, attributes created in a different order in each company will receive different IDs. When you try to replicate data or use intercompany features, it becomes a mess because the IDs don’t align.
Erik is candid about his feelings on this implementation: if you saw this table design in isolation, written for MySQL or any other SQL database, nobody would raise an eyebrow. It’s perfectly fine for a traditional SQL application. But that’s not how Business Central’s data model works, and introducing a foreign pattern into an established ecosystem creates confusion and practical problems.
Joins, Queries, and Filtering
Another major difference from SQL development is that Business Central’s data model is mostly without joins. Developers tend to work directly on individual tables. While there is a Query object type in AL that can encapsulate a SELECT statement with joins, it’s used relatively rarely — mostly for data export and reporting rather than core business logic.
That said, Erik acknowledges that AL developers could learn something from the SQL mindset when it comes to filtering. SQL developers tend to carefully think about their SELECT statements and WHERE clauses, building up precise data selections. AL developers can sometimes be “sloppy” with filters and keys, which impacts performance. Adopting more of the SQL discipline around thinking about data retrieval — while still working within AL’s table-based patterns — would benefit many developers.
The Path to Success: Imitate the Right Patterns
One of the best-kept “not-so-secret” secrets to becoming a successful Business Central developer is to study how things are done in the base application and imitate those patterns. Look at how documents work. Look at how the Item table, Customer table, and similar master data tables are constructed. Then follow those same conventions in your customizations.
Do not imitate the Item Attributes pattern — even though some developers might look at it and say, “I understand this approach.” It’s the odd one out, and following it leads to code and data structures that don’t fit the rest of the ecosystem.
Erik also references a great point from Henrik’s blog (The Double H): always write code with the intent that somebody else will have to read it. You won’t be the last person looking at your code. In the case of AL, the next person reading your code is almost certainly an AL developer. If you write AL following AL conventions, people can understand it immediately. If you write AL using SQL conventions, it becomes harder for the broader community to maintain and extend.
Summary
The core takeaway is simple but important: AL is not SQL. While SQL Server powers the database behind Business Central, the application layer operates with its own conventions, patterns, and design philosophy — one that was originally built for a completely different database engine. Developers entering the Business Central world from a SQL background should embrace AL’s patterns rather than importing SQL conventions. Study the base application, follow established table design patterns (Code primary keys, no auto-increment on master data), and write code that other AL developers will find natural and maintainable.
