I created a table by copying 19972 rows (ID,FirstName,LastName) from Adventureworks.person.person table. On this table I created a non-clustered index on Firstname Column. Using this table I created an Indexed view with ID and FirstName column with unique Clustered index on ID. When I query ID column from this view I get a TABLE SCAN. I was expecting to get a Clustered index seek. Can anyone explain why this is happening? Thank you.
Create View vIndexedView
with Schemabinding, Encryption
as
Select ID, FirstName
From dbo.RIDLookupTBL
where ID <=200
Create unique clustered index UCI_vIV_ID on vIndexedView(ID)
Select ID
From vIndexedView
Where ID = 20
