Common Database Paging SQL summary (Oracle/mysql/sql server)

ORACLE paging Query SQL statement

The following is a very inefficient

The code is as follows	Copy Code
SELECT * FROM (select A.*, rownum rn from (SELECT * to Ipay_rcd_fs_return ORDER BY id DESC) A) WHERE RN <20;

This is a highly efficient

The code is as follows	Copy Code
Select A.*, RowNum RN from (SELECT * to Ipay_rcd_fs_return ORDER BY id desc) A WHERE rownum <= 20;

MySQL paging Query SQL statement

Grammar:

The code is as follows	Copy Code
SELECT * FROM table LIMIT [offset,] rows | Rows Offset Offset

The LIMIT clause can be used to force a SELECT statement to return the specified number of records. LIMIT accepts one or two numeric parameters. parameter must be an integer constant.

Given two parameters, the first parameter specifies the offset of the first row to return the record, and the second parameter specifies the maximum number of rows to be returned.

The offset of the initial record line is 0 (instead of 1): In order to be compatible with PostgreSQL, MySQL also supports syntax: LIMIT # offset #.

The code is as follows

Copy Code

Mysql> SELECT * FROM table LIMIT 5, 10; Retrieve record row 6-15, note that 10 is offset To retrieve all row rows from an offset to the end of a recordset, you can specify a second argument of-1: Mysql> SELECT * from table LIMIT 95,-1; Retrieves the record row 96-last. If only one argument is given, it represents the maximum number of record rows returned: Mysql> SELECT * from table LIMIT 5; Retrieves the first 5 rows//i.e., LIMIT N is equivalent to LIMIT 0,n.

SQL SERVER paging query SQL statement

Create a table:

The code is as follows	Copy Code
CREATE TABLE [TestTable] ( [ID] [int] IDENTITY (1, 1) not NULL, [FirstName] [nvarchar] (MB) COLLATE chinese_prc_ci_as NULL, [LastName] [nvarchar] (MB) COLLATE chinese_prc_ci_as NULL, [Country] [nvarchar] (m) COLLATE chinese_prc_ci_as NULL, [Note] [nvarchar] (Watts) COLLATE chinese_prc_ci_as NULL ) on [PRIMARY] Go

Insert data: (20,000, test with more data will be obvious)
SET Identity_insert testtable on

The code is as follows	Copy Code
DECLARE @i int Set @i=1 While @i<=20000 Begin INSERT INTO testtable ([id], FirstName, LastName, Country,note) VALUES (@i, ' firstname_xxx ', ' lastname_xxx ', ' Country_ ') XXX ', ' note_xxx ') Set @i=@i+1 End SET Identity_insert testtable off

-------------------------------------

Paging Scheme one: (using not in and select top pagination)
Statement form:
S

The code is as follows	Copy Code
Elect 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 * Pages ID From table Order by ID) ORDER BY ID

-------------------------------------

Paging Scheme two: (with ID greater than how much and select top pagination)
Statement form:

The code is as follows	Copy Code
SELECT Top 10 * From TestTable WHERE (ID > (SELECT MAX (ID) From (SELECT the 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 * Pages ID From table Order by ID) as T) ORDER BY ID

-------------------------------------

Paging Scenario Three: (using SQL Cursor stored procedure paging)

code is as follows

copy code

create Procedure Xiaozhengge @sqlstr nvarchar (4000),--query string @currentpage int,--nth page @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, @rowcount = @rowcount 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 do not have a primary key, you can use a temporary table, or you can do it with scenario three, but the efficiency is low.
When optimization is recommended, the query efficiency is increased by adding primary keys and indexes.

Displays comparisons through SQL Query Analyzer: My conclusion is:
Paging scheme two: (with ID greater than the number and select top paging) The most efficient, need to stitch SQL statements
Paging scheme one: (with not in and select top pagination) Efficiency second, need to splice SQL statements
Paging scheme three: (using SQL Cursor stored procedure paging) is the least efficient, but the most common