0

My schema:

schema of data

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

plan 1

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

plan 2

Please any ideas? Why are the indexes not used when selecting from this view?

SQL Server 2019 development

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Horev Ivan
  • 270
  • 3
  • 9
  • 3
    Try adding the hint `WITH ( NOEXPAND )` – SMor May 25 '21 at 11:13
  • It works! Thanks @SMor! simple and elegant solve! – Horev Ivan May 25 '21 at 11:16
  • 1
    Does this answer your question? [Why Query Optimizer totally ignores indexed view indexes?](https://stackoverflow.com/questions/22072375/why-query-optimizer-totally-ignores-indexed-view-indexes) See also [Paul White: Another Reason to Use NOEXPAND hints in Enterprise Edition](https://sqlperformance.com/2015/12/sql-performance/noexpand-hints) – Charlieface May 25 '21 at 15:14

0 Answers0