I have a table (with 170M rows) that looks as follows:
CREATE TABLE [dbo].[Panel] ( [SubId] [varchar](15) NOT NULL, [LineageId] [int] NULL, [Buck] [varchar](20) NULL, [Lot] [varchar](20) NULL, [GlassType] [varchar](20) NULL, [ETA] [varchar](200) NULL, CONSTRAINT [PK_Panel] PRIMARY KEY CLUSTERED ( [SubId] ASC )
99% of the queries against this table reference SubId either in the Where clause or join. One of our DBAs told me he could make all those queries and joins perform better by creating the following index:
CREATE UNIQUE NONCLUSTERED INDEX [IX-Panel-SubID-I-LineageID] ON [dbo].[Panel] ( [SubId] ASC ) INCLUDE ([LineageId])
When he told me this, I thought he was nuts. But I just checked index usage since this index was created and found the following:
PK_Panel (232,394 seeks / 2,133 scans) IX-Panel-SubID-I-LineageID (25,528 seeks / 3644 scans)
I was a bit shocked to see this. Under what circumstances would this new index every get used? Why would SQL Server ever select it?
Or maybe a better question would be, why would SQL Server select the new index to do a seek, instead of the clustered index? Approximately 25K times, it thought seeking on the new index was a better choice.
In case this helps, the LineageId essentially indicates where a panel was created and there are ~35 distinct values it could contain.