SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dbo.[tabela1]
(
    [Id] int NOT NULL identity(1,1),
    [kol1] varchar(20) NOT NULL,
    [Kod] int NULL,  -- 
    CONSTRAINT [PK_tabela1] PRIMARY KEY NONCLUSTERED([id] ASC) WITH (ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON),
    CONSTRAINT [BK_tabela1] UNIQUE CLUSTERED([Kol1] ASC) WITH (ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON),
);
CREATE UNIQUE NONCLUSTERED INDEX [AKF_tabela1_Kod] 
    ON dbo.[tabela1]([Kod] ASC) INCLUDE ([id],[kol1]) 
    WHERE ([Kod] IS NOT NULL) 
    WITH (ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, MAXDOP = 0) ON [PRIMARY];
CREATE TABLE tabela2 (Kod int NOT NULL);
declare @i int=1
while @i<=100
begin
  INSERT tabela1 (kol1, kod)
  values (@i, cast(@i+100 as varchar))
  set @i += 1;
end;
100 rows affected

set statistics xml on;
SELECT 
    t2.Kod, t1.Id, t1.Kol1
FROM 
    tabela2  t2
LEFT JOIN 
    dbo.tabela1 t1 
    on t1.Kod = t2.Kod


Kod
Id
Kol1




Microsoft SQL Server 2005 XML Showplan


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.481" Build="14.0.3045.24"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT &#xd;&#xa;    t2.Kod, t1.Id, t1.Kol1&#xd;&#xa;FROM &#xd;&#xa;    tabela2  t2&#xd;&#xa;LEFT JOIN &#xd;&#xa;    dbo.tabela1 t1 &#xd;&#xa;    on t1.Kod = t2.Kod" StatementId="1" StatementCompId="2" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.00657038" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0xDB8770BF32BD48DB" QueryPlanHash="0x88F61DA12FDAFE6F" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="140"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="24" CompileTime="0" CompileCPU="0" CompileMemory="168"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="419378" EstimatedPagesCached="26211" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1409784"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2018-12-11T11:52:15.01" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000003_34C8D9D1]" Table="[tabela1]" Schema="[dbo]" Database="[fiddle_fb9c4d419fa84ac4a9f219acced8a8ab]"></StatisticsInfo><StatisticsInfo LastUpdate="2018-12-11T11:52:15.01" ModificationCount="0" SamplingPercent="100" Statistics="[PK_tabela1]" Table="[tabela1]" Schema="[dbo]" Database="[fiddle_fb9c4d419fa84ac4a9f219acced8a8ab]"></StatisticsInfo></OptimizerStatsUsage><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Nested Loops" LogicalOp="Left Outer Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-006" AvgRowSize="29" EstimatedTotalSubtreeCost="0.00657038" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_fb9c4d419fa84ac4a9f219acced8a8ab]" Schema="[dbo]" Table="[tabela2]" Alias="[t2]" Column="Kod"></ColumnReference><ColumnReference Database="[fiddle_fb9c4d419fa84ac4a9f219acced8a8ab]" Schema="[dbo]" Table="[tabela1]" Alias="[t1]" Column="Id"></ColumnReference><ColumnReference Database="[fiddle_fb9c4d419fa84ac4a9f219acced8a8ab]" Schema="[dbo]" Table="[tabela1]" Alias="[t1]" Column="kol1"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[fiddle_fb9c4d419fa84ac4a9f219acced8a8ab]" Schema="[dbo]" Table="[tabela2]" Alias="[t2]" Column="Kod"></ColumnReference></OuterReferences><RelOp NodeId="1" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_fb9c4d419fa84ac4a9f219acced8a8ab]" Schema="[dbo]" Table="[tabela2]" Alias="[t2]" Column="Kod"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><TableScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_fb9c4d419fa84ac4a9f219acced8a8ab]" Schema="[dbo]" Table="[tabela2]" Alias="[t2]" Column="Kod"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_fb9c4d419fa84ac4a9f219acced8a8ab]" Schema="[dbo]" Table="[tabela2]" Alias="[t2]" IndexKind="Heap" Storage="RowStore"></Object></TableScan></RelOp><RelOp NodeId="2" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="25" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="100" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_fb9c4d419fa84ac4a9f219acced8a8ab]" Schema="[dbo]" Table="[tabela1]" Alias="[t1]" Column="Id"></ColumnReference><ColumnReference Database="[fiddle_fb9c4d419fa84ac4a9f219acced8a8ab]" Schema="[dbo]" Table="[tabela1]" Alias="[t1]" Column="kol1"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="0" ActualExecutions="0"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_fb9c4d419fa84ac4a9f219acced8a8ab]" Schema="[dbo]" Table="[tabela1]" Alias="[t1]" Column="Id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_fb9c4d419fa84ac4a9f219acced8a8ab]" Schema="[dbo]" Table="[tabela1]" Alias="[t1]" Column="kol1"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_fb9c4d419fa84ac4a9f219acced8a8ab]" Schema="[dbo]" Table="[tabela1]" Index="[AKF_tabela1_Kod]" Filtered="1" Alias="[t1]" IndexKind="NonClustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_fb9c4d419fa84ac4a9f219acced8a8ab]" Schema="[dbo]" Table="[tabela1]" Alias="[t1]" Column="Kod"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[fiddle_fb9c4d419fa84ac4a9f219acced8a8ab].[dbo].[tabela2].[Kod] as [t2].[Kod]"><Identifier><ColumnReference Database="[fiddle_fb9c4d419fa84ac4a9f219acced8a8ab]" Schema="[dbo]" Table="[tabela2]" Alias="[t2]" Column="Kod"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></NestedLoops></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>