Several SQL notation for querying paging

Create DATABASE DBTest
Use DBTest

--Create a test table
CREATE TABLE Pagetest
(
ID int identity (a) is not NULL,
COL01 int NULL,
COL02 nvarchar () null,
COL03 datetime NULL
)

--1 Record Set
DECLARE @i int
Set @i=0
while (@i<10000)
Begin
    Insert INTO Pagetest Select CAST (Floor (rand () *10000) as int), Left (newid (), ten), GETDATE ()
    Set @[email protected]+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 not in (the select top 9900 ID from the pagetest ORDER by ID)
ORDER BY ID


--Writing 2,not exists
Select Top * from Pagetest
Where NOT EXISTS
(select 1 from (select top 9900 ID from Pagetest ORDER by id) a where a.id=pagetest.id)
ORDER BY ID

--Notation 3,max/top
Select Top * from Pagetest
Where id> (select Max (ID) from (the Select top 9900 ID from the pagetest ORDER by ID) a)
ORDER BY ID

--Notation 4,row_number ()
Select Top * FROM
(select Row_number () over (order by ID) rownumber,* from pagetest) a
where rownumber>9900

SELECT * FROM
(select Row_number () over (order by ID) rownumber,* from pagetest) a
where rownumber>9900 and rownumber<9951

SELECT * FROM
(select Row_number () over (order by ID) rownumber,* from pagetest) a
where RowNumber between 9901 and 9950

---5, in the csdn on a post to see, Row_number () variant, not based on the existing field generated record ordinal, first by criteria filtering and sequencing, and then on the result set to a constant column to generate the record sequence 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
) b
where rownumber>9900

3. Test under 10,000, 100,000 (take 1990 pages), 100 (take 19900 pages) record set.

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 ' milliseconds '

10,000: The basic feeling is not different.

100,000:

4. Conclusion:

1.max/top,row_number () is a good paging method. Compared to Row_number () only supports sql2005 and above, Max/top has better portability and can be used for sql2000,access.

2.not exists feeling is a little more efficient than not.

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

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

PS. The page sort above is based on the self-increment field ID. The test environment also provides a int,nvarchar,datetime type field, or you can try it. However, the efficiency of large data sequencing that is not indexed for non-primary keys should be very unsatisfactory.

5. Simply encapsulate the rownumber,max/top in a stored procedure.

ROWNUMBER ():

1 ALTER PROCEDURE [dbo]. [Proc_sqlpagebyrownumber]
2 (
3 @tbName VARCHAR (255),--table name
4 @tbGetFields VARCHAR (+) = ' * ',--return field
5 @OrderfldName VARCHAR (255),--sorted field names
6 @PageSize int=20,--page size
7 @PageIndex int=1,--page
8 @OrderType bit = 0,--0 Ascending, not 0 descending
9 @strWhere VARCHAR (1000) = ',--query condition
10[email protected]INT OUTPUT--Returns the total number of records
11)
As
13--=============================================
--Author:allen (Liyuxin)
--Create date:2012-03-30
Description: Paging Stored procedure (multi-table connection query support)
--Modify [1]: 2012-03-30
18--=============================================
BEGIN
DECLARE @strSql VARCHAR (5000)--the subject sentence
DECLARE @strSqlCount NVARCHAR (500)--Query the total number of records the subject sentence
DECLARE @strOrder VARCHAR (300)--Sort type
23
Total number of records-----------------------------
IF ISNULL (@strWhere, ') <> '
The SET @strSqlCount = ' Select @TotalCout =count (*) from ' + @tbName + ' where 1=1 ' + @strWhere
The ELSE SET @strSqlCount = ' Select @TotalCout =count (*) from ' + @tbName
28
--exec sp_executesql @strSqlCount, N ' @TotalCout int output ', @TotalCount output
--------------Page------------
IF @PageIndex <= 0 SET @PageIndex = 1
32
IF (@OrderType <>0) SET @strOrder = ' ORDER by ' [email protected]+ ' DESC '
"ELSE SET @strOrder = ' ORDER by ' [email protected]+ ' ASC '
35
SET @strSql = ' SELECT * FROM
PNS (SELECT row_number () over (' [email protected]+ ') RowNo, ' + @tbGetFields + ' from ' + @tbName + ' WHERE 1=1 ' + @strWher E+ ') TB
The WHERE TB. RowNo between ' +str ((@PageIndex-1) * @PageSize + 1) + ' and ' +str (@PageIndex * @PageSize)
39
+ EXEC (@strSql)
SELECT @TotalCount
END

Copy Code

C#

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 DbType, 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>
Paging gets the list of data and total rows
</summary>
<param name= "tbname" > table name </param>
<param name= "Tbgetfields" > Return field </param>
<param name= "Orderfldname" > sorted field names </param>
<param name= "PageSize" > Page size </param>
<param name= "PageIndex" > Page </param>
<param name= "OrderType" >false Ascending, True descending </param>
<param name= "strwhere" > Query conditions </param>
public static DataSet Getpagelist (String tbname, String tbgetfields, string orderfldname, int PageSize, int PageIndex, str ing 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");
}


       

Copy Code

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];
        }

Copy Code

Note: The place to be aware of when connecting multiple tables
1. Required Fields: Tbname,orderfldname,tbgetfields
2. 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,asc|u.name,desc" (Format: NAME,ASC|ID,DESC)
Strwhere: Must be added before each condition (for example: and userinfo.depid=1)

Max/top: (Simply write down, need to satisfy the primary key field name is "id")

CREATE proc [dbo]. [Spsqlpagebymaxtop]
@tbName varchar (255),--table name
@tbFields varchar (1000),--return field
@PageSize int,--page size
@PageIndex int,--page
@strWhere varchar (1000),--Query condition
@StrOrder varchar (255),--Sort condition
@Total int output--Returns the total number of records
As
DECLARE @strSql varchar (5000)--the subject sentence
declare @strSqlCount nvarchar (500)--Query the total number of records the subject sentence

Total number of records-----------------------------
If @strWhere! = "
Begin
Set @strSqlCount = ' Select @TotalCout =count (*) from ' + @tbName + ' where ' + @strWhere
End
Else
Begin
Set @strSqlCount = ' Select @TotalCout =count (*) from ' + @tbName
End
--------------Paging------------
If @PageIndex <= 0
Begin
Set @PageIndex = 1
End

Set @strSql = ' Select Top ' +str (@PageSize) + ' * from ' + @tbName + '
Where id> (select Max (ID) from (select Top ' +str ((@PageIndex-1) * @PageSize) + ' ID from ' + @tbName + ' [email protected]+ ' A
' [email protected]+ ']

EXEC sp_executesql @strSqlCount, N ' @TotalCout int output ', @Total output
EXEC (@strSql)

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

Call:

DECLARE @count int


Select @count

Several SQL notation for querying paging