LIMIT is not in the SQL standard, and in fact only exists in MySQL. It is popular enough, however, that many SQL Server users want to see it implemented here. They say it is intuitive to use, but I disagree. Does LIMIT(8,12) really make it unambiguous that the query should return rows 9-20?

In SQL Server 2005, you can use the new ROW_NUMBER() function in a subquery, and filter against the result. For example:

CREATE TABLE #foo
(
    RacerID INT,
    FinishTime DATETIME
)
 
INSERT #foo SELECT RacerID = 42, FT = '03:42:14'
    UNION ALL SELECT 37, '03:42:18'
    UNION ALL SELECT 22, '03:41:32'
    UNION ALL SELECT 21, '03:40:17'
    UNION ALL SELECT 57, '03:55:32'
    UNION ALL SELECT 12, '03:39:14'
    UNION ALL SELECT 32, '03:41:51'
    UNION ALL SELECT 17, '03:45:12'
    UNION ALL SELECT 46, '03:48:59'
    UNION ALL SELECT 24, '03:40:12'
    UNION ALL SELECT 81, '03:43:21'
    UNION ALL SELECT 62, '03:49:19'
    UNION ALL SELECT 41, '03:52:01'

Now, let's say I wanted to retrieve the runners who finished 5th through 10th.

SELECT
    Rank,
    RacerID,
    FinishTime = CONVERT(CHAR(8), FinishTime, 108) FROM
    (
      SELECT
        Rank = ROW_NUMBER() OVER (ORDER BY FinishTime),
        RacerID,
        FinishTime
      FROM
        #foo
    ) i
WHERE
    Rank BETWEEN 5 and 10
ORDER BY
    Rank

Of course, the 5 and 10 could have easily been populated by variables, whereas in existing versions you would only be able to do this with a nested TOP clause if you also used dynamic SQL. As an added twist, SQL Server 2005 now allows variables/expressions in the TOP clause, so we could also do this:

DECLARE @rowA INT
DECLARE @rowB INT
 
SET @rowA = 5
SET @rowB = 10
 
SELECT TOP (@rowA+1)
    RacerID,
    FinishTime
FROM
    (
      SELECT TOP (@rowB)
        RacerID,
        FinishTime
      FROM
        #foo
      ORDER BY
        FinishTime
    ) i
ORDER BY
    FinishTime DESC

Personally, I think that query is far less intuitive and far more cumbersome to write than the ROW_NUMBER() version listed above. Some extra complications it adds are the fact that you need a third outer query and ORDER BY to return the results in the right order, it does include the actual rank or rownumber you were looking for, and it makes it much more difficult to derive a properly formatted character string that doesn't need to be used in the ORDER BY that drives the TOP functionality. Just look at the differences in the output:

I just need to get used to the ROW_NUMBER() syntax, that's all.

Of course, this kind of methodology can be<