SQL query for asp.net pagination

Question

I am using iBatis and SQLServer ,

What is the best way to use offset and restriction for pagging requests?

Perhaps I will add the ROW_NUMBER() OVER (ORDER BY Id) AS RowNum , but this will only prevent data access for simple queries. In some cases, I use union select. How to optimize these queries?

Answer 1

I don't know anything about ibatis, but I think you could do it in SQL.

If I understand you correctly, you want to paginate the results of a select or union expression from several select statements.

I would do it as follows. For example, it could be a stored procedure, and there probably should be some kind of sanity check, check that the offset and limit values ​​are greater than 0. If you are doing something like this, make sure you replace * with your column names also!

Here is a merge example:

 DECLARE @offset INT; DECLARE @limit INT; WITH cte AS (SELECT t.*, Row_number() OVER (ORDER BY Id) AS RowNum FROM (SELECT * FROM Table1 UNION SELECT * FROM Table2) t) SELECT * FROM cte WHERE RowNum BETWEEN @offset AND @offset + @limit 

Essentially, I concluded that a new table from the union of two queries, as you said, could happen in your case. Then I add the column with the row number to the result of this in the CTE , then select only the rows specified by @Offset and @limit + @offset to return only the rows you requested.

eg. By setting @offset = 50 and @limit = 50 , you will get 50-100 results (as indicated by the criteria specified in the Row_number over section.

(Hope this was what you were looking for!)

Edit: This will only work in SQL Server 2005 - you did not specify which version you are using!