add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
8 rows affected
5 rows affected
EmployeeID Title Role RoleName Status
1 Title-1 1 Role-1 D
2 Title-2 2 Role-2 D
3 Title-3 3 Role-3 A
5 Title-5 3 Role-3 null
6 Title-6 3 Role-3 B
7 Title-7 2 Role-2 C
8 Title-8 1 Role-1 null
EmployeeID Title Role RoleName
3 Title-3 3 Role-3
6 Title-6 3 Role-3
7 Title-7 2 Role-2
Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
3 1 SELECT
       [EmployeeID]
      ,[Title]
      ,employee.[Role]
      ,roles.Role AS RoleName
FROM [dbo].Employee AS employee
INNER JOIN [dbo].Roles AS roles ON roles.id = employee.Role
WHERE [Status] <> 'D'
1 1 0 null null null null 3.5 null null null 0.00706876 null null SELECT False null
3 1   |--Nested Loops(Inner Join, OUTER REFERENCES:([employee].[Role])) 1 2 1 Nested Loops Inner Join OUTER REFERENCES:([employee].[Role]) null 3.5 0 1.672E-05 58 0.00706876 [employee].[EmployeeID], [employee].[Title], [employee].[Role], [roles].[Role] null PLAN_ROW False 1
4 1        |--Clustered Index Scan(OBJECT:([fiddle_873f68a37750498183b6baea51fa9e29].[dbo].[Employee].[PK_Employee] AS [employee]), WHERE:([fiddle_873f68a37750498183b6baea51fa9e29].[dbo].[Employee].[Status] as [employee].[Status]<>'D')) 1 3 2 Clustered Index Scan Clustered Index Scan OBJECT:([fiddle_873f68a37750498183b6baea51fa9e29].[dbo].[Employee].[PK_Employee] AS [employee]), WHERE:([fiddle_873f68a37750498183b6baea51fa9e29].[dbo].[Employee].[Status] as [employee].[Status]<>'D') [employee].[EmployeeID], [employee].[Title], [employee].[Role] 4 0.003125 0.0001658 46 0.0032908 [employee].[EmployeeID], [employee].[Title], [employee].[Role] null PLAN_ROW False 1
3 4        |--Clustered Index Seek(OBJECT:([fiddle_873f68a37750498183b6baea51fa9e29].[dbo].[Roles].[PK_Roles] AS [roles]), SEEK:([roles].[id]=[fiddle_873f68a37750498183b6baea51fa9e29].[dbo].[Employee].[Role] as [employee].[Role]) ORDERED FORWARD) 1 4 2 Clustered Index Seek Clustered Index Seek OBJECT:([fiddle_873f68a37750498183b6baea51fa9e29].[dbo].[Roles].[PK_Roles] AS [roles]), SEEK:([roles].[id]=[fiddle_873f68a37750498183b6baea51fa9e29].[dbo].[Employee].[Role] as [employee].[Role]) ORDERED FORWARD [roles].[Role] 1 0.003125 0.0001581 23 0.0037574 [roles].[Role] null PLAN_ROW False 4
EmployeeID Title Role RoleName
3 Title-3 3 Role-3
6 Title-6 3 Role-3
7 Title-7 2 Role-2
Microsoft SQL Server 2005 XML Showplan
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.539" Build="15.0.4053.23"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT&#xa; [EmployeeID]&#xa; ,[Title]&#xa; ,employee.[Role]&#xa; ,roles.Role AS RoleName&#xa;&#x9;FROM [dbo].Employee AS employee&#xa;&#x9;INNER JOIN [dbo].Roles AS roles ON roles.id = employee.Role&#xa;&#x9;WHERE [Status] &lt;&gt; &apos;D&apos;" StatementId="1" StatementCompId="5" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.00706876" StatementEstRows="3.5" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x3DF594AD70384BF4" QueryPlanHash="0xFA6CEA4178521683" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="150"><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="15" CompileCPU="15" CompileMemory="192"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="838808" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="2432512"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2022-01-04T09:02:27.36" ModificationCount="0" SamplingPercent="100" Statistics="[PK_Roles]" Table="[Roles]" Schema="[dbo]" Database="[fiddle_873f68a37750498183b6baea51fa9e29]"></StatisticsInfo><StatisticsInfo LastUpdate="2022-01-04T09:02:27.38" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000004_22AA2996]" Table="[Employee]" Schema="[dbo]" Database="[fiddle_873f68a37750498183b6baea51fa9e29]"></StatisticsInfo><StatisticsInfo LastUpdate="2022-01-04T09:02:27.36" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000003_22AA2996]" Table="[Employee]" Schema="[dbo]" Database="[fiddle_873f68a37750498183b6baea51fa9e29]"></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="Inner Join" EstimateRows="3.5" EstimateIO="0" EstimateCPU="1.672e-05" AvgRowSize="58" EstimatedTotalSubtreeCost="0.00706876" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_873f68a37750498183b6baea51fa9e29]" Schema="[dbo]" Table="[Employee]" Alias="[employee]" Column="EmployeeID"></ColumnReference><ColumnReference Database="[fiddle_873f68a37750498183b6baea51fa9e29]" Schema="[dbo]" Table="[Employee]" Alias="[employee]" Column="Title"></ColumnReference><ColumnReference Database="[fiddle_873f68a37750498183b6baea51fa9e29]" Schema="[dbo]" Table="[Employee]" Alias="[employee]" Column="Role"></ColumnReference><ColumnReference Database="[fiddle_873f68a37750498183b6baea51fa9e29]" Schema="[dbo]" Table="[Roles]" Alias="[roles]" Column="Role"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[fiddle_873f68a37750498183b6baea51fa9e29]" Schema="[dbo]" Table="[Employee]" Alias="[employee]" Column="Role"></ColumnReference></OuterReferences><RelOp NodeId="1" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="4" EstimatedRowsRead="8" EstimateIO="0.003125" EstimateCPU="0.0001658" AvgRowSize="46" EstimatedTotalSubtreeCost="0.0032908" TableCardinality="8" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_873f68a37750498183b6baea51fa9e29]" Schema="[dbo]" Table="[Employee]" Alias="[employee]" Column="EmployeeID"></ColumnReference><ColumnReference Database="[fiddle_873f68a37750498183b6baea51fa9e29]" Schema="[dbo]" Table="[Employee]" Alias="[employee]" Column="Title"></ColumnReference><ColumnReference Database="[fiddle_873f68a37750498183b6baea51fa9e29]" Schema="[dbo]" Table="[Employee]" Alias="[employee]" Column="Role"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="8" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_873f68a37750498183b6baea51fa9e29]" Schema="[dbo]" Table="[Employee]" Alias="[employee]" Column="EmployeeID"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_873f68a37750498183b6baea51fa9e29]" Schema="[dbo]" Table="[Employee]" Alias="[employee]" Column="Title"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_873f68a37750498183b6baea51fa9e29]" Schema="[dbo]" Table="[Employee]" Alias="[employee]" Column="Role"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_873f68a37750498183b6baea51fa9e29]" Schema="[dbo]" Table="[Employee]" Index="[PK_Employee]" Alias="[employee]" IndexKind="Clustered" Storage="RowStore"></Object><Predicate><ScalarOperator ScalarString="[fiddle_873f68a37750498183b6baea51fa9e29].[dbo].[Employee].[Status] as [employee].[Status]&lt;&gt;&apos;D&apos;"><Compare CompareOp="NE"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_873f68a37750498183b6baea51fa9e29]" Schema="[dbo]" Table="[Employee]" Alias="[employee]" Column="Status"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;D&apos;"></Const></ScalarOperator></Compare></ScalarOperator></Predicate></IndexScan></RelOp><RelOp NodeId="2" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="23" EstimatedTotalSubtreeCost="0.0037574" TableCardinality="5" Parallel="0" EstimateRebinds="2.62726" EstimateRewinds="0.372736" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_873f68a37750498183b6baea51fa9e29]" Schema="[dbo]" Table="[Roles]" Alias="[roles]" Column="Role"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="6" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="3" ActualEndOfScans="1" ActualExecutions="4"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_873f68a37750498183b6baea51fa9e29]" Schema="[dbo]" Table="[Roles]" Alias="[roles]" Column="Role"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_873f68a37750498183b6baea51fa9e29]" Schema="[dbo]" Table="[Roles]" Index="[PK_Roles]" Alias="[roles]" IndexKind="Clustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_873f68a37750498183b6baea51fa9e29]" Schema="[dbo]" Table="[Roles]" Alias="[roles]" Column="id"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[fiddle_873f68a37750498183b6baea51fa9e29].[dbo].[Employee].[Role] as [employee].[Role]"><Identifier><ColumnReference Database="[fiddle_873f68a37750498183b6baea51fa9e29]" Schema="[dbo]" Table="[Employee]" Alias="[employee]" Column="Role"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></NestedLoops></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>