Реализация внешнего соединения

2017-06-13

Внешнее соединение не поддерживается в нативно скомпилированных хранимых процедурах. В следующем примере показано, как реализовать функциональные возможности левого внешнего соединения в скомпилированной хранимой процедуре.

В примерах используется переменная таблицы для имитации курсора в левой части соединения и табличной переменной для создания одного результируемого набора, который подходит только при обработке ограниченного количества строк, так как он включает создание дополнительной копии строк данных.

Переменная (@outer) типа t1_type используется для итерации по строкам из t1, используя цикл while для имитации курсора. Затем переменная @result типа t1t2_join_type используется для создания результирующего набора.

Вы должны проверить производительность этого обходного решения, чтобы убедиться, что она соответствует вашим ожиданиям в вашем приложении.

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

См. также

Проблемы миграции, связанные с хранимыми процедурами, скомпилированными в собственном коде
Конструкции языка Transact-SQL, не поддерживаемые в выполняющейся в памяти OLTP

Поделиться через

Реализация внешнего соединения

См. также

Дополнительные ресурсы