Do you remember this post? I wrote it long time ago to explain what happens on SQL Server when you create a new table or you customize an existing table with an AL extension: the new table is created by appending the extension’s ID to the table’s name and also the customization to a standard table creates a new table with the primary key of the original table plus the newly added fields and also with the extension’s ID in the name:
dbo.[CompanyName$TableName$ExtensionID]
What happens now in Dynamics 365 Business Central Wave 2 release?
As you already know, all the standard business logic is now handled by the Microsoft’s Base Application extension. If you check your on-premise SQL Server database, you will see the following:
All the standard Dynamics 365 Business Central tables are now in the following format:
dbo.[CompanyName$TableName$BaseAppExtensionID]
This happens because the Base Application is simply just another extension and on SQL it respects the standard extension’s behaviour. If you need to create direct integrations via SQL Server (not recommended but possible for on-premise scenarios), you now need to remember to always use the above format in the table names (so, please check all your actual SQL queries).
How to avoid this? How to avoid to break your existing direct SQL integrations? You’ve only the following chances:
- Using APIs instead of SQL integration (but I know that this is not always possible)
- Use SQL views
- Creating synonyms on SQL
A view will permit you to create a query that joins the main table and all the extension’s companion tables that adds extra fields to the main table (tableextension objects in AL). In a view you can also incapsulate business logic for data retrieval and apply security to your data (like removing columns with sensitive information or filtering out a subset of the records).
A synonym is an alias or alternative name for a database object. You can create a synonym in the following way:
use CRONUS; CREATE SYNONYM [dbo].[CRONUS International Ltd_$Customer] FOR [dbo].[CRONUS International Ltd_$Customer$437dbf0e-84ff-417a-965d-ed2bb9650972]; GO
What’s the best choice?
My personal opinion is to start creating integrations by using APIs also if you’re with an on-premise platform. In this way you’re ready for the future and expecially you’re ready for the SaaS platform too.
If instead you’re forced to use direct SQL integrations (I know that in many on-premise scenarios you can’t totally avoid that) using views or synonyms is your choice. I personally prefer views (more control on what you expose) but a synonym is a very quick and effective way to hide the extension’s IDs.
Stefano Demiliani 