How to make LIMIT (from, to) in MSSQL 2005

Sample ImageLots of developers complain, that Microsoft SQL hasn't something similar to LIMIT(from, to) as MySQL has. In past time there was a solution by SELECT TOP n and inserted SELECT TOP n- With new T-SQL commands in Miscrosoft SQL Server 2005  is situation simpler.

You can use this code, define @start as limit value From and @rowsperpage as number of records you need.

DECLARE @rowsperpage INT
SET @start = 120000 SET @rowsperpage = 10
SELECT * FROM ( SELECT row_number() OVER (ORDER BY column) AS rownum, column2, column3, .... columnX
FROM table
) AS A
WHERE A.rownum
BETWEEN (@start) AND (@start + @rowsperpage)
Author info
Author: Stanislav DubenWebsite:
About me
I am experienced database engineer with more than 12 years developing and optimization experience. I worked on many high level projects based on SQL servers. I am also photograper and owner of many internet projects.

Add comment

Security code