Is it possible to optimize a SELECT COUNT(*) query using a filtered index as a hint to achieve constant speed?

Question

I'd like to count all the Orders that are not urgent and whose order status = 1 (shipped).

This should be a very simple query to optimize. I'd like to put a simple filtered index on the Orders table to cover this query to make it a constant time/O(1) operation. However, when I look at the query plan, it looks like it's using a Index Scan which doesn't make sense. Ideally, this query should just returning the number of items in the index.

The table look like this (simplified to get to the essence):

CREATE TABLE [dbo].[Orders](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [IsUrgent] [bit] NOT NULL,
    [Status] [tinyint] NOT NULL
 CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED ( [Id] ASC )

I've created this filtered index:

CREATE INDEX IX_Orders_ShippedNonUrgent ON Orders(Id) WHERE IsUrgent = 0 AND Status = 1;

Now, when I do this query:

SELECT COUNT(*) FROM Orders WHERE IsUrgent = 0 AND Status = 1

I see that the query plan is using IX_Orders_ShippedNonUrgent, but it's doing an Index Scan and performing around 200 reads across the ~150,000 rows in Orders.

Is it possible to always have this query run in constant time assuming the filtered index is kept up to date? Ideally, it should only perform 1 read to get the size of the index.

If I switch to a non-filtered index like this:

CREATE INDEX IX_Orders_IsUrgentStatus ON Orders(IsUrgent, Status);

The query plan uses an Index Seek, but still performs many more reads than should be necessary to answer this simple query.

UPDATE

I'm able to do this

SELECT TOP 1 rows FROM sys.partitions p
INNER JOIN sys.indexes i
ON i.name = 'IX_Orders_ShippedNonUrgent'
AND i.object_id = p.object_id
AND i.index_id = p.index_id

and get the result in 9 reads but it seems like there should be a much easier and less brittle way of using the simple COUNT(*) query.

Answer 1

It seems like what I'm wanting isn't possible. The best answer was left in the comments by Nikola Markovinović which is to forget about the filtered index and use an indexed view instead:

CREATE VIEW [dbo].vw_Orders_TotalShippedNonUrgent WITH SCHEMABINDING 
AS 
SELECT COUNT_BIG(*) AS TotalOrders 
  FROM dbo.Orders WHERE IsUrgent = 0 AND Status = 1;

with

CREATE UNIQUE CLUSTERED INDEX IX_vw_Orders_TotalShippedNonUrgent ON vw_Orders_TotalShippedNonUrgent(TotalOrders);

This forces creating views and their index for each summary statistic that I want as well as rewriting the query to ask the view instead of the simple approach, but it is fast at only 2 reads.

I'll leave this question open for awhile in case anyone has a simpler approach that's just as fast.