0

I thought how to get more performance to show data from a table with thousands of rows and that i could split a select in parts.

For example, I have a Repeater in ASP NET and only shows 10 rows at the time. I want to select only 10 rows from the table, on next page it selects the next 10 rows and so on.

the problem is that I can't find anything to give me a head on on this problem and I was hoping someone with knowledge of this could refer me to some good start ups, thank you.

Jackal
  • 3,359
  • 4
  • 33
  • 78
  • 2
    Possible duplicate of [Implement paging (skip / take) functionality with this query](https://stackoverflow.com/questions/13220743/implement-paging-skip-take-functionality-with-this-query) – PSK Jan 24 '19 at 13:03
  • 1
    Check how OFFSET and FETCH work. – PSK Jan 24 '19 at 13:04

1 Answers1

1

Try this Sample Sql script first it select only 10 rows from the table, on next page it selects the next 10 rows and so on.

DECLARE @i_PageIndex INT=1,-- change page index 1 and 2 .. you we get the exact difference
        @i_PageSize INT=10

SELECT COUNT(1) OVER() AS recordCnt,
       ROW_NUMBER()OVER(ORDER BY TABLE_NAME) AS Seq,
       * 
FROM INFORMATION_SCHEMA.COLUMNS
ORDER BY ROW_NUMBER()OVER(ORDER BY TABLE_NAME) 
OFFSET(COALESCE(@i_PageIndex, 1) - 1) * @i_PageSize ROWS FETCH NEXT @i_PageSize ROWS ONLY
Sreenu131
  • 2,476
  • 1
  • 7
  • 18