SQL SERVER 2008 Paging

Today is ready to use the project to increase the paging function, the original is too limited, so the Internet to find some information recorded, the front is the use of others to summarize, the following is their own test results, haha, testing to have the basis:

Three methods of paging in SQL SERVER 2008 and comparison, a friend you need can refer to

Create a table:

The code is as follows:

CREATE TABLE [TestTable] (
[ID] [int] IDENTITY (1, 1) not NULL,
[FirstName] [nvarchar] (+) COLLATE chinese_prc_ci_as NULL,
[LastName] [nvarchar] (+) COLLATE chinese_prc_ci_as NULL,
[Country] [nvarchar] (COLLATE) Chinese_prc_ci_as NULL,
[Note] [nvarchar] (COLLATE) Chinese_prc_ci_as NULL
) on [PRIMARY]
GO
Insert data: (200,000, test with more data will be obvious)
SET Identity_insert testtable on
DECLARE @i int
Set @i=1
While @i<=200000
Begin
INSERT INTO testtable ([id], FirstName, LastName, Country,note) VALUES (@i, ' firstname_xxx ', ' lastname_xxx ', ' country_xxx ', ' note_xxx ')
Set @[email protected]+1
End
SET identity_insert testtable OFF

Paging Scenario One: (using not in and select top paging)

Statement form:

The code is as follows:

SELECT TOP 10 *
From TestTable
WHERE (ID not in
(SELECT TOP ID
From TestTable
ORDER by ID))
ORDER by ID

SELECT TOP Page Size *
From TestTable
WHERE (ID not in
(SELECT TOP Page Size * Number of pages ID
From table
ORDER by ID))
ORDER by ID

Paging Scenario Two: (using ID greater than how much and select top paging)
Statement form:

The code is as follows:

SELECT TOP 10 *
From TestTable
WHERE (ID >
(SELECT MAX (ID)
From (SELECT TOP ID
From TestTable
ORDER by ID) (as T))
ORDER by ID

SELECT TOP Page Size *
From TestTable
WHERE (ID >
(SELECT MAX (ID)
From (SELECT TOP page Size * Number of pages ID
From table
ORDER by ID) (as T))
ORDER by ID

Paging Scenario Three: (Paging with SQL cursor stored procedures)

The code is as follows:

CREATE PROCEDURE Xiaozhengge
@sqlstr nvarchar (4000),--query string
@currentpage int,--page n
@pagesize INT--Number of rows per page
As
SET NOCOUNT ON
declare @P1 int,--P1 is the ID of the cursor
@rowcount int
EXEC sp_cursoropen @P1 output, @sqlstr, @scrollopt =1, @ccopt =1,@[email protected] Output
Select Ceiling (1.0* @rowcount/@pagesize) as total number of pages--, @rowcount as rows, @currentpage as current page
Set @currentpage = (@currentpage-1) * @pagesize +1
exec sp_cursorfetch @P1, @currentpage, @pagesize
EXEC sp_cursorclose @P1
SET NOCOUNT OFF

Other scenarios: If you don't have a primary key, you can use a temporary table, or you can do it with scenario three, but the efficiency will be low.

When tuning is recommended, the query efficiency increases with the primary key and index.

Using SQL Query Analyzer to show comparisons: My conclusion is:
Paging Scenario Two: (using ID greater than how much and select top paging) The most efficient, need to splice SQL statements
Paging Scenario One: (using not and select top paging) second, the need to splice SQL statements
Paging Scenario Three: (Paging with SQL cursor stored procedures) is the least efficient, but the most common
In the actual situation, to be specific analysis.

The above is someone else to write the article, here I as a preservation, to prevent the future text can not find, the following is his own writing:

I just tested the second and third scenario, found that the second solution is really fast, with the increase in data, the difference is not an order of magnitude, once in a book read about the cursor, the efficiency is not as straightforward as the implementation of SQL, scenario two in use need to pay attention to the ID, where the ID is self-increment, if there is no self , we need to introduce rownum. This time the sql may become more complex.

I wrote a page by the scheme two, because some tables without self-growing fields, pagination two implementation will be difficult, and then use SQL Server's Row_number () function to implement a

Select TOP Page Size * FROM (select Row_number () over (order by ID) as rn,* from TestTable) temtable where RN >= page size * (Page 1) and RN < Page size * page number

This statement comes from Oracle's paging idea, as this can be encapsulated in a program, and of course, there is no higher efficiency and requires a sort field. The data I tested was 950,000, 11 fields.

Conditions are: where RN > 100000 and rn<101000

Self-written test results:

SQL Server parse and compile time:
CPU time = 3 milliseconds, elapsed time = 3 milliseconds.

(999 rows affected)
Table ' Memberlevelglide '. Scan count 1, logical read 101,196 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.

(8 rows affected)

SQL Server Execution Time:
CPU time = 156 milliseconds, elapsed time = 531 milliseconds.
SQL Server parse and compile time:
CPU time = 0 milliseconds, elapsed time = 0 milliseconds.

SQL Server Execution Time:
CPU time = 0 milliseconds, elapsed time = 0 milliseconds.

SQL Server Execution Time:
CPU time = 0 milliseconds, elapsed time = 0 milliseconds.

Outcome of Programme II:

SQL Server parse and compile time:
CPU time = 0 milliseconds, elapsed time = 0 milliseconds.

(1000 rows affected)
Table ' Memberlevelglide '. Scan count 2, logical read 1205 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.

(9 rows affected)

SQL Server Execution Time:
CPU time = 31 milliseconds, elapsed time = 217 milliseconds.
SQL Server parse and compile time:
CPU time = 0 milliseconds, elapsed time = 0 milliseconds.

SQL Server Execution Time:
CPU time = 0 milliseconds, elapsed time = 0 milliseconds.

SQL Server Execution Time:
CPU time = 0 milliseconds, elapsed time = 0 milliseconds.

Reference: http://www.jb51.net/article/35269.htm