AD

SQL Server的通用分页存储过程 未使用游标,速度更快!

使用SQL Server时,分页处理一直是个比较棘手的问题

正常情况下,SQL Server服务器上会对使用频率大的Table建立合适的索引
这样能大幅度的提高数据库本身的数据检索速度,建立索引的方法就不细说了

如果需要返回大量数据,从几百行到几万行,甚至几十万行数据
这时会发现响应速度越来越慢,甚至发生响应超时的错误
为了解决这种大数据量请求的问题,就不得不使用分页模式了

在这方面,JDBC就强悍得多,它可以将指定的行数和SQL请求一并发送给SQL Server,这样只返回分页后的数据,JDBC的原理还不清楚,但在实际使用中,速度还是非常快的

如果没办法使用JDBC,最常用的方法就是存储过程了!

我在写这个分页存储之前,参考了网上的大量相关文章,可以通过关键字:SQL Server 分页 进行搜索
他们主要都是利用SQL中的Top方法,并且对所检索的数据结构要求有标识列,如果没有标识列,或者是联合主键,那么就会非常麻烦了。而且对应用里原有的SQL检索部分需要修改的地方较多,工作量较大。

因此,我在写这个存储之前就要求一定要对原有的SQL脚本最大程度的兼容

经过一个下午的时间,和我一个同事(绝对是高手)的共同努力下,摸索出了以下的思路:

1、确定存储的输入参数:
1)SQL脚本,该参数接收完整的、正确的SQL检索文本,可将原应用中写好的SQL脚本直接传入
2)每页的数据容量,就是一页有多少条数据
3)当前页码
2、确定分页机制:
1)执行传入的SQL脚本,并将结果生成临时表
2)修改临时表的结构,增加标识列字段
3)根据标识列字段,计算出指定页码内的记录范围,并返回
4)返回总数据条数,用于客户端进行分页显示

根据以上的思路,编写出以下通用的分页存储过程:

[code]
--// ============================
--// SQL Server通用分页存储过程
--// Author : netwild
--// date : 2010/07/22
--// Email : [email protected]
--// QQ : 52100641(网无忌)
--// ============================ 

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO 

CREATE PROC execByPage 

@sqlQuery varchar(2000), --//输入参数:SQL检索语句或表名
@pageSize int, --//输入参数:每页显示记录条数
@pageIndex int --//输入参数:当前页码 

AS 

SET NOCOUNT ON
SET ANSI_WARNINGS OFF 

declare @tmpTableName varchar(50)
set @tmpTableName = '##TB1516_' + replace(cast(newid() as varchar(40)),'-','') --//生成随机临时表名称 

declare @subIndex int
set @subIndex = charindex('from',@sqlQuery)
if (@subIndex > 0)
begin --//带FROM的标准检索语句
declare @sqlQuery1 varchar(2000)
declare @sqlQuery2 varchar(2000)
set @sqlQuery1 = substring(@sqlQuery,1,@subIndex - 1)
set @sqlQuery2 = substring(@sqlQuery,@subIndex,len(@sqlQuery))
set @sqlQuery = @sqlQuery1 + ',IDENTITY(numeric,1,1) as ID1516 into ' + @tmpTableName + ' ' + @sqlQuery2
end
else --//不带FROM的表名
begin
set @sqlQuery = 'select *,IDENTITY(numeric,1,1) as ID1516 into ' + @tmpTableName + ' from' + @sqlQuery
end
exec(@sqlQuery) --//建立并初始化临时表数据 

declare @indexStart varchar(20),@indexEnd varchar(20)
set @indexStart = cast((@pageIndex-1)*@pageSize+1 as varchar(20)) --//数据起始行ID
set @indexEnd = cast(@pageIndex * @pageSize as varchar(20)) --//数据结束行ID 

exec('select * from ' + @tmpTableName + ' where ID1516 between ' + @indexStart + ' and ' + @indexEnd) --//检索该页数据 

exec('select max(ID1516) as recordCount from ' + @tmpTableName) --//提取总条数 

exec('drop table ' + @tmpTableName) --//删除临时表 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

[/code]

标签: 存储过程, 分页
分类: MsSql
时间: 2014-06-03

相关文章

  1. SQL Server 2005通用分页存储过程及多表联接应用

    分页存储过程在好多文章中都有介绍过:本篇是不久前写的一个分页存储过程,可应用于SQL Server 2005上面,感兴趣的朋友可以研究下,希望本文对你学习存储过程有所帮助 这是不久前写的一个分页存储过程,可应用于SQL ...
  2. 深入sql server 2005 万能分页存储过程的详解

    本篇文章是对sqlserver2005中的万能分页存储过程进行了详细的分析介绍,需要的朋友参考下 ?建立主表临时表CREATE TABLE #temp ( rownumber bigint, orderseqno VAR ...
  3. 基于Sql Server通用分页存储过程的解决方法

    本篇文章,小编将为大家介绍基于Sql Server通用分页存储过程的解决方法,有需要的朋友可以参考一下 sql server2000分页方式很多,效率吗 当到达10万级以上就有些吃力了,这里暂时不例出来了 sql ser ...
  4. SQL Server两种分页的存储过程使用介绍

    这篇文章主要介绍了SQL Server两种分页的存储过程的使用,需要的朋友可以学习下 由于现在很多的企业招聘的笔试都会让来招聘的写一个分页的存储过程,有的企业甚至要求应聘者用两种方式实现分页,如果没有在实际项目中使用过分 ...
  5. 通用分页存储过程,源码共享,大家共同完善

    好久没有上来写点东西了,今天正好有空,共享一些个人心得,就是关于分页的存储过程,这个问题应该是老生重谈了,网上的通用存储过程的类型已经够多了,但是,好象看到的基本上不能够满足一些复杂的SQL语句的分页(也可能是我不够见多 ...
  6. sqlserver 通用分页存储过程

    sqlserver 通用分页存储过程,用存储过程可以提高效率与节约时间. create proc commonPagination @columns varchar(500), --要显示的列名,用逗号隔开 @table ...
  7. sql server 常用的系统存储过程

    系统存储过程 说明 sp_databases 列出服务上的所有数据库 sp_helpdb 报告有关指定数据库或所有数据库的信息 sp_renamedb 更改数据库的名称 sp_tables 返回当前环境下可查询的对象的列 ...
  8. SQL Server中通过扩展存储过程实现数据库的远程备份与恢复

    SQL Server中通过扩展存储过程实现数据库的远程备份与恢复实现方法,需要的朋友可以参考下 本文通过实例解析了 SQL Server 数据库扩展存储过程,实现远程备份与恢复的方法和步骤 实例说明: 环境:win2k+ ...
  9. SQL Server数据库中的存储过程介绍

    这篇文章主要介绍了SQL Server数据库中的存储过程介绍,本文讲解了什么是存储过程.存储过程语法.存储过程的一些使用实例等内容,需要的朋友可以参考下 什么是存储过程 如果你接触过其他的编程语言,那么就好理解了,存储过 ...
  10. [数据库] 通用分页存储过程第1/5页

    分页存储过程共有四种方式可以实现,行计数.游标.升序-降序.子查询 我记得曾经有人测试过这四种方式的效率分别是 从性能最好到最差的顺序进行的--行计数.游标.升序-降序.子查询 以下是我收集的一些资料供大家参考 QUOT ...
  11. SQL SERVER数据库开发之存储过程应用

    可能有不少朋友使用SQL SERVER做开发也已经有段日子,但还没有或者很少在项目中使用存储过程,或许有些朋友认为根本没有必要使用存储过程等等.其实当你一个项目做完到了维护阶段时,就会发现存储过程给我们带来了好处了,修改 ...
  12. 在SQL Server 20005中修改存储过程

    初次使用SQL Server 20005时,在修改了存储过程后竟然不知道如何保存. 我在SQL Server Management Studio的Progammability中找到相应的存储过程,点击右键并选择" ...
  13. 一个基于ROW_NUMBER()的通用分页存储过程代码

    项目中有很多小型的表(数据量不大),都需要实现分页查询,因此实现了一个通用的分页. 建立好如下的存储过程,以后要分页,直接调用改存储过程就可以了. 注意:数据量大.性能要求高的,请个性化处理. ALTER PROCEDU ...
  14. SqlServer 2000.2005分页存储过程整理第1/3页

    这篇文章主要介绍了SqlServer 2000.2005分页存储过程整理,本文给出了4个实现版本,还给出二分分页过程,需要的朋友可以参考下 sql server 2005的分页存储过程分3个版本,一个是没有优化过的,一个 ...
  15. 防SQL注入 生成参数化的通用分页查询语句

    前些时间看了玉开兄的"如此高效通用的分页存储过程是带有sql注入漏洞的"这篇文章,才突然想起某个项目也是使用了累似的通用分页存储过程. 使用这种通用的存储过程进行分页查询,想要防SQL注入,只能对输入 ...
  16. SQL Server常用存储过程及示例

    以下是对SQL Server中常用的存储过程进行了介绍.需要的朋友可以过来参考下 分页: /*分页查找数据*/ CREATE PROCEDURE [dbo].[GetRecordSet] @strSql varchar( ...
  17. SQL Server存储过程的基础说明

    创建存储过程,存储过程是保存起来的可以接受和返回用户提供的参数的 Transact-SQL 语句的集合. 可以创建一个过程供永久使用,或在一个会话中临时使用(局部临时过程),或在所有会话中临时使用(全局临时过程). 也可 ...
  18. 在SQL Server启动时自动执行存储过程.第1/2页

    当sql server启动时,我很想运行一个存储过程.有没有一种方法可以在每次SQL Server服务启动时都会自动执行这个存储过程呢? sql Server提供了系统存储过程sp_procoption,这个存储过程可以 ...
  19. SQL Server在存储过程中编写事务处理代码的三种方法

    SQL Server中数据库事务处理是相当有用的,鉴于很多SQL初学者编写的事务处理代码存往往存在漏洞,本文我们介绍了三种不同的方法,举例说明了如何在存储过程事务处理中编写正确的代码.希望能够对您有所帮助. 在编写SQL ...