or
PeterVandivier
sql-server sql-server-2019
[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`.
Disabling function inlining does not reflect on system catalog
PeterVandivier
this is why i don't normally ask questions without thinking about it for at least a week and a half 🤦‍♂️
PeterVandivier
`inline_type`  
> Indicates whether inlining is turned on for the module currently.
PeterVandivier
oh lawd... i needed to literally just scroll down 40 pixels...
caderoux
Anyway, I have it sorted out because all these functions are templated so they have access to the schema and name in macros.  And then the caller who enumerates them can also get to all the meta data in extended properties and join it up.
caderoux
It's just a hash?
caderoux
It's the @@PROCID from the calling context?
caderoux
My original case is in SQL 2012/2016.  So the @@PROCID in an ITVF isn't accurate in the first place?
Paul White
MD5 IIRC.
Paul White replying to PeterVandivier
The value returned by `@@PROCID` for an in-lined item is a hash of the definition so yes it stays the same.
Paul White replying to Andriy M
`@@PROCID` doesn't really make sense with iTVFs. It has always been a weird edge case. The behaviour of UDFs needs to be preserved for backwards compatibility.
PeterVandivier replying to Andriy M
I'll let you take point on that @Andriy
PeterVandivier
i wonder if that's instructive, but that _really is_ another question :-P 
PeterVandivier
hmm... it's curious that procid stays the same in both executions in the fiddle
Andriy M replying to Paul White
Noted. Still, if `@@PROCID` returns the 'wrong' ID in an inline TVF, I can see how it would be consistent if it was made to behave the same with the new inlining mechanism for scalar UDFs.
PeterVandivier
kk, i'll edit those names as well and update
Paul White
I don't mind editing my answer
PeterVandivier
and the chatbot yells at me when i try to edit old things :P
Paul White
foo is an iTVF not an msTVF yeah
PeterVandivier replying to Paul White
yea i did, but that one felt big enough to leave off
PeterVandivier
could also edit the names of the objects to more instructive if you're not opposed to updating your answer as well   
  
foo->dbo.TVF (iTVF?)  
bar>dbo.proc  
baz->dbo.scalar
Paul White
Prefix the link with `<>`
Paul White
Did you know fiddles can be in-lined (ha!) in chat?
PeterVandivier replying to Paul White
can you let me know if you see anything that would damage the thread if i update the post with the new fiddle in the name of tidiness?
PeterVandivier
i updated the fiddle to be a easier to read, a little more instructive & demonstrate the variance between procid & objectid that i was searching for a bit in the original context (although it may not be strictly instructive to this thread)
PeterVandivier
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=5ab3fc579386c97a50ebcb6ea3746b71
Paul White
Could be heavy if you execute scalar functions a lot maybe.
Paul White
For analysis I suppose you could look for `query_tsql_scalar_udf_parsed` events without a matching `query_tsql_scalar_udf_inlined`.
Paul White
For live tuning, it is easier to simply request an estimated plan. If there is a separate plan for the function, it is not in-lined.
PeterVandivier
https://sqlperformance.com/2019/01/sql-performance/scalar-udf-sql-server-2019#comment-162177  
> Glenn Berry says:  
>
> January 25, 2019 at 8:56 PM  
>  
> Currently, there is no DMV/DMF to show which scalar-UDFs are getting automatically inlined. You can query sys.dm_exec_function_stats to see which scalar UDFs are still being executed. After you switch to database compatibility level 150, the scalar UDFs that got automatically inlined will no longer show up in that DMV. It is a little unwieldy.
> 
> I have asked Microsoft to expose which scalar UDFs are getting automatically inlined to make it easier to see what is happening.
Paul White
there might be a more specific one but I don't know it
PeterVandivier
aw... and here i was about to get snarky about there being an XE for everything
Paul White
anyway
Paul White
hm that seems to be just for functions that result in `is_inlineable = 0`
Paul White replying to PeterVandivier
Yes it is sad there is no reason for not in-lining in the execution plan but there is an extended event `tsql_scalar_udf_not_inlineable`
PeterVandivier
i guess i'm just imagining there's at least one DBA out there who checked the `is_inlineable` attribute but isn't looking close enough to realise that one of the options is set to prevent the inlining from taking place
Paul White replying to PeterVandivier
I can't think of any time an eligible function wouldn't be inlined unless something explcit was preventing it like compatibility level, db scoped config, query hint, or `INLINE=OFF` in the def.
PeterVandivier
but as you know, all i am but a SQL Server hobbyist these days so idk if i'll continue down the rabbit hole quite that far
PeterVandivier
probably a whole other question, but i'm a bit curious now about functions getting _actually_ inlined. like... presumably there's situations where inline-eligible functions are not inlined (feel like i've seen mutterings about that around)
PeterVandivier
:-D
Paul White replying to PeterVandivier
Answer posted.
Paul White replying to Andriy M
Inlining scalar functions is a completely new mechanism. It has nothing to do with the way inline TVFs have always been inline.
PeterVandivier
i'll leave it. it might be productive for him to see this if only to comment what CU he's on
Andriy M
or *inlineability*
Andriy M
Though he may still be interested to know that the issue has to do with inlining
Andriy M
@PeterVandivier You might want to call off your suggestion to Cade to try his iTVF on the newest build as it doesn't seem to be relevant for him, as Paul explained above.
PeterVandivier
those get put in a folder for years
Paul White
I'd hate to see a long question :P
PeterVandivier
i sort of typed it super quick since it seemed like i'd done enough poking at it that it warranted a writeup even if just a short/quick one
PeterVandivier
tbh, i don't mind if you edit / re-scope the question a bit to make it more useful
Paul White
The chat here was to confirm your understanding, which version you were using, and what the question was.
Paul White
I'll write up an answer.
PeterVandivier
gotcha, i'll slow down and circle back when i have a minute to read a bit more slowly and actually, y'know, "_comprehend_" or whatever
Paul White
No, my comments above are all based on my understanding of what that column means, and how things work in general.  
I haven't read the docs.
PeterVandivier
doing a few things at once
PeterVandivier
sry, maybe i should slow down a bit
PeterVandivier
oooh, i thought you were saying that's doc'd and intended
Paul White
I also haven't read the docs page yet :)
Paul White replying to PeterVandivier
Which bit?
PeterVandivier
and my opinions are _very_ important to the SQL Server docs team
PeterVandivier
hmm, idk how i feel about that
Paul White
It's the -able bit that is important
Paul White
@PeterVandivier `is_inlineable` is true for an inlineable scalar function even if it is defined `WITH INLINE=OFF`
Paul White
I just saw this question here.
Paul White replying to Andriy M
I don't know I haven't read The Heap 1.0 yet today.
Andriy M
in The Heap 1.0
Andriy M replying to Paul White
Basically what Tom suggested
Paul White
@PeterVandivier `is_inlineable` only tells you if a **scalar** valued UDF is suitable for inlining, not whether it will be or not.
Paul White replying to PeterVandivier
You need to be super clear with the terminology. There are inline table-valued functions (iTVFs), which are always inline by definition, in all versions of SQL Server. **Scalar** valued user-defined functions (UDFs) can be inlined starting with SQL Server 2019.
Andriy M replying to PeterVandivier
There are inline TVFs and multi-statement TVFs. They are different types of objects.
Paul White
`is_inlineable` isn't affected by `ALTER DATABASE SCOPED CONFIGURATION` as expected.
PeterVandivier
so TVFs are allowed to access object_id / procid / et al and still be eligible for inlining? that feels unexpected
Paul White replying to PeterVandivier
No because he is using inline **table** valued functions, not scalar functions
PeterVandivier
assuming of course that the inlining is what borks the `object_name` resolution
PeterVandivier replying to Paul White
🤔i wonder if that means Cade Roux's build process would work again in CU2
Paul White
I haven't checked that yet. There is a lot of ground to cover in this question.
PeterVandivier
fwiw, the base switching question is still valid though, right? when you disable inlining - objects that are validly inlined do not have the attribute modified, correct?
Paul White
>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)
PeterVandivier
yea, my VM is at RTM
Paul White
db<>fiddle is currently at CU1
Paul White replying to PeterVandivier
The `baz` function is marked as not inlineable in CU2 see https://support.microsoft.com/en-nz/help/4538581/fix-scalar-udf-inlining-issues-in-sql-server-2019
PeterVandivier
for additional context, this was in pursuit of tracking down the answer to [this question](https://chat.stackexchange.com/transcript/message/53685082#53685082) started by [this comment](https://chat.stackexchange.com/transcript/message/53681181#53681181)