Question / Problem:
When using a MS tool 'Data Migration Assistant' to migrate on-premises TotalAgility DB (SQL Server 2012 SP4) to Azure SQL Server,
the following warning occurs.
Unqualified Join(s) detected
Starting with database compatibility level 90 and higher, in rare occasions,
the 'unqualified join' syntax can cause 'missing join predicate' warnings, leading to long running queries.
Answer / Solution:
If its only a warning, but the stored procedures are migrated as is, there should be no issue.
As MS provide the tools to migrate the databases, and assuming that results in a completely valid DB structure, then there should be no issue.
This is a change in SQL Engine which has been there since SQL 2005. It was discussed within Product Management,
the use of CROSS JOIN v Inner or outer join, after the release of SQL 2016 and the engine optimizations.
It was decided, as Kofax needed to maintain support for older versions of SQL, that having the older syntax was actually a benefit because Kofax could maintain CE support.
This change in the SQL Engine, in regards to the ANSI join syntax, is not mandatory and the Product Arch team have seen no evidence to suggest this greatly enhances
performance, or the lack of reduces performance. MS don’t even qualify it, all they say is “This could result in a long-running query” but its important to note that
only applies to cross database or where referential integrity (foreign keys) are included in joins. Neither apply to KTA in this context.
The Product Team will review the results from the compatibility report with the intention to potentially update the SPs in a future release
when the older versions of SQL are sunsetted and therefore no requirement for backward compatibility within KTA.