I was testing SQL Server indexes and found very strange behavior. Here is my code:
DROP TABLE IF EXISTS dbo._Test DROP TABLE IF EXISTS dbo._Newtest GO CREATE TABLE _Test( ID INT NOT NULL, UserSystemID INT NOT NULL, Age INT ) GO INSERT INTO dbo._Test ( ID, UserSystemID, Age ) SELECT TOP 10000000 ABS(CHECKSUM(NEWID())) % 5000000, ABS(CHECKSUM(NEWID())) % 2, ABS(CHECKSUM(NEWID())) % 100 FROM sys.all_columns CROSS JOIN sys.all_objects a CROSS JOIN sys.all_objects b CROSS JOIN sys.all_objects c ; WITH cte AS ( SELECT ID, UserSystemID, age, ROW_NUMBER() OVER(PARTITION BY ID, UserSystemID ORDER BY GETDATE()) rn FROM dbo._Test ) SELECT cte.ID , cte.UserSystemID , cte.Age INTO _newTest FROM cte WHERE cte.rn = 1 CREATE UNIQUE NONCLUSTERED INDEX IX_test ON dbo._NewTest(ID, UserSystemID) INCLUDE(age) GO ALTER TABLE dbo._NewTest ADD CONSTRAINT PK_NewTest PRIMARY KEY CLUSTERED(UserSystemID, ID) GO
At this point, I have two indexes on the same table and on the same columns. First one is nonclustered and the second one is clustered. The
Id column is more selective (about 5000000 unique values) and
UserSystemID is not (two unique values).
Then I run the following query to test which index is used:
SELECT id, UserSystemID, age FROM _NewTest WHERE id = 1502945 AND UserSystemID = 1
It seeks the clustered index. You can see the plan here.
The question is why SQL Server prefers the clustered index instead of the unique nonclustered index.
My leading column of clustered index is much less selective than the other unique nonclustered index. So I expect that the performance must be worse with clustered index but in practice it is not.