Sql-server – How does SQL Server know what index to use

nonclustered-indexsql server

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
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?

Best Answer

Answer originally left as a comment by i-one:

Your query references ProductId, VariantId, StoreId, ChainId and LocalInventory columns.

All of them except ChainId are declared in the non-clustered index definition.

ChainId is included in the non-clustered index implicitly as part of the clustered index key.

So, the non-clustered index is covering for this query.

The nonclustered index contains the clustered index keys because SQL Server must be able to locate the base table row from the index entry.

From the SQL Server Index Architecture and Design Guide:

If the table has a clustered index, the column or columns defined in the clustered index are automatically appended to the end of each nonclustered index on the table. This can produce a covered query without specifying the clustered index columns in the definition of the nonclustered index. For example, if a table has a clustered index on column C, a nonclustered index on columns B and A will have as its key values columns B, A, and C.

Related Question