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
 [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'" 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]<>'D'"><Compare CompareOp="NE"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_873f68a37750498183b6baea51fa9e29]" Schema="[dbo]" Table="[Employee]" Alias="[employee]" Column="Status"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="'D'"></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> |