COLUMNSTORE INDEX - can it help on my query if I add a filter? (MS SQL)

I am trying to optimize a query that uses a count(*) which cause a full index scan.

I have read about the columnstore indexes and think that can maybe help optimize the query.

However, I wonder if I should add a filter to make it even better?

The query I try to improve is this one:

SELECT status, count(*) num FROM HKT_TABLE WHERE oen_user=export_user GROUP BY status

The Index I was trying to create was this one:

CREATE NONCLUSTERED COLUMNSTORE INDEX [HKT_INDEX] ON [dbo].[HKT_TABLE]
(
    [export_user],
    [oen_user],
    [status]
)WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0)

However, creating that index, did not help on the query, it only made it more slow.

So my idea was to try this index with a filter:

CREATE NONCLUSTERED COLUMNSTORE INDEX [HKT_INDEX] ON [dbo].[HKT_TABLE]
(
    [export_user],
    [oen_user],
    [status]
) WHERE (oen_user=export_user)

But I am unable to create it, as the where clause is incorrect.

Any help on what I am missing or doing wrong? (I do not have the option to change the query, so can only create indexes or similar).

Thanks in advance.

Solution

Unfortunately, you cannot create a filtered index predicate which compares two columns. If you look at the grammar for it, you will see it only allows constants as the right side.

Instead you can create an indexed view, and then create a clustered columnstore index on that

CREATE VIEW dbo.HKT_filtered
WITH SCHEMABINDING
AS
SELECT
    hkt.SomePrimaryKey,
    hkt.export_user,
    hkt.oen_user,
    hkt.status
FROM dbo.HKT_TABLE hkt
WHERE hkt.oen_user = hkt.export_user;

CREATE CLUSTERED INDEX CX ON dbo.HKT_filtered
    (SomePrimaryKey);

CREATE NONCLUSTERED COLUMNSTORE INDEX HKT_INDEX ON dbo.HKT_filtered
    (export_user, oen_user, status);

On Enterprise Edition the compiler will automatically use it even with your existing query. However, even on this edition, it is better to query the view directly and use the NOEXPAND hint.