My schema:
I need to get count of comment for each tag.
I created a view:
create view dbo.UserTagCommentCount
with schemabinding
as
select
c.UserPK, t.PK TagPK, count_big(*) Count
from
dbo.Comments c
join
dbo.Posts p on p.PK = c.PostPK
join
dbo.PostTags pt on pt.PostPK = p.PK
join
dbo.Tags t on t.PK = pt.TagPK
group by
t.PK, c.UserPK
go
and I created clustered unique index on this view:
create unique clustered index PK_UserTagCommentCount
on dbo.UserTagCommentCount(UserPK, TagPK)
go
But when I select a rows by UserPK
- this clustered index is not being used:
select *
from UserTagCommentCount
where UserPK = 19146
order by Count desc
OK. I create a simple index
create index IX_UserTagCommentCount_UserPK
on UserTagCommentCount(UserPK)
go
and use select with it
select *
from UserTagCommentCount with(index(IX_UserTagCommentCount_UserPK))
where UserPK = 19146
order by Count desc
but I see the same plan
Please any ideas? Why are the indexes not used when selecting from this view?
SQL Server 2019 development