I have a
Records table with over 100 columns and very many rows, and a nonclustered index on 5 fields based on my access paths:
CREATE NONCLUSTERED INDEX [IX_Records_CustomerID] ON [dbo].[Records] ( [CustomerID] ASC, -- int [IsInvalid] ASC, -- int [IsProcessed] ASC, -- bit [IsRejected] ASC, -- bit [RecordName] ASC, -- varchar(12) ;
The 5 fields do not include the primary key
RecordID, which is the column in the clustered index.
Here is my poorly performing query:
SELECT * FROM Records WHERE CustomerID IN (181, 283, 505)
The execution plan shows that it performs a Clustered Index Scan, which I understand is because I'm selecting columns that are not included in the index. In Management Studio, I change the query to:
SELECT CustomerID, IsInvalid, IsProcessed, IsRejected, RecordName FROM Records WHERE CustomerID IN (181, 283, 505)
And the execution plan shows an Index Seek, and the query execution time drops from 44 seconds to 2 seconds. However, I lack the liberty in the application to replace the
* with only the columns I need and have included in my index.
Is there any way around the clustered index scan when I'm locked into