SQL Server paging Query stored procedures

1. Inefficient paging

　　

But there is a fatal drawback to this stored procedure, which is that it contains the not-in typeface. Although I can transform it into:

　　

That is, using not exists instead of not in, but we have already talked about, the implementation efficiency of the two is actually no difference.

2. Efficient Paging

Select Top Page Size * from table1 where id> (       select Max (ID) from       (select Top ((page 1) * page size) ID from table1 ORDER by I d) as T        )       

　　

Almost any field can fetch the largest or smallest value in a field by using Max (field) or min (field), so if this field is not duplicated, you can use the max or min of these non-repeating fields as a watershed to make it a reference for separating each page in the paging algorithm. Here, we can use the operator ">" or "<" to accomplish this mission, so that the query statement conforms to SARG form.

3. Efficient Paging stored procedures

--Get data for the specified page CREATE PROCEDURE pagination3 @tblName varchar (255),--table name @strGetFields varchar (1000) = ' * ',--return Column @fldName varchar (255) = ',--the field name of the sort @PageSize int = 10,--page size @PageIndex int = 1,--page number @doC Ount bit = 0,--Returns the total number of records, not 0 returns @OrderType bit = 0,--set sort type, non 0 value descending @strWhere varchar (1500) = '--Query condition (NOTE:  Do not add where) as declare @strSQL varchar (5000)--Declare @strTmp varchar (110)--Temporary variable declare @strOrder     VARCHAR (400)--Sort Type--if @docount pass over 0, the total count is counted if @doCount! = 0 BEGIN--splicing WHERE clause if @strWhere! = "  Set @strSQL = "SELECT count (*) as Total from [" + @tblName + "] where" [email protected] Else Set @strSQL = "SELECT COUNT (*) as Total from [" + @tblName + "]" End else begin--sort type, ordertype!=0 descending if @OrderType! = 0 BEGIN-    -< (select min and the following > (select max) are key to this stored procedure.    --Use Max or min of the non-repeating field as a watershed, making it a reference for separating each page in the paging algorithm. --Here, you can use the operator ">" or "<" to accomplish this mission, so that the query statement conforms to the Sarg form   --If you cannot limit the scope of the search by using a statement that does not satisfy the Sarg form, or not exist, SQL Server must determine for each row whether it satisfies all the conditions in the WHERE clause.    --an index is useless for expressions that do not satisfy the Sarg form. Set @strTmp = "< (select Min" Set @strOrder = "ORDER BY [" + @fldName + "] desc"--if @ordertype is not 0, it is important to perform descending order!  End ELSE BEGIN set @strTmp = "> (select Max" Set @strOrder = "ORDER BY [" + @fldName + "] ASC" End If @PageIndex = 1 BEGIN if @strWhere! = ' Set @strSQL = "SELECT top" + str (@PageSize) + "" [email protected]+] from [ "+ @tblName +"] where "+ @strWhere +" "+ @strOrder Else Set @strSQL =" SELECT top "+ str (@PageSize) +" "[E mail protected]+ "from [" + @tblName + "]" + @strOrder--executes the above code on the first page, which speeds up the execution speed end else Begin--The following code gives @strsql a real  The line's SQL code set @strSQL = "SELECT top" + str (@PageSize) + "" [email protected]+] from ["+ @tblName +"] where ["+ @fldName + "]" + @strTmp + "([" + @fldName + "]) from (select top" + str ((@PageIndex-1) * @PageSize) + "[" + @fldName + "] from ["+ @tblName +"] "+ @sTrorder + ") as Tbltmp)" + @strOrder if @strWhere! = ' Set @strSQL = "SELECT top" + str (@PageSize) + "" [EMAIL&NBSP;PR otected]+ "from [" + @tblName + "] where [" + @fldName + "]" + @strTmp + "([" + @fldName + "]) from (SEL          ECT Top "+ STR ((@PageIndex-1) * @PageSize) +" ["+ @fldName +"] from ["+ @tblName +"] where "+ @strWhere +" "  + @strOrder + ") as Tbltmp) and" + @strWhere + "" + @strOrder End end EXEC (@strSQL) go

　　

4.SARG

Sarg definition: Used to limit the search to an operation, because it usually refers to a specific match, a worthy range of matching or more than two conditions and connection.

The form is as follows:

Column name operators < constants or variables > or < constants or variables > operator column names

Column names can appear on one side of the operator, while constants or variables appear on the other side of the operator. Such as:

Name= ' Zhang San '

Price >5000

5000< Price

Name= ' Zhang San ' and price >5000

If an expression does not meet the form of sarg, it cannot limit the scope of the search, which means that SQL Server must determine for each row whether it satisfies all the conditions in the WHERE clause. So an index is useless for an expression that does not satisfy the Sarg form.

After the introduction of Sarg, we will summarize the experience of using SARG and the conclusions of certain materials encountered in practice:

1) Whether a like statement belongs to Sarg depends on the type of wildcard you are using

such as: Name like ' Zhang% ', which belongs to Sarg

And: Name like '% Zhang ', does not belong to Sarg.

The reason is that the wildcard% is opened in the string so that the index is unusable.

2) or will cause a full table scan

Name= ' Zhang San ' and price >5000 symbol SARG, while: Name= ' Zhang San ' or price >5000 does not conform to SARG. Using or causes a full table scan.

3) Non-operator, function-induced statements that do not satisfy the Sarg form

The most typical case of a statement that does not satisfy the Sarg form is a statement that includes non-operators, such as not,! =, <>,!<,!>, not-EXISTS, not-in, not-like, and also functions. Here are a few examples that do not satisfy the Sarg form:

ABS (Price) <5000

Name like '% three '

Some expressions, such as:

WHERE Price *2>5000

SQL Server will also assume that Sarg,sql server will convert this type to:

WHERE Price >2500/2

However, we do not recommend this, because sometimes SQL Server does not guarantee that this conversion is completely equivalent to the original expression.

SQL Server paging Query stored procedures