Optimistic Paging and separate query for COUNT? I am using SQL Server 2012 FETCH/OFFSET after searching a lot that this will give me the best performance. But for paging to work, I need a total count. 

There are 2 ways to achieve this:


Paging query can include COUNT(*) OVER() column 
Separate query


Which one will be best? Here is my SQL:

ALTER PROCEDURE [dbo].[GetAllProducts] (@PageNumber int,
@PageCount int)
AS
BEGIN
    SELECT
        Products.[ID] AS [ID1],
        Products.[Name] AS [Name],
        Products.[Price] AS [Price],
        Products.[NewPrice] AS [NewPrice],
        Products.[ShortDescription] AS [ShortDescription],
        Products.[SKU] AS [SKU],
        Products.[ProductTypeID] AS [ProductTypeID],
        Products.[ImageID] AS [ImageID],
        Products.[Promotion] AS [Promotion],
        Products.[ParentID] AS [ParentID],
        Products.[Attributes] AS [Attributes],
        ROW_NUMBER() OVER (ORDER BY Products.[ID] ASC) AS [row_number]
    FROM [dbo].[Products] AS Products
    INNER JOIN [dbo].[Images] AS Images
        ON Products.[ImageID] = Images.[ID]
    ORDER BY Products.[ID] ASC
    OFFSET ((@PageNumber - 1) * @PageCount) ROWS
    FETCH NEXT @PageCount ROWS ONLY;
END


The Products table has more than 50,000 rows, sometimes static, sometimes changing a lot.
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.