MS SQL seems missing an pageable functions allows you to query data in a range, for sample if you wanna query 10 data between index of row from 25 to 35 theres no way you can setting a start row and end row just like Mysql.
Fortunately MS SQL geving some tricky way do the same effect by using ROW_NUMBER() function in SQL Server 2005 and later version.
-- First index of row in the page you go.
DECLARE @startRowIndex INT
-- How many rows in a page
-- How many rows in a page
DECLARE @maximumRows INT
-- If you goto page 1 and each page have 10 rows, the index row of page 1 is 1, the index row of page 2 is 21 and so forth.
-- If you goto page 1 and each page have 10 rows, the index row of page 1 is 1, the index row of page 2 is 21 and so forth.
SET @startRowIndex = 1
SET @maximumRows = 10
SET @maximumRows = 10
-- This SQL statement returns 10 of rows data between the rows from @startRowIndex to (@startRowIndex + @maximumRows)-1
SELECT *
FROM
(SELECT *,
ROW_NUMBER() OVER(ORDER BY OrderDate DESC) as RowNum
FROM Northwind.dbo.Orders
) as DerivedTableName
WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1
FROM
(SELECT *,
ROW_NUMBER() OVER(ORDER BY OrderDate DESC) as RowNum
FROM Northwind.dbo.Orders
) as DerivedTableName
WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1
related link:
