Yes, an in-lined function can show different results than its out-lined (!?) counterpart. The following reliably reproduces the situation on my machine (Windows 10, 4 cores + HT @ 2GHz, 16GB RAM, SSD).
Configure the database and session to use Read Committed Snapshot Isolation (RCSI):
alter database Sandpit
set read_committed_snapshot on
with rollback immediate;
GO
set transaction isolation level read committed;
GO
This table will give a shared object on which concurrent workloads can act.
drop table if exists t;
go
create table t(c int);
insert t(c) values (1);
go
This table is to capture the results from the test, hopefully revealing divergent behaviour between functions that are in-lined and not:
drop table if exists #Out;
go
create table #Out(Base int, Old int, New int);
go
To demonstrate the different behaviour I want two function executed within a single SELECT, one of which in in-line and the other isn't. The documentation says
A scalar T-SQL UDF can be inline if .. the UDF does not invoke any intrinsic function .. such as GETDATE()
To ensure one UDF cannot be in-line I add a reference to GETDATE. Note that this additional statement plays no part in the logic of the UDF, it simply suppresses in-lining. (Indeed, this function could be optimised away. Perhaps some future release will implement just such a optimization?)
create or alter function dbo.Old_skool()
returns int
as
begin
declare @tot int = 0;
declare @d date = GETDATE(); -- inhibits in-lining
select @tot = SUM(C) from t;
return @tot;
end
go
create or alter function dbo.New_kid_on_the_block()
returns int
as
begin
declare @tot int = 0;
select @tot = SUM(C) from t;
return @tot;
end
go
To reference the shared table I've arbitrarily chosen to use SUM. I believe, but haven't tested, that any other technique that surfaces differences in the rows seen by the functions and the containing SELECT (MIN, MAX, TOP(1)) would do just as well.
Next I start two sessions. The first is to perform the SELECT, the second to make concurrent writes against the shared table.
-- Session 1 for reads
set transaction isolation level read committed;
GO
truncate table #Out;
declare @c int = 0;
while @c < 99 -- large enough to exhibit the behaviour
begin
insert #Out(Base, Old, New)
select
c,
dbo.Old_skool(),
dbo.New_kid_on_the_block()
from t;
set @c += 1;
end
-- Session 2 for writes
declare @c int = 0;
while @c < 99999
begin
update t set c = c + 1;
set @c += 1;
end
I set running the session performing writes. On my machine it runs for about 24s, which is ample time to switch to session 1 (the reads) and start it.
For one run over 99 SELECTs there are 12 instances where the in-line and traditional execution mechanism return different results. In every case the in-lined function returns the same result as the containing query (which is not to say this test shows such behaviour is guaranteed).
Base Old New
----------- ----------- -----------
1801 1802 1801
1803 1804 1803
1814 1815 1814
1841 1842 1841
1856 1857 1856
1857 1858 1857
1860 1861 1860
1861 1862 1861
1864 1865 1864
1883 1884 1883
1884 1885 1884
1890 1891 1890
