PeterVandivier
[Aaron Bertrand tells us][1] re: function inlining...
> ...you can control this at the database level, but separate from compatibility level \[using...]
```sql
ALTER DATABASE SCOPED CONFIGURATION
SET TSQL_SCALAR_UDF_INLINING = OFF;
```
One would expect that if inlining is disabled, then the `is_inlineable` attribute on [`sys.sql_modules`][2] as mentioned in the same article would reflect this. However, this does not appear to be the case.
Giving the existence of inlineable functions `dbo.iTVF()` and `dbo.scalar()` as seen in the below fiddle, I seem to be unable in local testing to disable inlining even after marking the functions for recompile, reducing the compatibilty level & restarting the database, and subsequently restarting the instance. Have I _actually_ disabled inlining for these functions? Is this a bug in the `sql_modules` catalog? Or is it calculated at the instance level (despite `sql_modules` being scoped to the calling database context) and I just need to take a more literal reading of [the requirements regarding the `is_inlineable` property][3]?
<>https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=6a2be4881c680d402aec17a66150401c
[1]: https://sqlperformance.com/2019/01/sql-performance/scalar-udf-sql-server-2019
[2]: https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-sql-modules-transact-sql
[3]: https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/scalar-udf-inlining?view=sql-server-ver15#inlineable-scalar-udfs-requirements
Top Answer
Paul White
## is_inlineable
>One would expect that if inlining is disabled, then the `is_inlineable` attribute on `sys.sql_modules` as mentioned in the same article would reflect this.
The `is_inlineable` attribute on `sys.sql_modules` reflects whether a scalar user-defined function (UDF) is **capable** of being in-lined given the T-SQL definition in the module.
It is **not affected** by the various settings that can disable in-lining in practice. For example, a UDF that uses `WITH INLINE=OFF` will still show as being **capable** of in-lining (if that is the case).
A primary use for this column is in identifying and testing scalar UDFs for SQL Server 2019 before upgrading.
## Scalar and table-valued functions
The ability to in-line scalar UDFs was introduced with SQL Server 2019. Prior to that, they could never be in-lined by the server. Inline table-valued functions (iTVFs) are a different animal, and are always inline by definition, in all versions.
The word 'inline' may be the same, but in-lining of scalar UDFs in 2019 is a **completely new and separate implementation** from that used to provide inline TVFs.
* Scalar UDFs return a single scalar value and have `BEGIN...END` in their definition.
* Inline table-valued functions (iTVFs) return a table-specification (like a parameterized view) and do not have `BEGIN...END`. iTVFs are always expanded into the referencing query before compilation begins.
* For completeness: Multi-statement table-valued functions (msTVFs) return data via a table variable. The body of an msTVF uses `BEGIN...END`.
## Function dbo.scalar()
This was incorrectly marked as eligible for in-lining in SQL Server 2019 RTM and CU1. The **bug** means in-lining changes the behaviour of the function.
This is fixed in Cumulative Update 2 for SQL Server 2019. The specific fix is [FIX: Scalar UDF Inlining issues in SQL Server 2019](https://support.microsoft.com/en-nz/help/4538581/fix-scalar-udf-inlining-issues-in-sql-server-2019), which says:
>This cumulative update also **blocks** Inlining in the following scenarios:
>* If the UDF references certain intrinsic functions (e.g. @@ROWCOUNT) that may alter the results when Inlined
>* Aggregate functions being passed as parameters to a scalar UDF
>* If the UDF references built-in views (e.g. OBJECT_ID)
When the fix is applied, and the function `dbo.scalar` recompiled, it is no longer eligible for in-lining on SQL Server 2019 CU2. At the time of writing, db<>fiddle was still using CU1 so you will see the unfixed behaviour there.
The extended event `tsql_scalar_udf_not_inlineable` reports the reason the function cannot be in-lined as `SystemDataAccess`. If the reference to `sys.objects` is removed by using `OBJECT_NAME` instead, the event reports a new reason for not being able to inline the function: `UnsafeBuiltin`.