How to improve Clustered Index scan to Clustered Index seek?

I have two tables

[dbo].[Employee](
    [EmployeeID] [int] IDENTITY(1,1) NOT NULL,
    [Title] [varchar](50) NULL,
    [Role] [int] NULL,
    [Status] [varchar](1) NULL)

[dbo].[Roles](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [Role] [varchar](25) NULL,
    [Description] [varchar](25) NULL)

with primary clustered keys by id. Also I have stored procedure

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'

The Execution plan shows me a ‘Clustered Index Scan’ that I want to avoid. Is there any way I can convert it to a ‘Clustered Index Seek’? Execution Plan screen

<db fiddle>

Answer

As I mentioned in the comments, the CLUSTERED INDEX on dbo.Employees isn’t going to help you here. The reason for this is because you are filtering on the column status, which is simply included in the CLUSTERED INDEX, but isn’t ordered on it. As a result, the RDBMS has no option but to scan the entire table to filter out the rows where Status has a value of D.

You could add an INDEX on the column Status and INCLUDE the other columns, which may result in an index seek (not a clustered index seek), however, due to you using <> D then the RDBMS may feel still chose to perform a scan; whether it does depends on your data’s distribution:

CREATE INDEX IX_EmployeeStatus ON dbo.Employee (Status) INCLUDE (Title, Role);

Also add a FOREIGN KEY to your table:

ALTER TABLE dbo.Employee ADD CONSTRAINT FK_EmployeeRole FOREIGN KEY (Role) REFERENCES dbo.Roles (id);

db<>fiddle