MS SQL replacement for OFFSET command.

[Unheard]

As the topic says, I need a replacement command for OFFSET that will work in MS SQL. Thanks!

 

[MrChad]

I don't think there is an equivalent. This article describes how to dynamically generate row numbers in your SQL queries. That should allow you to limit the number of rows returned (although the process may become tedious for complex queries).

 

[Mark R]

There is no equivalent in SQL Server 2000 and before.

You need to work around it - e.g. create a temporary table with an identity field.

The following example retrieves rows 11 - 20

DECLARE @temp TABLE (CustomerID int, CustName varchar(50), Row_num int identity (1,1))
INSERT INTO @temp SELECT TOP 20 CustomerID, CustName FROM Customers ORDER BY DateSignedUp ASC
SELECT CustomerID, CustName FROM @temp WHERE Row_num > 10

Note that if you want to use a parameter for the offset then you need to change things, because you can't pass a parameter to TOP. See the next example:

declare @offset int
declare @numrows int

DECLARE @temp TABLE (CustomerID int, CustName varchar(50), Row_num int identity (1,1))
SET ROWCOUNT (@offset + @numrows) -- works like top
INSERT INTO @temp SELECT TOP 20 CustomerID, CustName FROM Customers ORDER BY DateSignedUp ASC
SET ROWCOUNT 0 -- switch off the limit
SELECT CustomerID, CustName FROM @temp WHERE Row_num > @offset


SQL server 2005 has row-numbering functions built in, but still has no OFFSET command.

The equivalent for SQL server 2005 would be:

SELECT CustomerID, CustName FROM ( SELECT CustomerID, Custname, R = Row_Number() OVER (ORDER BY DateSignedUp ASC) FROM Customers) subqry WHERE R>10 AND R<=20

 

[kamper]

Can be done quite easily with a subquery:

select top 10 * from foo where foo.key not in (select top 10 key from foo)

That'll get you the second 10 rows. Adjust top X in the nested query to move your page, adjust top X in the outer query to adjust page size. And use the same order by clause in both, of course.