Limit Results in SQL Server Using Row_Number

Here is a sample query that limits the number of results in a query to 500. The first bold item is the column the results need to be ordered by, and the second bold item is the table that your are pulling the data from. Everything else can be used as is to pull all columns from a table and limit the results to 500 rows.


SELECT * FROM
( SELECT
    ROW_NUMBER() OVER (ORDER BY ikeyid ASC) AS ROW_NUMBER,
    *
  FROM A_Table
) foo
WHERE ROW_NUMBER <= 500


You can also adjust the where statement in order to grab a range of records. I had to do this in order to pull out chunks of a huge table rather than pulling them all at once.

WHERE ROW_NUMBER > 0 AND ROW_NUMBER <= 50000

WHERE ROW_NUMBER > 50000 AND ROW_NUMBER <= 100000

WHERE ROW_NUMBER > 100000

Running this query 3 times with the 3 different where statements listed above will result in 3 results sets that have 50,000 rows a piece and the final query pulls in any row over the first 100,000.