Optimistic Paging and separate query for COUNT?
Assuming you need an exact count and not an approximation, and you want this to go fast without index scanning (yes, please!), a good place to start would be to create an indexed view that maintains the count of rows:

CREATE VIEW dbo.MyView
    WITH SCHEMABINDING
AS
    SELECT
        COUNT_BIG(*) AS NumberOfRows
        FROM [dbo].[Products];
GO

CREATE UNIQUE CLUSTERED INDEX IX_MyView
    ON dbo.MyView(NumberOfRows);


(Note: an ID column can't be added to this view and then the clustered index created on it.)

Yes, this will add a small amount of overhead to writes to the base table; it doesn't sound like that's going to be a problem in this case.

Then it's really easy and efficient to read a transactionally-consistent row count:

SELECT NumberOfRows FROM dbo.MyView WITH(NOEXPAND);


which can either be included in the same query, or separately. Personally, I'd try to keep it separate and return the value as an OUTPUT parameter. In either case, for an exact count, I'd strongly recommend using snapshot isolation to avoid out-of-sync issues.

This type of structure could also be created with a user table and a trigger, or a "business logic" approach... but because of the number of moving parts in either of those solutions, I really wouldn't recommend them unless there's no other choice.



If you do go this route, be sure to patch SQL Server to at least:


Cumulative Update 7 for SQL Server 2012 SP2
Cumulative Update 1 for SQL Server 2014 Service Pack 1
Cumulative Update 8 for SQL Server 2014 RTM


See the Knowledge Base article:

Incorrect results are returned in an indexed view after an Insert or Delete operation on the base table occurs in SQL Server 2014

For more details and background, see An Indexed View Bug with Scalar Aggregates by Paul White.