SQL query for asp.net grid pagination

Question

I am using iBatis and SQLServer,

What is the best way for using offset and limit for pagging queries?

Maybe I add the column ROW_NUMBER() OVER (ORDER BY Id) AS RowNum, but this will only prevent data access for simple queries. There is some cases that I use union of selects. How to optmize these queries?

Answer 1

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

If I understand you correctly, you want to get paginate the results of a select statement or union of a few select statements.

I'd do it the following way. This could be a stored procedure for example, and there should probably be some sanity checking in there the check the values of offset and limit are greater than 0. If you do end up doing something like this, make sure you replace * with your column names too!

Here is an example with a union:

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 what I've done is derived a new table from the union of two queries, as you said could happen in your case. I'm then adding a column with the row number to the result of that in a CTE, then only selecting the rows specified in @Offset and @limit + @offset to get back only the rows you've asked for.

E.g. Setting @offset = 50 and @limit = 50, you'd get back results 50-100 (as ordered by the criteria specified in the Row_number over clause.

(I hope this was the sort of thing you were looking for!)

Edit: This will only work in SQL Server 2005 onwards - you haven't mentioned which version you're using!