SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE [dbo].[Employee](
	[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
	[Title] [varchar](50) NULL,
	[Role] [int] NULL,
	[Status] [varchar](1) NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
(
	[EmployeeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO Employee VALUES
('Title-1',   1, 'D'),
('Title-2',   2, 'D'),
('Title-3',   3, 'A'),
('Title-4',   NULL, 'A'),
('Title-5',   3, NULL),
('Title-6',   3, 'B'),
('Title-7',   2, 'C'),
('Title-8',   1, NULL);
8 rows affected

CREATE TABLE [dbo].[Roles](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[Role] [varchar](25) NULL,
	[Description] [varchar](25) NULL,
 CONSTRAINT [PK_Roles] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO Roles VALUES
('Role-1',   'Description - 1'),
('Role-2',   'Description - 2'),
('Role-3',   'Description - 3'),
('Role-4',   NULL),
(NULL,   'Description - 5');
5 rows affected

ALTER TABLE dbo.Employee ADD CONSTRAINT FK_EmployeeRole FOREIGN KEY (Role) REFERENCES dbo.Roles (id);
CREATE INDEX IX_EmployeeStatus ON dbo.Employee (Status) INCLUDE (Title, Role);
CREATE PROCEDURE [dbo].[GetEmployeeList] 
AS
BEGIN
	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'

END
SELECT
       [EmployeeID]
      ,[Title]
      ,employee.[Role]
      ,roles.Role AS RoleName
      ,[Status]
	FROM [dbo].Employee AS employee
	INNER JOIN [dbo].Roles AS roles ON roles.id = employee.Role


EmployeeID
Title
Role
RoleName
Status


5
Title-5
3
Role-3
null

8
Title-8
1
Role-1
null

3
Title-3
3
Role-3
A

6
Title-6
3
Role-3
B

7
Title-7
2
Role-2
C

1
Title-1
1
Role-1
D

2
Title-2
2
Role-2
D


set statistics profile on;
EXEC [dbo].[GetEmployeeList];
set statistics profile off;


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.00706052
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.00706052
[employee].[EmployeeID], [employee].[Title], [employee].[Role], [roles].[Role]
null
PLAN_ROW
False
1

4
1
       |--Index Seek(OBJECT:([fiddle_b6b0d917a8ca4bd8943715ca5d286c53].[dbo].[Employee].[IX_EmployeeStatus] AS [employee]), SEEK:([employee].[Status] < 'D' OR [employee].[Status] > 'D') ORDERED FORWARD)
1
3
2
Index Seek
Index Seek
OBJECT:([fiddle_b6b0d917a8ca4bd8943715ca5d286c53].[dbo].[Employee].[IX_EmployeeStatus] AS [employee]), SEEK:([employee].[Status] < 'D' OR [employee].[Status] > 'D') ORDERED FORWARD
[employee].[EmployeeID], [employee].[Title], [employee].[Role]
4
0.003125
0.0001614
44
0.0032864
[employee].[EmployeeID], [employee].[Title], [employee].[Role]
null
PLAN_ROW
False
1

3
4
       |--Clustered Index Seek(OBJECT:([fiddle_b6b0d917a8ca4bd8943715ca5d286c53].[dbo].[Roles].[PK_Roles] AS [roles]), SEEK:([roles].[id]=[fiddle_b6b0d917a8ca4bd8943715ca5d286c53].[dbo].[Employee].[Role] as [employee].[Role]) ORDERED FORWARD)
1
4
2
Clustered Index Seek
Clustered Index Seek
OBJECT:([fiddle_b6b0d917a8ca4bd8943715ca5d286c53].[dbo].[Roles].[PK_Roles] AS [roles]), SEEK:([roles].[id]=[fiddle_b6b0d917a8ca4bd8943715ca5d286c53].[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


set statistics xml on;
EXEC [dbo].[GetEmployeeList];
set statistics xml off;


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.00706052" StatementEstRows="3.5" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x3DF594AD70384BF4" QueryPlanHash="0xE6BEA9C5955B27ED" 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="32" CompileTime="0" CompileCPU="0" CompileMemory="192"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="838808" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="2209424"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2022-01-04T11:50:18.07" ModificationCount="0" SamplingPercent="100" Statistics="[PK_Roles]" Table="[Roles]" Schema="[dbo]" Database="[fiddle_b6b0d917a8ca4bd8943715ca5d286c53]"></StatisticsInfo><StatisticsInfo LastUpdate="2022-01-04T11:50:18.08" ModificationCount="0" SamplingPercent="100" Statistics="[IX_EmployeeStatus]" Table="[Employee]" Schema="[dbo]" Database="[fiddle_b6b0d917a8ca4bd8943715ca5d286c53]"></StatisticsInfo><StatisticsInfo LastUpdate="2022-01-04T11:50:18.07" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000003_22AA2996]" Table="[Employee]" Schema="[dbo]" Database="[fiddle_b6b0d917a8ca4bd8943715ca5d286c53]"></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.00706052" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_b6b0d917a8ca4bd8943715ca5d286c53]" Schema="[dbo]" Table="[Employee]" Alias="[employee]" Column="EmployeeID"></ColumnReference><ColumnReference Database="[fiddle_b6b0d917a8ca4bd8943715ca5d286c53]" Schema="[dbo]" Table="[Employee]" Alias="[employee]" Column="Title"></ColumnReference><ColumnReference Database="[fiddle_b6b0d917a8ca4bd8943715ca5d286c53]" Schema="[dbo]" Table="[Employee]" Alias="[employee]" Column="Role"></ColumnReference><ColumnReference Database="[fiddle_b6b0d917a8ca4bd8943715ca5d286c53]" 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_b6b0d917a8ca4bd8943715ca5d286c53]" Schema="[dbo]" Table="[Employee]" Alias="[employee]" Column="Role"></ColumnReference></OuterReferences><RelOp NodeId="1" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="4" EstimatedRowsRead="4" EstimateIO="0.003125" EstimateCPU="0.0001614" AvgRowSize="44" EstimatedTotalSubtreeCost="0.0032864" TableCardinality="8" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_b6b0d917a8ca4bd8943715ca5d286c53]" Schema="[dbo]" Table="[Employee]" Alias="[employee]" Column="EmployeeID"></ColumnReference><ColumnReference Database="[fiddle_b6b0d917a8ca4bd8943715ca5d286c53]" Schema="[dbo]" Table="[Employee]" Alias="[employee]" Column="Title"></ColumnReference><ColumnReference Database="[fiddle_b6b0d917a8ca4bd8943715ca5d286c53]" 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="2" ActualLogicalReads="4" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="4" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_b6b0d917a8ca4bd8943715ca5d286c53]" Schema="[dbo]" Table="[Employee]" Alias="[employee]" Column="EmployeeID"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b6b0d917a8ca4bd8943715ca5d286c53]" Schema="[dbo]" Table="[Employee]" Alias="[employee]" Column="Title"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b6b0d917a8ca4bd8943715ca5d286c53]" Schema="[dbo]" Table="[Employee]" Alias="[employee]" Column="Role"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_b6b0d917a8ca4bd8943715ca5d286c53]" Schema="[dbo]" Table="[Employee]" Index="[IX_EmployeeStatus]" Alias="[employee]" IndexKind="NonClustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><EndRange ScanType="LT"><RangeColumns><ColumnReference Database="[fiddle_b6b0d917a8ca4bd8943715ca5d286c53]" Schema="[dbo]" Table="[Employee]" Alias="[employee]" Column="Status"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="&apos;D&apos;"><Const ConstValue="&apos;D&apos;"></Const></ScalarOperator></RangeExpressions></EndRange></SeekKeys></SeekPredicateNew><SeekPredicateNew><SeekKeys><StartRange ScanType="GT"><RangeColumns><ColumnReference Database="[fiddle_b6b0d917a8ca4bd8943715ca5d286c53]" Schema="[dbo]" Table="[Employee]" Alias="[employee]" Column="Status"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="&apos;D&apos;"><Const ConstValue="&apos;D&apos;"></Const></ScalarOperator></RangeExpressions></StartRange></SeekKeys></SeekPredicateNew></SeekPredicates></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_b6b0d917a8ca4bd8943715ca5d286c53]" 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_b6b0d917a8ca4bd8943715ca5d286c53]" Schema="[dbo]" Table="[Roles]" Alias="[roles]" Column="Role"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_b6b0d917a8ca4bd8943715ca5d286c53]" Schema="[dbo]" Table="[Roles]" Index="[PK_Roles]" Alias="[roles]" IndexKind="Clustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_b6b0d917a8ca4bd8943715ca5d286c53]" Schema="[dbo]" Table="[Roles]" Alias="[roles]" Column="id"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[fiddle_b6b0d917a8ca4bd8943715ca5d286c53].[dbo].[Employee].[Role] as [employee].[Role]"><Identifier><ColumnReference Database="[fiddle_b6b0d917a8ca4bd8943715ca5d286c53]" 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>

EmployeeID	Title	Role	RoleName	Status
5	Title-5	3	Role-3	null
8	Title-8	1	Role-1	null
3	Title-3	3	Role-3	A
6	Title-6	3	Role-3	B
7	Title-7	2	Role-2	C
1	Title-1	1	Role-1	D
2	Title-2	2	Role-2	D

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.00706052	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.00706052	[employee].[EmployeeID], [employee].[Title], [employee].[Role], [roles].[Role]	null	PLAN_ROW	False	1
4	1	\|--Index Seek(OBJECT:([fiddle_b6b0d917a8ca4bd8943715ca5d286c53].[dbo].[Employee].[IX_EmployeeStatus] AS [employee]), SEEK:([employee].[Status] < 'D' OR [employee].[Status] > 'D') ORDERED FORWARD)	1	3	2	Index Seek	Index Seek	OBJECT:([fiddle_b6b0d917a8ca4bd8943715ca5d286c53].[dbo].[Employee].[IX_EmployeeStatus] AS [employee]), SEEK:([employee].[Status] < 'D' OR [employee].[Status] > 'D') ORDERED FORWARD	[employee].[EmployeeID], [employee].[Title], [employee].[Role]	4	0.003125	0.0001614	44	0.0032864	[employee].[EmployeeID], [employee].[Title], [employee].[Role]	null	PLAN_ROW	False	1
3	4	\|--Clustered Index Seek(OBJECT:([fiddle_b6b0d917a8ca4bd8943715ca5d286c53].[dbo].[Roles].[PK_Roles] AS [roles]), SEEK:([roles].[id]=[fiddle_b6b0d917a8ca4bd8943715ca5d286c53].[dbo].[Employee].[Role] as [employee].[Role]) ORDERED FORWARD)	1	4	2	Clustered Index Seek	Clustered Index Seek	OBJECT:([fiddle_b6b0d917a8ca4bd8943715ca5d286c53].[dbo].[Roles].[PK_Roles] AS [roles]), SEEK:([roles].[id]=[fiddle_b6b0d917a8ca4bd8943715ca5d286c53].[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