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.