How to improve Clustered Index scan to Clustered Index seek?-CodePudding

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

CodePudding user response：

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

CodePudding user response：

Provided you had an index on your Status column then that code should result in two index seeks. One for < 'D' and one for > 'D'. You just need an index on Status.

You can convert it to one seek using a temp table to flip your "Not" into an "is", which is always a good idea in query tuning:

CREATE TABLE #StatusMatch (StatusCode varchar(1) NOT NULL PRIMARY KEY CLUSTERED)
 INSERT INTO #StatusMatch WITH(TABLOCKX)
      SELECT Status 
        FROM dbo.Employee WITH(NOLOCK)
       WHERE Status <> 'D'
    GROUP BY Status
    ORDER BY Status;

      SELECT a.EmployeeID, a.Title, a.Role, b.Description AS RoleName
        FROM dbo.Employee a WITH(NOLOCK)
  INNER JOIN dbo.Roles b WITH(NOLOCK) ON a.Role = b.id
  INNER JOIN #StatusMatch c ON a.Status = c.StatusCode