Why would SQL Server ignore an index?

I have a table, CustPassMaster with 16 columns in it, one of which is CustNum varchar(8), and I created an index IX_dbo_CustPassMaster_CustNum. When I run my SELECT statement:

SELECT * FROM dbo.CustPassMaster WHERE CustNum = '12345678'

It ignores the index completely. This confuses me as I have another table CustDataMaster with way more columns (55), one of which is CustNum varchar(8). I created an index on this column (IX_dbo_CustDataMaster_CustNum) in this table, and use practically the same query:

SELECT * FROM dbo.CustDataMaster WHERE CustNum = '12345678'

And it uses the index I created.

Is there any specific reasoning behind this? Why would it use the index from CustDataMaster, but not the one from CustPassMaster? Is it due to the low column count?

The first query returns 66 rows. For the second, 1 row is returned.

Also, additional note: CustPassMaster has 4991 records, and CustDataMaster has 5376 records. Could this be the reasoning behind ignoring the index? CustPassMaster also has duplicate records that have the same CustNum values as well. Is this another factor?

I am basing this claim on the actual execution plan results of both queries.

Here is the DDL for CustPassMaster (the one with the unused index):

CREATE TABLE dbo.CustPassMaster(
    [CustNum] [varchar](8) NOT NULL,
    [Username] [char](15) NOT NULL,
    [Password] [char](15) NOT NULL,
    /* more columns here */
    [VBTerminator] [varchar](1) NOT NULL
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_dbo_CustPassMaster_CustNum] ON dbo.CustPassMaster
(
    [CustNum] ASC
) WITH (PAD_INDEX = OFF
    , STATISTICS_NORECOMPUTE = OFF
    , SORT_IN_TEMPDB = OFF
    , DROP_EXISTING = OFF
    , ONLINE = OFF
    , ALLOW_ROW_LOCKS = ON
    , ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

And the DDL for CustDataMaster (I've omitted a lot of irrelevant fields):

```
CREATE TABLE dbo.CustDataMaster(
[CustNum] varchar NOT NULL,
/* more columns here

Typically indexes will be used by SQL Server if it deems it more expedient to use the index than to directly use the underlying table.

It would seem likely the cost-based optimizer thinks it would be more expensive to actually use the index in question. You may see it use the index if instead of doing SELECT *, you simply SELECT T1Col1.

When you SELECT * you are telling SQL Server to return all columns in the table. To return those columns SQL Server must read the pages for the rows that match the WHERE statement criteria from the table itself (clustered index or heap). SQL Server is probably thinking the amount of reads required to get the rest of the columns from the table means it might as well scan the table directly. It would be useful to see the actual query and the actual execution plan used by the query.

StackExchange Database Administrators Q#124785, answer score: 18