------------------------------------------------------------------------------------------
---create table structure and indexes
Create table #TempTable (FK int)
Create table MainTable (Id int, MainKey int, Column2 varchar(100))
Create table SomeAdditionalTable (MainKey int, AdditionalKey int, Column1 int)
go
Create index MainTable_I12 on MainTable(Column2) 
Create index SomeAdditionalTable_Id1 on SomeAdditionalTable (MainKey, AdditionalKey) 
---create table structure and indexes
------------------------------------------------------------------------------------------
go
------------------------------------------------------------------------------------------
---dump plan to sdout and copy it to stdin
set plan dump on 
go
select 
  t.Id, f.Column1, t.MainKey 
from #TempTable s 
  join MainTable t on t.Id = s.FK 
  join SomeAdditionalTable f on f.MainKey = t.MainKey and f.AdditionalKey = 0 
where f.Column1 <> 0 and t.Column2 <> 'SomeTextValue'
plan "( nl_join ( sort ( nl_join ( t_scan ( table ( s #TempTable ) ) ) ( i_scan MainTable_I12 ( table ( t MainTable ) ) ) ) ) ( sort ( i_scan SomeAdditionalTable_Id1 ( table ( f SomeAdditionalTable ) ) ) ) ) ( prop ( table ( s #TempTable ) ) ( parallel 1 ) ( prefetch 2 ) ( lru ) ) ( prop ( table ( t MainTable ) ) ( parallel 1 ) ( prefetch 16 ) ( lru ) ) ( prop ( table ( f SomeAdditionalTable ) ) ( parallel 1 ) ( prefetch 16 ) ( lru ) )"
go
set plan dump off
go
sp_copy_all_qplans ap_stdout, ap_stdin
---dump plan to sdout and copy it to stdin
------------------------------------------------------------------------------------------
go
set showplan on
go
------------------------------------------------------------------------------------------
---try to get the same select with same plan - HER TAM
select 
  t.Id, f.Column1, t.MainKey 
from #TempTable s 
  join MainTable t on t.Id = s.FK 
  join SomeAdditionalTable f on f.MainKey = t.MainKey and f.AdditionalKey = 0 
where f.Column1 <> 0 and t.Column2 <> 'SomeTextValue'
---try to get the same select with same plan - HER TAM
------------------------------------------------------------------------------------------
go