I have two indexes on a table called Shopper
.
Clustered index:
CREATE CLUSTERED INDEX [CI_EMail_ShopperNumID]
ON [dbo].[Shopper] ([EMail] ASC, [ShopperNumID] ASC)
Non Clustered Index
CREATE NONCLUSTERED INDEX [nci_wi_Shopper_D8E9A1BB0660D0838F923BB8587C7115]
ON [dbo].[Shopper] ([EMail] ASC)
INCLUDE ([DateCreated], [FirstName], [LastLoginDate], [LastName],
[MaxEmailVolume], [ShopperNumID], [ShopperSourceCD], [ShopperSourceOther])
I run a very simple SELECT
:
SELECT ShopperNumID
FROM shopper
WHERE Email = '[email protected]'
On analyzing the Execution Plan, I notice that the non-clustered index is being used:
Now, I drop the non-clustered index:
DROP INDEX IF EXISTS [nci_wi_Shopper_D8E9A1BB0660D0838F923BB8587C7115]
ON [dbo].[Shopper]
GO
and re-run my select to notice that the clustered index is (finally) being used
Can someone please explain why the (bulky) non-clustered index is being used by the optimization engine, instead of the (preferred) clustered index?
Microsoft SQL Server 2016 (RTM-GDR) (KB3194716) - 13.0.1722.0 (X64)
Developer Edition (64-bit) on Windows 10 Pro 6.3 (Build 14393:)
UPDATE: Based on the inputs received, to evaluate this further, I created another non clustered index on the table, very similar to the already existing clustered index.
CREATE NONCLUSTERED INDEX [NCI_EMail_ShopperNumID]
ON [dbo].[Shopper] ([EMail] ASC, [ShopperNumID] ASC)
Currently, the table has 3 indexes that can support my SELECT
:
- CLUSTERED INDEX [CI_EMail_ShopperNumID]
- NONCLUSTERED INDEX [nci_wi_Shopper_D8E9A1BB0660D0838F923BB8587C7115]
- NONCLUSTERED INDEX [NCI_EMail_ShopperNumID]
Now, When I run the same SELECT
:
SELECT ShopperNumID
FROM shopper
WHERE Email = '[email protected]'
and analyze the Execution Plan, I notice that the newly created non-clustered index is being used:
Seems like the optimizer is adamant about using a Non Clustered Index, no matter what!
SET STATISTICS IO
show any particularly different output? – Nick.McDermaidTable 'Shopper'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SameSTATISTICS IO
output when either Index is used. Ideally, execution time is unimpacted irrespective of the index being used, because value is being seeked (not scanned for). – AeyJey