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](8) NOT NULL,
/* more columns here */
[VBTerminator] [varchar](1) NOT NULL
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_dbo_CustDataMaster_CustNum] ON dbo.CustDataMaster
(
[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]
I don't have a clustered index on either of those tables, only one nonclustered index.
Ignore the fact that the datatypes don't entirely match the type of data being stored. These fields are a backup from an IBM AS/400 DB2 database, and these are the compatible datatypes for it. (I have to be able to query this backup database with the exact same queries, and get the exact same results.)
This data is only used for SELECT
statements. I don't do any INSERT
/UPDATE
/DELETE
statements on it, except for when the backup application is copying data from the AS/400.