Simple paging program for SQL Server 2005

SQL Server 2005 adds a number of new features, including Ntile and Row_numer, which make it less nerve-racking for SQL to be flexible and easy to page through (no need to envy databases like Oracle).

Here is a very simple page-splitting query:

DECLARE @rows_per_page as INTEGER

DECLARE @current_page as INTEGER

DECLARE @total_pages as INTEGER

--Set the number of rows per page

SET @rows_per_page = 20

--Set the page number to display (starting from 1)

SET @current_page = 2

--Calculate Total pages

SELECT @total_pages = COUNT (*)/@rows_per_page

from TestTable;

--Lists the contents of the specified page

With T as

(

SELECT Ntile (@total_pages) over (order by ID) as Page_number, *

From TestTable

)

SELECT * FROM T

WHERE Page_number = @current_page

The program is simple enough to be used without explanation.

We can use the simple statement above, change the sorting criteria and query table, we can make a very common paging query stored procedures or query statements.

Similarly, the use of row_number can also be done paging query:

DECLARE @rows_per_page as INTEGER

DECLARE @current_page as INTEGER

DECLARE @start_row_num as INTEGER

--Set the number of rows per page

SET @rows_per_page = 20

--Set the page number to display (starting from 1)

SET @current_page = 2

--Set start line number

SET @start_row_num = (@current_page-1) * @rows_per_page

With T as

(

SELECT row_number () over (order by ID) as Row_number, *

From TestTable

)

SELECT * FROM T

WHERE row_number BETWEEN @start_row_num and @start_row_num + @rows_per_page seems simpler.

As to which is more efficient, this requires practical testing.