Forum Discussion

PavelSivoplyasov's avatar
PavelSivoplyasov
Copper Contributor
Oct 21, 2024

ALTER TVF with NATIVE_COMPILATION option resets uses_native_compilation. Bug?

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
No RepliesBe the first to reply

Resources