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
- 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.