<p>How do I page results in SQL Server 2005?</p>

<p>I tried it in SQL Server 2000, but there was no reliable way to do this. I'm now wondering if SQL Server 2005 has any built in method?</p>

<p>What I mean by paging is, for example, if I list users by their username, I want to be able to only return the first 10 records, then the next 10 records and so on.</p>

<p>Any help would be much appreciated.</p>

[{'answer_id': 2843, 'author': 'Pat', 'author_id': 36, 'author_profile': 'https://Stackoverflow.com/users/36', 'pm_score': 6, 'selected': True, 'text': '<p>You can use <code>the Row_Number()</code> function.\nIts used as follows:</p>\n\n<pre><code>SELECT Row_Number() OVER(ORDER BY UserName) As RowID, UserFirstName, UserLastName\nFROM Users\n</code></pre>\n\n<p>From which it will yield a result set with a <code>RowID</code> field which you can use to page between.</p>\n\n<pre><code>SELECT * \nFROM \n    ( SELECT Row_Number() OVER(ORDER BY UserName) As RowID, UserFirstName, UserLastName\n      FROM Users \n    ) As RowResults\nWHERE RowID Between 5 AND 10\n</code></pre>\n\n<p>etc</p>\n'}, {'answer_id': 2855, 'author': 'Pat', 'author_id': 36, 'author_profile': 'https://Stackoverflow.com/users/36', 'pm_score': 0, 'selected': False, 'text': '<p>I believe you\'d need to perform a separate query to accomplish that unfortionately.</p>\n\n<p>I was able to accomplish this at my previous position using some help from this page:\n<a href="https://web.archive.org/web/20210510021915/http://aspnet.4guysfromrolla.com/articles/031506-1.aspx" rel="nofollow noreferrer">Paging in DotNet 2.0</a></p>\n\n<p>They also have it pulling a row count seperately.</p>\n'}, {'answer_id': 9870, 'author': 'Eric Z Beard', 'author_id': 1219, 'author_profile': 'https://Stackoverflow.com/users/1219', 'pm_score': 0, 'selected': False, 'text': '<p>Here\'s what I do for paging:  All of my big queries that need to be paged are coded as inserts into a temp table.  The temp table has an identity field that will act in a similar manner to the row_number() mentioned above.  I store the number of rows in the temp table in an output parameter so the calling code knows how many total records there are.  The calling code also specifies which page it wants, and how many rows per page, which are selected out from the temp table.</p>\n\n<p>The cool thing about doing it this way is that I also have an "Export" link that allows you to get all rows from the report returned as CSV above every grid in my application.  This link uses the same stored procedure: you just return the contents of the temp table instead of doing the paging logic.  This placates users who hate paging, and want to see <em>everything</em>, and want to sort it in a million different ways.</p>\n'}, {'answer_id': 11352, 'author': 'Brian', 'author_id': 700, 'author_profile': 'https://Stackoverflow.com/users/700', 'pm_score': 4, 'selected': False, 'text': "<p>If you're trying to get it in one statement (the total plus the paging).  You might need to explore SQL Server support for the partition by clause (windowing functions in ANSI SQL terms).  In Oracle the syntax is just like the example above using row_number(), but I have also added a partition by clause to get the total number of rows included with each row returned in the paging (total rows is 1,262):</p>\n\n<pre><code>SELECT rn, total_rows, x.OWNER, x.object_name, x.object_type\nFROM (SELECT COUNT (*) OVER (PARTITION BY owner) AS TOTAL_ROWS,\n         ROW_NUMBER () OVER (ORDER BY 1) AS rn, uo.*\n         FROM all_objects uo\n         WHERE owner = 'CSEIS') x\nWHERE rn BETWEEN 6 AND 10\n</code></pre>\n\n<p>Note that I have where owner = 'CSEIS' and my partition by is on owner.  So the results are:</p>\n\n<pre><code>RN  TOTAL_ROWS  OWNER   OBJECT_NAME            OBJECT_TYPE\n6   1262    CSEIS   CG$BDS_MODIFICATION_TYPES   TRIGGER\n7   1262    CSEIS   CG$AUS_MODIFICATION_TYPES   TRIGGER\n8   1262    CSEIS   CG$BDR_MODIFICATION_TYPES   TRIGGER\n9   1262    CSEIS   CG$ADS_MODIFICATION_TYPES   TRIGGER\n10  1262    CSEIS   CG$BIS_LANGUAGES            TRIGGER\n</code></pre>\n"}, {'answer_id': 74129, 'author': 'Andrew Burgess', 'author_id': 12096, 'author_profile': 'https://Stackoverflow.com/users/12096', 'pm_score': 2, 'selected': False, 'text': "<p>When I need to do paging, I typically use a temporary table as well.  You can use an output parameter to return the total number of records.  The case statements in the select allow you to sort the data on specific columns without needing to resort to dynamic SQL.</p>\n\n<pre><code>--Declaration--\n\n--Variables\n@StartIndex INT,\n@PageSize INT,\n@SortColumn VARCHAR(50),\n@SortDirection CHAR(3),\n@Results INT OUTPUT\n\n--Statements--\nSELECT @Results = COUNT(ID) FROM Customers\nWHERE FirstName LIKE '%a%'\n\nSET @StartIndex = @StartIndex - 1 --Either do this here or in code, but be consistent\nCREATE TABLE #Page(ROW INT IDENTITY(1,1) NOT NULL, id INT, sorting_1 SQL_VARIANT, sorting_2 SQL_VARIANT)\nINSERT INTO #Page(ID, sorting_1, sorting_2)\nSELECT TOP (@StartIndex + @PageSize)\n    ID,\n    CASE\n        WHEN @SortColumn='FirstName' AND @SortDirection='ASC' THEN CAST(FirstName AS SQL_VARIANT)\n        WHEN @SortColumn='LastName' AND @SortDirection='ASC' THEN CAST(LastName AS SQL_VARIANT)\n        ELSE NULL\n    END AS sort_1,\n    CASE\n        WHEN @SortColumn='FirstName' AND @SortDirection='DES' THEN CAST(FirstName AS SQL_VARIANT)\n        WHEN @SortColumn='LastName' AND @SortDirection='DES' THEN CAST(LastName AS SQL_VARIANT)\n        ELSE NULL\n    END AS sort_2\nFROM (\n    SELECT\n        CustomerId AS ID,\n        FirstName,\n        LastName\n    FROM Customers\n    WHERE\n        FirstName LIKE '%a%'\n) C\nORDER BY sort_1 ASC, sort_2 DESC, ID ASC;\n\nSELECT\n    ID,\n    Customers.FirstName,\n    Customers.LastName\nFROM #Page\nINNER JOIN Customers ON\n    ID = Customers.CustomerId\nWHERE ROW &gt; @StartIndex AND ROW &lt;= (@StartIndex + @PageSize)\nORDER BY ROW ASC\n\nDROP TABLE #Page\n</code></pre>\n"}, {'answer_id': 752940, 'author': 'Beska', 'author_id': 57120, 'author_profile': 'https://Stackoverflow.com/users/57120', 'pm_score': 3, 'selected': False, 'text': "<p>The accepted answer for this doesn't actually work for me...I had to jump through one more hoop to get it to work.</p>\n\n<p>When I tried the answer</p>\n\n<pre><code>SELECT Row_Number() OVER(ORDER BY UserName) As RowID, UserFirstName, UserLastName\nFROM Users\nWHERE RowID Between 0 AND 9\n</code></pre>\n\n<p>it failed, complaining that it didn't know what RowID was.</p>\n\n<p>I had to wrap it in an inner select like this:</p>\n\n<pre><code>SELECT * \nFROM\n    (SELECT\n    Row_Number() OVER(ORDER BY UserName) As RowID, UserFirstName, UserLastName\n    FROM Users\n    ) innerSelect\nWHERE RowID Between 0 AND 9\n</code></pre>\n\n<p>and then it worked.</p>\n"}]
['https://Stackoverflow.com/questions/2840', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/383/']