How does SQL Server set the value of uses_native_compilation?

Question

Running ALTER on a TVF with the NATIVE_COMPILATION option resets the uses_native_compilation value in sys.sql_modules to 0.

Let's assume there is a TVF called dbo.fn:

create function dbo.fn()
returns table
with native_compilation, schemabinding
as
    return select 1 as result;
go

This function has 1 in the uses_native_compilation column in sys.sql_modules after creation. Next, I need to execute an ALTER as a result of some logical changes or whatever. Let us keep it as simple as possible:

alter function dbo.fn()
returns table
with native_compilation, schemabinding
as
    return select 1 as result;
go

i.e. a simple ALTER, even without changing the function body, results in uses_native_compilation in sys.sql_modules being set to 0. Even though the text in the "definition" column might say "...with native_compilation...", it doesn't actually mean that anymore. TVF become non-native.

This behavior leads to some negative outcomes:

1. Attempting to execute ALTER again results in an error:

Cannot ALTER a non-native module into a natively compiled module. Omit WITH NATIVE_COMPILATION or drop and recreate the module.

2. Attempting to create a natively compiled module that uses this function results in an error:

Object 'dbo.fn' is not a memory optimized table or a natively compiled inline table-valued function and cannot be accessed from a natively compiled module.

Is this a bug or am I doing something wrong?

Script to reproduce:

drop function if exists dbo.fn
go

create function dbo.fn()
returns table
with native_compilation, schemabinding
as
    return select 1 as result;
go

-- 1
select uses_native_compilation from sys.sql_modules where object_id = object_id('dbo.fn')
go

alter function dbo.fn()
returns table
with native_compilation, schemabinding
as
    return select 1 as result;
go

-- 0
select uses_native_compilation from sys.sql_modules where object_id = object_id('dbo.fn')
go

Reproduced in 2019 (I'm interested in) and 2022:

1. the latest SQL Server 2022 Developer. Just installed, no extra configuration was made.

Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64)

2. SQL Server 2019 Developer.

Microsoft SQL Server 2019 (RTM-CU28)(KB5039747) - 15.0.4385.2 (X64)

3. SQL Server 2019 Developer.

Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)

If you can't reproduce when running CREATE and ALTER sequentially in different batches, as I failed with 15.0.2000.5, then try using this script with a loop (tests showed the need for a delay of up to 65 seconds before ALTER affects native, no idea why):

declare @dt datetime2(7) = current_timestamp;

while exists (select 1 from sys.sql_modules where object_id = object_id('dbo.fn') and uses_native_compilation = 1)
begin
    waitfor delay '00:00:01';

    exec('alter function dbo.fn()
returns table
with native_compilation, schemabinding
as
    return select 1 as result;');
end

select datediff(MILLISECOND, @dt, current_timestamp), current_timestamp, uses_native_compilation from sys.sql_modules where object_id = object_id('dbo.fn')
go

Answer 1

tests showed the need for a delay of up to 65 seconds before ALTER affects native, no idea why

If I understand what you're saying correctly, and I know your example uses a table-valued function, but I assume the following is also true for them, as the Microsoft docs state the following in Scalar User-Defined Functions for In-Memory OLTP:

When you alter a natively compiled, scalar user-defined function, the application remains available while the operation is being run and the new version of the function is being compiled.

I wonder if what you're seeing is just the state of uses_native_compilation while it's in the middle of still being compiled natively, but also remaining available. Perhaps if you wait long enough, that'll eventually resolve to show the correct value?