I have this non clustered index
CREATE NONCLUSTERED INDEX [nci_wi_StoreVariantInventory_F09B4BE817BC953FAC6861B7E46F6F51] ON [dbo].[StoreVariantInventory]
(
[ProductId] ASC,
[StoreId] ASC,
[VariantId] ASC
)
INCLUDE ( [LocalInventory]) WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
GO
And this clustered index
ALTER TABLE [dbo].[StoreVariantInventory] ADD CONSTRAINT [PK_StoreVariantInventory] PRIMARY KEY CLUSTERED
(
[ProductId] ASC,
[ChainId] ASC,
[CountryCode] ASC,
[StoreId] ASC,
[VariantId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO
Generated for this table
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[StoreVariantInventory](
[ProductId] [bigint] NOT NULL,
[ChainId] [nvarchar](5) NOT NULL,
[CountryCode] [nvarchar](2) NOT NULL,
[StoreId] [bigint] NOT NULL,
[VariantId] [int] NOT NULL,
[Size] [int] NOT NULL,
[SizeRow] [nvarchar](1) NOT NULL,
[KoncernNumber] [nvarchar](4) NOT NULL,
[LocalInventory] [int] NOT NULL,
[OrderedInventory] [int] NOT NULL,
[Price] [money] NOT NULL,
[LastUpdated] [datetime] NOT NULL,
CONSTRAINT [PK_StoreVariantInventory] PRIMARY KEY CLUSTERED
(
[ProductId] ASC,
[ChainId] ASC,
[CountryCode] ASC,
[StoreId] ASC,
[VariantId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[StoreVariantInventory] WITH CHECK ADD CONSTRAINT [FK_StoreVariantInventory_Stores] FOREIGN KEY([StoreId])
REFERENCES [dbo].[Stores] ([StoreId])
GO
ALTER TABLE [dbo].[StoreVariantInventory] CHECK CONSTRAINT [FK_StoreVariantInventory_Stores]
GO
ALTER TABLE [dbo].[StoreVariantInventory] WITH CHECK ADD CONSTRAINT [FK_StoreVariantInventory_Variants] FOREIGN KEY([ProductId], [VariantId])
REFERENCES [dbo].[Variants] ([ProductId], [VariantId])
GO
ALTER TABLE [dbo].[StoreVariantInventory] CHECK CONSTRAINT [FK_StoreVariantInventory_Variants]
GO
If I do a simple select
select LocalInventory
from StoreVariantInventory
where ProductId=1219100110
and VariantId = 49
and StoreId = 9050
and ChainId = 'F'
It looks through the non clustered index
But why does it pick the non clustered index? I am doing a WHERE that doesn't fit the non clustered index 100% so how can it use it? Does SQL Server just picks what it seems a best fit, when all columns in the WHERE clause doesn't fit an index 100%? Is it bad practice not having an index like here not fitting 100% with my where clause?