CODEWITHSUNDEEP
sqlsql-server
gabi
gabi

Reputation: 1376

where clause on non-indexed column in sql

I have an employee table where id is its primary key. It contains five hundred thousand or even more rows and there are only less than ten fired employees. What is the best way to look them up?

SELECT id, name, mode
  FROM employees
  WHERE mode   = 'fired'

table

CREATE TABLE [dbo].[employee](
    [id] [int] IDENTITY(27,1) NOT NULL,
    [name] [varchar](255) NULL,
    [mode] [varchar](45) NOT NULL)

Upvotes: 0

Views: 553

Answers (2)

Bruno Costa
Bruno Costa

Reputation: 2720

As sidux said, you should create an index on the mode column.

Adding more options to sidux answer, In my opinion, you should have another table with metadata information about the status of the employee and have a FK to that table as Status of the employee, replacing the mode column (id, name, statusId). This will allow you to create a smaller index and with that getting a better performance when filtering by status id (with index created)

Upvotes: 2

Dmitrij Kultasev
Dmitrij Kultasev

Reputation: 5787

You can create NON CLUSTERED conditional index.

 CREATE NON CLUSTERED INDEX ix_employees_firedEmployees ON employees (name) INCLUDE (mode) WHERE mode = 'fired';

But in practice better way to have flags or status to indicate such employees. For example status CHAR(1) = 'F', or IsFired BIT = 1

Upvotes: 1

Related Questions