High performance count with where clause and dynamic data I am looking for a high-performance approach to solve this problem.
I have a table on a SQL-Server 2019 with 316 columns and several million rows.
For this query, there are only a few of those columns that are relevant.  Something like a date-field and an amount-field (maybe the id-field as well).
Now I would like to know how many entries exist in a specific date range (from now, to X days in the past) that has an amount that is lower than Y.
My current approach (split the request into two requests):

Create a nonclustered index over the date-field &amp; id-field.

Do a first select with a where-clause to get only entries in the relevant date range. (Select only id) Will this avoid a full-table scan?

Do a second select (count) with a where-clause over the ids (and maybe the amount-field).  The id-field is also the primary key and clustered index.


At step three I am not really sure if I can put the amount-field into the where-clause without any performance loss.  If that is not possible, maybe I should select only the ids and the amount-field and do the count in my application (in memory)?  It would be great if it is possible to avoid this.
Also, I am not sure if it will be necessary to split the request at step three if I will have 1,000,000 Ids which will hit the condition from step two.
What do you think about my approach?  Should I go for it, or is there a better one?
Create a composite index and use only one SELECT statement. That index would either be on (date, amount) or (amount, date). Which one you choose depends on your query and the selectivity for each column.
Imagine an old-school (physical) phone book. You find something by last name, and keep searching for the first name you want (Doe, John).
Sure, columnstore indexex can be useful, but that might also be overkill.  There are specific performance concerns when you modify data frequently and there are no SEEKs in a columnstore index.