Several ways to _mssql SQL paging query

About the SQL statement pagination, there are a lot of online, I posted a part of it, and summed up their known pagination to the following to facilitate future access

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

Create DATABASE dbtest use
dbtest-
 
creates a test table create
table Pagetest
(
ID int identity (1,1) not NULL,
col01 int null,
col02 nvarchar (m) null,
col03 datetime null
)
 
--10,000 recordset
declare @i int
set @i=0
while (@i<10000)
begin
 inserts into Pagetest select CAST (Floor (rand () *10000) as int), Left (newid (), ten), GETDATE ()
 set @i=@i+1
end

2. Several typical paging SQL, the following example is 50 per page, 198*50=9900, take the 199th page of data.

--Writing 1,not In/top

Select Top * from Pagetest
where ID. (SELECT top 9900 ID to pagetest ORDER by ID)

--Writing 2,not exists

Select top to Pagetest where not
exists
(select 1 from (select top 9900 ID to pagetest ORDER by ID) a wher e a.id=pagetest.id) Order by
ID

--Writing 3,max/top

Select top to Pagetest
where id> (select Max (ID) from (select top 9900 ID) a)
or Der by ID

--Writing 4,row_number ()

Select Top x from
(select Row_number () over (order by ID) rownumber,* from pagetest) a
where rownumber>9900< C12/>select * from
(select Row_number ()-rownumber,* from Pagetest) a
where rownumber>9900 and rownumber<9951
 
select * from
(select Row_number ()-rownumber,* from Pagetest) a
where RowNumber between 9901 and 9950

--writing 5, seen in Csdn Last Post, Row_number () variant, does not produce the record ordinal based on the existing field, first by the condition filter and the order, then gives a constant column on the result set to produce the record serial number

SELECT *
from (
 select Row_number ()-Tempcolumn) rownumber,*
 from (select Top 9950 tempcolumn=0, * FROM Pagetest where 1=1 order by ID) a
) b
where rownumber>9900

3. In 10,000, 100,000 (take 1990 pages), 100 (take 19900 pages) set of records under the test.

Test sql:

DECLARE @begin_date datetime
DECLARE @end_date datetime
Select @begin_date = getdate ()
. YOUR code.....>
Select @end_date = getdate ()
Select DateDiff (MS, @begin_date, @end_date) as ' millisecond '

10,000: The basic feeling is not the difference.

100,000:

4. Conclusion:

1.max/top,row_number () is a good way of paging. Compared with row_number () only supports sql2005 and above versions, Max/top has better portability and can be applied to sql2000,access at the same time.

2.not exists feel is a little bit more efficient than not.

3.row_number () 3 different ways of writing efficiency look similar.

the variant of 4.row_number () is based on the fact that my test efficiency is really bad. The original post is here http://topic.csdn.net/u/20100617/04/80d1bd99-2e1c-4083-ad87-72bf706cb536.html

PS. The page sorter above is based on the self-added field ID. The test environment also provides the Int,nvarchar,datetime type field, or you can try it. However, it is not ideal to sort the large amount of data that is not indexed by the primary key.

5. Simply encapsulate the rownumber,max/top approach to the stored procedure.

RowNumber (): ALTER PROCEDURE [dbo].
[Proc_sqlpagebyrownumber] (@tbName VARCHAR (255),--The table name @tbGetFields VARCHAR (1000) = ' * ',--returns the field @OrderfldName VARCHAR (255),--The sorted field name @PageSize INT=20,--page size @PageIndex int=1,--page number @OrderType bit = 0,--0 Ascending, not 0 descending @strWhere VARCHAR (1000) = ',--query criteria--@To  Talcount INT OUTPUT--Returns the total number of records as--=============================================--Author:allen (liyuxin)--Create DATE:2012-03-30-Description: Paging Stored procedures (support for multiple table connection queries)--Modify [1]: 2012-03-30--========================================= = = = = BEGIN DECLARE @strSql VARCHAR (5000)--subject sentence DECLARE @strSqlCount NVARCHAR (500)--query record total subject sentence DECLARE @strOrder VARCHAR ( 300)--sort type--------------Total record number---------------IF ISNULL (@strWhere, ') <> ' SET @strSqlCount = ' Select @TotalCo Ut=count (*) from ' + @tbName + ' where 1=1 ' + @strWhere ELSE SET @strSqlCount = ' Select @TotalCout =count (*) from ' + @tbNam
 E--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 @TotalCount End

  

public static SqlParameter Makeinparam (String paramname, SqlDbType DbType, Int32 Size, Object Value) {return Makepa
  Ram (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 DbType, Int32 Size, ParameterDirection Direction, obj
   ECT 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>///Paging get data list and total rows///</summary>///<param name= "tbname" > table name </param>/// <param name= "Tbgetfields" > Return field </param>///<param name= "Orderfldname" > sorted field name </param>///<param name= "PageSize" > Page size </param>///<param name= "PageIndex" > page number </ param>///<param name= "OrderType" >false Ascending, True descending </param>///<param name= "strwhere" > Query criteria </ param> public static DataSet getpagelist (String tbname, String tbgetfields, string orderfldname, int PageSize, int Pa
      Geindex, 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.in T,0,pageindex), Makeinparam ("@OrderType", Sqldbtype.bit,0,ordertype), Makeinparam ("@strWhere", Sqldbtype.varch
   Ar,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 strwh ere, 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: Where you need to be aware of multiple table joins

1. Required Items: Tbname,orderfldname,tbgetfields

2. Examples:

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: Each condition must be added with and (for example: and userinfo.depid=1)

Max/top: (Simple to write, you need to meet the primary key field name is "id")

 create proc [dbo].[    Spsqlpagebymaxtop] @tbName varchar (255),--table name @tbFields varchar (1000),--Returns the field @PageSize int,--page size @PageIndex int, --page number @strWhere varchar (1000),--Query criteria @StrOrder varchar (255),--Sorting criteria @Total int OUTPUT--Returns the total number of records as declare @strSql Varch AR (5000)--subject sentence declare @strSqlCount nvarchar (500)--Total number of query records subject sentence--------------Total records---------------if @strWhere!= ' begin s ET @strSqlCount = ' Select @TotalCout =count (*) from ' + @tbName + ' where ' + @strWhere end ELSE begin set @strSqlCount = ' Selec T @TotalCout =count (*) from ' + @tbName end--------------Paging------------if @PageIndex <= 0 begin set @PageIndex = 1 en D Set @strSql = ' Select Top ' +str (@PageSize) + ' * from ' + @tbName + ' where id> (select Max (IDS) from (select Top ' +str "(@ PAGEINDEX-1) * @PageSize) + ' ID from ' + @tbName + ' + @strOrder + ') ' + @strOrder + ' exec sp_executesql @strSqlCount, N ' @Total Cout int output ', @Total output exec (@strSql) 

The garden found a version of Max/top that looked 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 output
exec [dbo].[ Spsqlpagebymaxtop] ' pagetest ', ' * ', 50,20, ', ' ORDER by ID ASC ', @count output
Select @count

The above is this article for SQL paging query the full content of several ways, I hope you like.