實作外部聯結
原生編譯的預存程序不支援外部聯結。 下列範例示範如何在原生編譯的預存程序中實作左方外部聯結功能。
此範例使用一個資料表變數來模擬聯結左側的資料指標，並使用另一個資料表變數來建構單一結果集，由於需要建立額外的資料列複本，因此僅適用於處理有限數目的資料列。
首先會使用 t1_type 類型的變數 (@outer) 來反覆查看 t1 中的資料列，並使用 while 迴圈模擬資料指標。 t1t2_join_type類型的變數 @result 接著會用來建構結果集。
您應該測試這個因應措施的執行效果，以確定其在您的應用程式中如預期般執行。
-- 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