SQL Server 分页查询存储过程

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了SQL Server 分页查询存储过程，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5123字，纯文字阅读大概需要8分钟。

内容图文

SELECT TOP m-n+1 * 
FROM publish 
WHERE (id NOT IN 
　　　　(SELECT TOP n-1 id 
　　　　 FROM publish)) 

　　

但这个存储过程有一个致命的缺点，就是它含有NOT IN字样。虽然我可以把它改造为：

SELECT TOP 页大小 * 
FROM Table1 
WHERE not exists 
(select * from (select top (页大小*页数) * from table1 order by id) b where b.id=a.id ) 
order by id 

　　

即，用not exists来代替not in，但我们前面已经谈过了，二者的执行效率实际上是没有区别的。

 

2.高效的分页

select top 页大小 * from table1 
where id> 
      (select max (id) from 
      (select top ((页码-1)*页大小) id from table1 order by id) as T 
       )     
  order by id 

　　

几乎任何字段，都可以通过max(字段)或min(字段)来提取某个字段中的最大或最小值，所以如果这个字段不重复，那么就可以利用这些不重复的字段的max或min作为分水岭，使其成为分页算法中分开每页的参照物。在这里，我们可以用操作符“>”或“<”号来完成这个使命，使查询语句符合SARG形式。

3.高效的分页存储过程

-- 获取指定页的数据 
CREATE PROCEDURE pagination3 
@tblName   varchar(255),       -- 表名 
@strGetFields varchar(1000) = ‘*‘,  -- 需要返回的列 
@fldName varchar(255)=‘‘,      -- 排序的字段名 
@PageSize   int = 10,          -- 页尺寸 
@PageIndex  int = 1,           -- 页码 
@doCount  bit = 0,   -- 返回记录总数, 非 0 值则返回 
@OrderType bit = 0,  -- 设置排序类型, 非 0 值则降序 
@strWhere  varchar(1500) = ‘‘  -- 查询条件 (注意: 不要加 where) 
AS 
declare @strSQL   varchar(5000)       -- 主语句 
declare @strTmp   varchar(110)        -- 临时变量 
declare @strOrder varchar(400)        -- 排序类型 

--如果@doCount传递过来的不是0，就执行总数统计
if @doCount != 0 
  begin 
    --拼接where 子句
    if @strWhere !=‘‘ 
    set @strSQL = "select count(*) as Total from [" + @tblName + "] where "+@strWhere 
    else 
    set @strSQL = "select count(*) as Total from [" + @tblName + "]" 
end  
else 
begin 
--排序的类型, OrderType!=0 降序
if @OrderType != 0 
begin 
    --<(select min 和下文的>(select max 是这个存储过程的关键。
    --利用不重复的字段的max或min作为分水岭，使其成为分页算法中分开每页的参照物。
    --在这里，可以用操作符“>”或“<”号来完成这个使命，使查询语句符合SARG形式
    --若用使用不满足SARG形式的语句not in 或not exist 就无法限制搜索的范围了,SQL SERVER必须对每一行都判断它是否满足WHERE子句中的所有条件。
    --一个索引对于不满足SARG形式的表达式来说是无用的.
    set @strTmp = "<(select min" 
set @strOrder = " order by [" + @fldName +"] desc" 
--如果@OrderType不是0，就执行降序，这句很重要！ 
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) +" "+@strGetFields+ "  from [" + @tblName + "] where " + @strWhere + " " + @strOrder 
     else 
     set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "  from ["+ @tblName + "] "+ @strOrder 
--如果是第一页就执行以上代码，这样会加快执行速度 
end 
else 
begin 
--以下代码赋予了@strSQL以真正执行的SQL代码 
set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "  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) +" "+@strGetFields+ "  from [" 
        + @tblName + "] where [" + @fldName + "]" + @strTmp + "([" 
        + @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " [" 
        + @fldName + "] from [" + @tblName + "] where " + @strWhere + " " 
        + @strOrder + ") as tblTmp) and " + @strWhere + " " + @strOrder 
end 
end   
exec (@strSQL) 
go 

　　

4.SARG

SARG的定义：用于限制搜索的一个操作，因为它通常是指一个特定的匹配，一个值得范围内的匹配或者两个以上条件的AND连接。

形式如下：  

列名 操作符 <常数 或 变量>或<常数 或 变量> 操作符列名 

列名可以出现在操作符的一边，而常数或变量出现在操作符的另一边。如：  

Name=’张三’  

价格>5000 

5000<价格 

Name=’张三’ and 价格>5000  

        如果一个表达式不能满足SARG的形式，那它就无法限制搜索的范围了，也就是SQL SERVER必须对每一行都判断它是否满足WHERE子句中的所有条件。所以一个索引对于不满足SARG形式的表达式来说是无用的。  

介绍完SARG后，我们来总结一下使用SARG以及在实践中遇到的和某些资料上结论不同的经验：  

  1) Like语句是否属于SARG取决于所使用的通配符的类型  

如：name like ‘张%’ ，这就属于SARG  

而：name like ‘%张’ ,就不属于SARG。  

原因是通配符%在字符串的开通使得索引无法使用。  

  2) or 会引起全表扫描  

Name=’张三’ and 价格>5000 符号SARG，而：Name=’张三’ or 价格>5000 则不符合SARG。使用or会引起全表扫描。  

  3)非操作符、函数引起的不满足SARG形式的语句  

       不满足SARG形式的语句最典型的情况就是包括非操作符的语句，如：NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等，另外还有函数。下面就是几个不满足SARG形式的例子：  

ABS(价格)<5000  

Name like ‘%三’  

有些表达式，如：  

WHERE 价格*2>5000  

SQL SERVER也会认为是SARG，SQL SERVER会将此式转化为：  

WHERE 价格>2500/2  

但我们不推荐这样使用，因为有时SQL SERVER不能保证这种转化与原始表达式是完全等价的。

SQL Server 分页查询存储过程

标签：经验   不同   bit   尺寸   表达式   参照物   高效   size   符号   

本文系统来源：http://www.cnblogs.com/mojo/p/7400084.html

内容总结

以上是互联网集市为您收集整理的SQL Server 分页查询存储过程全部内容，希望文章能够帮你解决SQL Server 分页查询存储过程所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---