Shulou(Shulou.com)05/31 Report--

This article shows you how to achieve paging query in sql, the content is concise and easy to understand, it can definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

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

Create database DBTestuse DBTest-- create test table create table pagetest (id int identity (1) not null,col01 int null,col02 nvarchar (50) null,col03 datetime null)-- 10, 000 recordset declare @ I intset @ i=0while (@ I (select max (id) from (select top 9900 id from pagetest order by id) a) order by id

-- write "4" rowdy 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 rownumber9900

3. It was tested under 10,000, 100000 (1990 pages) and 100 (19900 pages) recordsets, respectively.

Test sql:

Declare @ begin_date datetimedeclare @ end_date datetimeselect @ begin_date = getdate () select @ end_date = getdate () select datediff (ms,@begin_date,@end_date) as'Ms'

10,000: basically can not feel the difference.

100000:

4. Conclusion:

1. Maxmax topdirection ROWBUMBER () is a good paging method. Compared with row _ NUMBER (), which only supports sql2005 and above, max/top has better portability and can be applied to sql2000,access at the same time.

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

The efficiency of the three different ways of writing 3.ROW_NUMBER () looks about the same.

The variant of 4.ROW_NUMBER () is not very efficient based on me. The original post is here, http://topic.csdn.net/u/20100617/04/80d1bd99-2e1c-4083-ad87-72bf706cb536.html.

PS. The paging sorting above is based on the self-incrementing field id. The test environment also provides an int,nvarchar,datetime type field, or you can try it. However, the efficiency of sorting large amounts of data without indexes for non-primary keys should not be ideal.

5. Simply encapsulate the ROWNUMBER,max/top way into a stored procedure.

ROWNUMBER (): ALTER PROCEDURE [dbo]. [Proc_SqlPageByRownumber] (@ tbName VARCHAR (1000),-- Table name @ tbGetFields VARCHAR (1000) ='*',-- return field @ OrderfldName VARCHAR (255),-- sorted field name @ PageSize INT=20,-- Page size @ PageIndex INT=1,-- Page number @ OrderType bit = 0,-- 0 ascending order Non-zero descending order @ strWhere VARCHAR (1000) ='' -- query condition-- @ TotalCount INT OUTPUT-- return total number of records) AS-- =-- Author: allen (liyuxin)-- Create date: 2012-03-30 strSqlCount NVARCHAR-Description: paging stored procedure (support for multi-table join queries)-- Modify [1]: 2012-03-30 UV-= BEGIN DECLARE @ strSql VARCHAR (5000)-- subject sentence DECLARE @ strSqlCount NVARCHAR (5000)-- Total number of query records subject sentence DECLARE @ strOrder VARCHAR )-- sort type-total 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-pagination-IF @ PageIndex 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 } / get the list of data and the total number of rows / Table name / / return field / sorted field name / Page size / / Page number / false ascending order True descending / / query criteria 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: what you should pay attention to when connecting multiple tables

1. Required: tbName,OrderfldName,tbGetFields

two。 Example:

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 _ DSC" (format: Name,ASC | ID,DESC) strWhere: AND must be added before each condition (for example: AND UserInfo.DepID=1)

Max/top: (in a nutshell, the primary key field name is "id".)

Create proc [dbo]. [spSqlPageByMaxTop] @ tbName varchar (1000),-- 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-- return total number of records asdeclare @ strSql varchar (5000)-- subject sentence declare @ strSqlCount nvarchar (5000)-- query total number of records-if @ strWhere! =''beginset @ strSqlCount='Select @ TotalCout=count (*) from' + @ tbName + 'where' + @ strWhereendelsebeginset @ strSqlCount='Select @ TotalCout=count ( *) from'+ @ tbNameend- pagination-if @ PageIndex

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.