WEBCON BPS databases in the SQL 2019 compatibility level

Applies to version: 2022.1.x and above; author: Jacek Język

From WEBCON BPS 2022.1.1, the lowest supported SQL Server is 2014.

In the case of WEBCON BPS installations using the higher versions of SQL Server, WEBCON BPS databases work with the compatibility level set to SQL Server 2014 by default.

Changing the compatibility level from SQL Server 2014 to SQL Server 2019 is possible, but you need to disable the TSQL_SCALAR_UDF_INLINING (Scalar UDF Inlining) option. This option is enabled by default when changing the compatibility level to SQL Server 2019

Microsoft documentation

The impact of the TSQL_SCALAR_UDF_INLINING parameter on the operation of WEBCON BPS

Scalar functions defined in WEBCON BPS mostly perform conversion or formatting operations on single values. There are no subqueries or arithmetic operations on a few columns from the table - passed as input parameters. “Inline” of such functions in WEBCON BPS databases does not improve query performance. However, because they are used in large numbers in SQL queries generated, for example, by application reports, the size of such a query increases significantly during "inline", which leads to a deterioration in the performance of these queries, and even the inability to run them.

Due to the above, "inline" scalar functions should be turned off in WEBCON BPS databases.

From WEBCON BPS 2022.1.2.31, the compatibility level and status of the TSQL_SCALAR_UDF_INLINING parameter are verified when upgrading the system to the higher versions. If the compatibility level of the database (for configuration, content, and archive databases) is set to SQL Server 2019 and at the same time the TSQL_SCALAR_UDF_INLINING parameter is enabled – then the TSQL_SCALAR_UDF_INLINING parameter is disabled for this database.