CODEWITHSUNDEEP
asp.netsql-server
Jackal
Jackal

Reputation: 3521

splitting selecting rows from a table based on page number of table ASP net SQL

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.

Upvotes: 0

Views: 38

Answers (1)

Sreenu131
Sreenu131

Reputation: 2516

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

Upvotes: 1

Related Questions