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.