0

For implementing a custom paging solution using C#, ADO.Net, with SQL Server as database, let us assume following details:

Total Records = 1000
Page Size = 100

This would mean there are 10 pages, I use a query as follows:

Min and Max Id (Primary Key) are fetched using scalar Queries:

Select Min(Id), Max(Id) from Table;

Select * from Table where Id >= (Min Record) and Id < (Max Record)

Here for every transaction, Min and Max Record Value is incremented by Page Size, till the point Max Record reach or exceed the Actual Max value.

This solution works fine for an auto increment / identity column, which have continuous values, but let's assume the column used has gap in values, though they can still be ordered, like 1000 values are between Min = 1 and Max = 3000.

  • I have following possible work around, one suggested in following SO link, where an extra RowId column is used to achieve the same.

  • Other options would be opening a Reader and read a record for given Page, create a List and thus fetch a Page

What I ideally want is executing a Top <PageSize> sql query, where it is executed from a pre-defined row or value that I suggest, instead of beginning, thus I would avoid creating an extra column, I can work with disconnected architecture, no Reader is required.

Any suggestion or pointer, please let me know if a clarification is required, for any detail in the question

Community
  • 1
  • 1
Mrinal Kamboj
  • 11,300
  • 5
  • 40
  • 74
  • That's there in the SO link posted in the question, but I want to avoid the extra column creation. http://stackoverflow.com/questions/758186/how-to-get-n-rows-starting-from-row-m-from-sorted-table-in-t-sql – Mrinal Kamboj Oct 26 '15 at 08:28
  • You can use this solution but add an extra `SELECT` on top to exclude the new column if it is that much undesirable. – Aka Guymelef Oct 26 '15 at 08:32
  • @AkaGuymelef as the link above suggest do I also need an inner join between derived and parent table, does that help performance – Mrinal Kamboj Oct 26 '15 at 08:37

2 Answers2

1

You can use the ROW_NUMBER function in a common table expression(CTE):

WITH CTE AS
(
   SELECT 
    t.*, 
    RN = ROW_NUMBER() OVER (ORDER BY t.ID)
   FROM dbo.TableName t
)
SELECT ID, Col2, Col3, ...
FROM CTE 
WHERE RN >= (@pageIndedx * @pageSize) AND RN <= (@pageIndedx * @pageSize) + @pageSize
Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • Aware of this solution, it is there in the SO link posted, trying to figure out a way to avoid an extra column added as part of CTE – Mrinal Kamboj Oct 26 '15 at 08:35
  • @MrinalKamboj: you don't need to select all columns including the row-number column, just replace `SELECT *` with `SELECT allcolumnsfromtable`. – Tim Schmelter Oct 26 '15 at 08:36
  • But wouldn't the CTE which is a run time feature have a performance impact for a table which have millions of records, it is getting executed at run time – Mrinal Kamboj Oct 26 '15 at 08:38
  • @MrinalKamboj: what means "it is a runtime feauture", it's similar to a view/subquery in the database. There's no performance impact. It will use indexes as every sql query. If you self-join a cte with itself you might have a performance impact but not in this case. – Tim Schmelter Oct 26 '15 at 08:41
  • Got it thanks, let me see if someone can help with a solution without using Row_Number() – Mrinal Kamboj Oct 26 '15 at 08:53
  • @MrinalKamboj: note that i've edited my answer to provide a query where you have to provide two parameters, the current pageindex and the pagesize. – Tim Schmelter Oct 26 '15 at 08:59
1

If by any chance you are using Sql Server 2012 onwards, you can use

Fetch and Offset

e.g.

-- Variable to hold the offset value
Declare @RowSkip As int
-- Variable to hold the fetch value
Declare @RowFetch As int

--Set the value of rows to skip
 Set @RowSkip = 20000
--Set the value of rows to fetch
 Set @RowFetch = 50000

Select *
From dbo.tblSample 
Order by (Select 1)  
Offset @RowSkip Row 
Fetch  Next @RowFetch Rows Only;

Please refer : Usage 1: Server Side Paging for more details

RNA Team
  • 269
  • 1
  • 6