Several methods of querying by SQL page and several methods of querying by SQL page

Several methods of querying by SQL page and several methods of querying by SQL page

There are many pages for SQL statements on the Internet. I will post some pages and summarize the pages I have known to facilitate future reference.

1. Create a test environment, (It takes about 5 minutes to insert 1 million data records ).

Create database DBTestuse DBTest -- create test table create table pagetest (id int identity (1,1) not null, col01 int null, col02 nvarchar (50) null, col03 datetime null) -- 10 thousand record set declare @ I intset @ I = 0 while (@ I <10000) begin insert into pagetest select cast (floor (rand () * 10000) as int ), left (newid (), 10), getdate () set @ I = @ I + 1end

2. Several Typical paging SQL statements,The following example shows 50 entries per page, 198*50 = 9900, and the number of data records is 199th.

-- Statement 1, not in/top

select top 50 * from pagetestwhere id not in (select top 9900 id from pagetest order by id)order by id

-- Statement 2, not exists

select top 50 * from pagetestwhere not exists(select 1 from (select top 9900 id from pagetest order by id)a where a.id=pagetest.id)order by id

-- Statement 3, max/top

select top 50 * from pagetestwhere id>(select max(id) from (select top 9900 id from pagetest order by id)a)order by id

-- Statement 4, row_number ()

select top 50 * from(select row_number()over(order by id)rownumber,* from pagetest)awhere rownumber>9900 select * from(select row_number()over(order by id)rownumber,* from pagetest)awhere rownumber>9900 and rownumber<9951 select * from(select row_number()over(order by id)rownumber,* from pagetest)awhere rownumber between 9901 and 9950

-- Write 5: The row_number () variant shown in the post on csdn does not generate record numbers based on existing fields. Filter and sort records by conditions first, then a constant column in The result set is used to generate the record serial number.

select *from ( select row_number()over(order by tempColumn)rownumber,* from (select top 9950 tempColumn=0,* from pagetest where 1=1 order by id)a)bwhere rownumber>9900

3. perform tests in the 10 thousand, 0.1 million (take 1990 pages), and 100 (take 19900 pages) records sets.

Test SQL:

Declare @ begin_date datetimedeclare @ end_date datetimeselect @ begin_date = getdate () <..... your code...> select @ end_date = getdate () select datediff (MS, @ begin_date, @ end_date) as 'millisecond'

10 thousand: Basically there is no difference.

0.1 million:

4. Conclusion:

1. max/top and ROW_NUMBER () are all good paging methods. Compared with ROW_NUMBER (), it only supports sql2005 and later versions. max/top has better portability and can be applied to both sql2000 and access.

2. not exists feels a little more efficient than not in.

3. The efficiency of writing three ROW_NUMBER () statements is similar.

4. The variation of ROW_NUMBER () is based on my test efficiency. Original post http://topic.csdn.net/u/20100617/04/80d1bd99-2e1c-4083-ad87-72bf706cb536.html here

PS. The above paging sorting is based on the id of the auto-increment field. The test environment also provides int, nvarchar, and datetime fields. You can also try them. However, the sorting efficiency of large data volumes without indexes for non-primary keys should be unsatisfactory.

5. encapsulate ROWNUMBER and max/top into the stored procedure.

ROWNUMBER (): alter procedure [dbo]. [Proc_SqlPageByRownumber] (@ tbName VARCHAR (255), -- table name @ tbGetFields VARCHAR (1000) = '*', -- Return field @ OrderfldName VARCHAR (255 ), -- sorting field name @ PageSize INT = 20, -- page size @ PageIndex INT = 1, -- page number @ OrderType bit = 0, -- 0 in ascending order, non-0 descending order @ strWhere VARCHAR (1000) = '', -- Query condition -- @ TotalCount int output -- total number of records returned) AS -- =================================================== =======-- Author: allen (liyuxin) -- Create date: 2012-03-30 -- Description: Paging Stored Procedure (Multi-table join query supported) -- Modify [1]: 2012-03-30 -- ================================================= ======= begin declare @ strSql VARCHAR (5000) -- subject sentence DECLARE @ strSqlCount NVARCHAR (500) -- query the total number of records subject sentence DECLARE @ strOrder VARCHAR (300) -- sorting type -------------- total number of records ----------- if isnull (@ strWhere ,'') <> ''set @ strSqlCount = 'select @ TotalCout = count (*) from '+ @ tbName + 'where 1 = 1' + @ strWhere else set @ strSqlCount = 'select @ TotalCout = count (*) from' + @ tbName -- exec sp_executesql @ strSqlCount, n' @ TotalCout int output', @ TotalCount output -------------- paging ------------ IF @ PageIndex <= 0 SET @ PageIndex = 1 IF (@ OrderType <> 0) SET @ strOrder = 'ORDER BY' + @ OrderfldName + 'desc' else set @ strOrder = 'ORDER BY' + @ OrderfldName + 'asc 'SET @ strSql = 'select * FROM (SELECT ROW_NUMBER () OVER ('+ @ strOrder +') RowNo, '+ @ tbGetFields + 'from' + @ tbName + 'where 1 = 1' + @ strWhere +') tb WHERE tb. rowNo BETWEEN '+ str (@ PageIndex-1) * @ PageSize + 1) + 'and' + str (@ PageIndex * @ PageSize) exec (@ strSql) SELECT @ TotalCountEND

Public static SqlParameter MakeInParam (string ParamName, SqlDbType DbType, Int32 Size, object Value) {return MakeParam (ParamName, DbType, Size, ParameterDirection. input, Value);} public static SqlParameter MakeOutParam (string ParamName, SqlDbType DbType) {return MakeParam (ParamName, DbType, 0, ParameterDirection. output, null);} public static SqlParameter MakeParam (string ParamName, SqlDbType DbT Ype, Int32 Size, ParameterDirection Direction, object Value) {SqlParameter param; if (Size> 0) param = new SqlParameter (ParamName, DbType, Size ); else param = new SqlParameter (ParamName, DbType); param. direction = Direction; if (! (Direction = ParameterDirection. output & Value = null) param. value = Value; return param ;} /// <summary> /// obtain the data list and total number of rows by PAGE /// </summary> /// <param name = "tbName"> table name </param> /// <param name = "tbGetFields"> return field </param> /// <param name = "OrderFldName"> name of the sorted field </param> /// <param name = "PageSize"> page size </param> /// <param name = "PageIndex"> page number </param> /// <param name = "OrderType"> false ascending, true descending order </param> // <param name = "strWhere"> query condition </param> public static DataSet GetPageList (string tbName, string tbGetFields, string OrderFldName, int PageSize, int PageIndex, string strWhere) {SqlParameter [] parameters = {MakeInParam ("@ tbName", SqlDbType. varChar, 255, tbName), MakeInParam ("@ tbGetFields", SqlDbType. varChar, 1000, tbGetFields), MakeInParam ("@ OrderfldName", SqlDbType. varChar, 255, OrderFldName), MakeInParam ("@ PageSize", SqlDbType. int, 0, PageSize), MakeInParam ("@ PageIndex", SqlDbType. int, 0, PageIndex), MakeInParam ("@ OrderType", SqlDbType. bit, 0, OrderType), MakeInParam ("@ strWhere", SqlDbType. varChar, 1000, strWhere), // MakeOutParam ("@ TotalCount", SqlDbType. int)}; return RunProcedure ("Proc_SqlPageByRownumber", parameters, "ds ");}

Call:

public DataTable GetList(string tbName, string tbGetFields, string OrderFldName, int PageSize, int PageIndex, string strWhere, ref int TotalCount)  {   DataSet ds = dal.GetList(tbName, tbGetFields, OrderFldName, PageSize, PageIndex, strWhere);   TotalCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);   return ds.Tables[0];  }

Note: Notes for multi-table join

1. required items: tbName, OrderfldName, tbGetFields

2. instance:

TbName = "UserInfo u inner join Department d ON u. depID = d. ID "tbGetFields =" u. id as UserID, u. name, u. sex, d. id as DepID, d. defName "OrderfldName =" u. ID, ASC | u. name, DESC "(Format: Name, ASC | ID, DESC) strWhere: AND (for example, AND UserInfo) must be added before each condition. depID = 1)

Max/top: (if you write it down, the name of the primary key field must be "id ")

Create proc [dbo]. [spSqlPageByMaxTop] @ tbName varchar (255), -- table name @ tbFields varchar (1000), -- Return field @ PageSize int, -- page size @ PageIndex int, -- page number @ strWhere varchar (1000), -- Query condition @ StrOrder varchar (255), -- Sort condition @ Total int output -- Total number of returned records asdeclare @ strSql varchar (5000) -- subject sentence declare @ strSqlCount nvarchar (500) -- query the total number of records. subject sentence ------------ total number of records ------------- if @ strWhere! = ''Ininset @ strSqlCount = 'select @ TotalCout = count (*) from '+ @ tbName + 'where' + @ strWhereendelsebeginset @ strSqlCount = 'select @ TotalCout = count (*) from '+ @ tbNameend ------------ paging ------------ if @ PageIndex <= 0 begin set @ PageIndex = 1 endset @ strSql = 'select top' + str (@ PageSize) + '* from' + @ tbName +' where id> (select max (id) from (select top '+ str (@ PageIndex-1) * @ PageSize) + 'id from' + @ tbName + ''+ @ strOrder + ') a)' + @ strOrder +'' exec sp_executesql @ strSqlCount, n' @ TotalCout int output ', @ Total outputexec (@ strSql)

Found in the garden Max/top such a version, looks very powerful, http://www.cnblogs.com/hertcloud/archive/2005/12/21/301327.html

Call:

declare @count int--exec [dbo].[spSqlPageByRownumber]'pagetest','*',50,20,'','order by id asc',@count outputexec [dbo].[spSqlPageByMaxTop]'pagetest','*',50,20,'','order by id asc',@count outputselect @count

The above is all the content in this article for querying several types of SQL by page. I hope you will like it.