Hi,

Could this be the problem?: https://dba.stackexchange.com/questions/264596/subscription-initialization-throws-error-the-option-inline-on-is-not-valid-fo

There may be an issue surrounding this inside SQL2019 at some point. I hope the thread above can help out.

This thread mainly seems to address an old issue that caused the with inline = on to be added to incompatible table valued functions and this was supposedly fixed in with the QL 2019 CU6 update. We’re at CU8. 

I want the with inline = on option. I specifically set it using the subroutine options in the SF. The problem is that this always results in an error.

The thread does contain a single comment mentioning a problem with CU9 but googling this problem doesn’t give any useful results. 

Has there been any success creating inline table valued functions? And is this option in the SF the way to go about creating them?

When the Function cannot be inline an error will be thrown as specified here. Does the function cover all requirements of an inline function? 

It’s an empty template only containing return;
After executing it on the database without with inline = on and checking the inlineable compatibility with 
 

select o.*
from sys.sql_modules o
where exists(
	select 1
	from sys.objects x
	where x.object_id = o.object_id
	and x.name like '%get_calculation%'
)

I can see that is_inlineable is set to 0. Which would indicate that the function doesn’t meet the requirements. I wonder what requirement and empty function doesn’t meet though. 

Adding a tiny template which inserts a row into the table variable which gets returned doesn’t change it either.

I’ll check if there are any other requirements I do not meet with this function.

I can’t get this to work. Manually altering the create function query made it possible to create a table valued function that seems to meet the requirements. 
 

if exists (select 1 from sysobjects
           where name = 'get_calculation_component_overview' and (type = 'FN' or type = 'TF' or type = 'IF'))
   drop function "get_calculation_component_overview"
go
create function "get_calculation_component_overview"
(
   @calculation_id   bigint   
)
returns table
--with inline = on
as
 return select * from calculation_component cc
 where cc.calculation_id = @calculation_id

go

is_inlineable is now set to 1:

If I try to set the with inline = on option it still throws the  “invalid option was specified”  error...

I think with inline = on is not available to TVF, only to Scalar UDF, so table-valued functions cannot be inlineable using this statement.

This might be it. It seems the option isn’t available. There are loads of blog posts mentioning that it is possible to inline TVF’s but none of them use this option. I’ll check if it’s possible to inline a TVF without using the option.

After some testing, TVF’s with the is_inlineable property set to 1 appear to be inlined automatically.

For anyone else who wants to inline a TVF:

TVF’s defined using the SF with the Return table value set will always fail the requirements and won’t be inlined.

Instead leave the return table empty and don’t set the Inline option:

In the template don’t insert values into a table variable, instead return a set immediately: 
 

Check if your TVF is inlineable by using:

select o.*
from sys.sql_modules o
where exists(
	select 1
	from sys.objects x
	where x.object_id = o.object_id
	and x.name = <your TVF name>
)

if is_inlined is set to 1 it should be inlined automatically by SQL server (2019 and up)

It works like a dream now.