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.
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
