Implementing an Outer Join
Outer join is not supported in natively compiled stored procedures. The following sample shows a way to implement the functionality of a left outer join in a natively compiled stored procedure.
The samples uses a table variable to simulate a cursor on the left side of the join, and a table variable to construct a single result set, which is only suitable when processing a limited number of rows as it involves creating an additional copy of the data rows.
A variable (@outer) of type t1_type is used to iterate over the rows from t1, using a while loop to simulate a cursor. The variable @result of type t1t2_join_type is then used to construct the result set.
You should test the performance of this workaround, to be sure that it performs as expected in your application.
-- original query:
select
t1.c1 as t1c1,
t1.c2 as t1c2,
t2.c2 as t2c2,
t2.c3 as t2c3
from t1 left join t2 on t1.c2=t2.c3
GO
create table dbo.t1
(c1 int not null primary key nonclustered,
c2 int not null) with (memory_optimized=on)
create table dbo.t2
(c2 int not null primary key nonclustered,
c3 int not null) with (memory_optimized=on)
INSERT t1 VALUES (1,2)
INSERT t1 VALUES (2,3)
INSERT t1 VALUES (3,2)
INSERT t2 VALUES (2,3)
INSERT t2 VALUES (4,3)
GO
create type dbo.t1_type as table
(
id int identity not null primary key nonclustered hash with (bucket_count=1024),
c1 int,
c2 int
) with (memory_optimized=on)
GO
create type dbo.t1t2_join_type as table
(
t1c1 int not null index ix_t1c1,
t1c2 int not null,
t2c2 int,
t2c3 int
) with (memory_optimized=on)
GO
-- ====== scenario: generic left join
-- stored procedure including the workaround
create procedure dbo.usp_left_join
with native_compilation, execute as owner, schemabinding
as
begin atomic with (transaction isolation level = snapshot, language = N'us_english')
DECLARE @outer dbo.t1_type
DECLARE @result dbo.t1t2_join_type
-- populate the variable used for iterating over the outer rows
INSERT @outer(c1, c2) select c1,c2 from dbo.t1
DECLARE @i int = 1
DECLARE @max int = scope_identity()
DECLARE @t1c1 int
DECLARE @t1c2 int
while @i <= @max
begin
select @t1c1 = c1, @t1c2 = c2 from @outer where id = @i
INSERT @result select @t1c1, @t1c2, c2, c3 from dbo.t2 where c3 = @t1c2
if @@rowcount = 0
INSERT @result (t1c1, t1c2) VALUES (@t1c1, @t1c2)
set @i += 1
end
select
t1c1,
t1c2,
t2c2,
t2c3
from @result
end
GO
exec dbo.usp_left_join
See Also
Migration Issues for Natively Compiled Stored Procedures
Transact-SQL Constructs Not Supported by In-Memory OLTP