sql2008存储过程分页
ASP.Net中的DataGrid有内置分页功能,
但是它的默认的分页方式效率是很低的,特别是在数据量很大的时候,用它内置的分页功能几乎是不可能的事,因为它会把所有的数据从数据库读出来再进行分页,
这种只选取了一小部分而丢掉大部分的方法是不可去取的.
在最进的一个项目中因为一个管理页面要管理的数据量非常大,所以必须分页显示,并且不能用DataGrid的内置分页功能,于是自己实现分页.
下面介绍一下我在项目中用到的分页方法.
当然显示控件还是用DataGrid的,
因为数据绑定很方便^_^.
要保证不传输冗余的数据,那么必须在数据库中数据读取时实现分页,
数据库的分页操作可以放在存储过程中.
看了CSDN的一篇Blog中讲了一个百万级数据分页的存储过程的实现(
http://blog.csdn.net/wellknow/posts/55167.aspx
，他的这个方法可以根据不同情况进行适当的优化),
根据他的方法,这里实现一个简单的SQL语句来实现这里分页需要的存储过程。
create
procere
ListProct
(
@PageIndex
int,
--
分页后需要页的序号
@PageSize
int,
--
一页的大小
@ConditionSQL
–
查询条件的SQL语句
)
AS
…
具体代码就不写了(可以参考上面的链接).
具体的SQL语句如下:
Select
TOP
100
*
FROM
(select
*
from
proct
where
proctid<200000)
T
Where
T.proctid
NOT
IN
(Select
TOP
900
proctid
FROM
(select
proctid
from
proct
where
proctid<200000)
T1
orDER
BY
T1.proctid
asc)
orDER
BY
proctid
asc
这条语句的
从总的商品(30万)中取出proctid<200000(共20万),再按每页100的大小分页,然后取出第10页.
Public
DataTable
ListProct(int
pageIndex,
int
pageSize)
{
//ADO.net从数据库中取出数据的代码就略过^_^.
}
用上面的存储过程读出的数据在DataGrid里面分页,
必须把DataGrid的AllowPaging和AllowCustomPaging设置为true
protected
System.Web.UI.WebControls.DataGrid
ProctGrid;
ProctGrid.AllowPaging
=
true;
ProctGrid.AllowCustomPaging
=
true;
然后在设置要显示的一页的大小
ProctGrid.PageSize
=
100;
//
在显示的时候依据实际的数据显示。
设置一页大小后,如果要让DataGrid实际分出页数来,还必须设置
ProctGrid.VirtualItemCount
=
GetProctCount()
;
//
GetProctCount()
的功能是获取满足条件的产品数目,
这里的条件就是proctid<200000.
设置这项属性后,那么这个DataGrid的页数就是
VirtualItemCount/PageSize,
也就是PageCount的值.
不能对PageCount直接赋值,因为他是只读的属性.
② sql存储过程分页 临时表问题
sql分页不一定用存储过程,就过个人认为存储过程尽量少用!
--前提是必需有一列是自动增长类型,唯一性
--方法一
SELECT DISTINCT TOP 8 CategoryID
FROM tbl_Proct_Procts
WHERE (UserID = 73) AND (CategoryID >
(SELECT MAX(categoryid)
FROM (SELECT DISTINCT TOP 16 categoryid
FROM tbl_proct_procts where userid=73
ORDER BY categoryid) AS b))
ORDER BY CategoryID
--方法二
select top 10 * from [order details]
where orderid>all(select top 10 orderid from [order details] order by orderid)
order by orderid
下面的来自CSDN-------------------------------------
SQL Server 存储过程的分页，这个问题已经讨论过几年了，很多朋友在问我，所以在此发表一下我的观点
建立表：
CREATE TABLE [TestTable] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[FirstName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[LastName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[Country] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Note] [nvarchar] (2000) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
插入数据：(2万条，用更多的数据测试会明显一些)
SET IDENTITY_INSERT TestTable ON
declare @i int
set @i=1
while @i<=20000
begin
insert into TestTable([id], FirstName, LastName, Country,Note) values(@i, 'FirstName_XXX','LastName_XXX','Country_XXX','Note_XXX')
set @i=@i+1
end
SET IDENTITY_INSERT TestTable OFF
-------------------------------------
分页方案一：(利用Not In和SELECT TOP分页)
语句形式：
SELECT TOP 10 *
FROM TestTable
WHERE (ID NOT IN
(SELECT TOP 20 id
FROM TestTable
ORDER BY id))
ORDER BY ID
SELECT TOP 页大小 *
FROM TestTable
WHERE (ID NOT IN
(SELECT TOP 页大小*页数 id
FROM 表
ORDER BY id))
ORDER BY ID
-------------------------------------
分页方案二：(利用ID大于多少和SELECT TOP分页）
语句形式：
SELECT TOP 10 *
FROM TestTable
WHERE (ID >
(SELECT MAX(id)
FROM (SELECT TOP 20 id
FROM TestTable
ORDER BY id) AS T))
ORDER BY ID
SELECT TOP 页大小 *
FROM TestTable
WHERE (ID >
(SELECT MAX(id)
FROM (SELECT TOP 页大小*页数 id
FROM 表
ORDER BY id) AS T))
ORDER BY ID
-------------------------------------
分页方案三：(利用SQL的游标存储过程分页)
create procere XiaoZhengGe
@sqlstr nvarchar(4000), --查询字符串
@currentpage int, --第N页
@pagesize int --每页行数
as
set nocount on
declare @P1 int, --P1是游标的id
@rowcount int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
select ceiling(1.0*@rowcount/@pagesize) as 总页数--,@rowcount as 总行数,@currentpage as 当前页
set @currentpage=(@currentpage-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@currentpage,@pagesize
exec sp_cursorclose @P1
set nocount off
其它的方案：如果没有主键，可以用临时表，也可以用方案三做，但是效率会低。
建议优化的时候，加上主键和索引，查询效率会提高。
通过SQL 查询分析器，显示比较：我的结论是:
分页方案二：(利用ID大于多少和SELECT TOP分页）效率最高，需要拼接SQL语句
分页方案一：(利用Not In和SELECT TOP分页) 效率次之，需要拼接SQL语句
分页方案三：(利用SQL的游标存储过程分页) 效率最差，但是最为通用
在实际情况中，要具体分析。
更多的讨论见：
http://community.csdn.net/Expert/topic/3292/3292678.xml?temp=.1621515
③ （问题解决再追加100分）sql server存储过程实现查询数据条数过大，分页查询怎么实现
按说5-8w这样数量级的数据没有问题，写入Excel是布比较耗性能，主要还是要通过优化写入Excel的代码效率上去考虑。你可以考虑利用分批查询写入的方式来避免一次写太多的数据到Excel：将你的查询结果分段，比方你的语句中能不能用时间来认为分段，每次返回部分结果。
回到你的问题，对大数据量查询的解决方案有以下两种：
（1）、将全部数据先查询到内存中，然后在内存中进行分页，这种方式对内存占用较大，必须限制一次查询的数据量。
（2）、采用存储过程在数据库中进行分页，这种方式对数据库的依赖较大，不同的数据库实现机制不通，并且查询效率不够理想。以上两种方式对用户来说都不够友好。
2．解决思路
通过在待查询的数据库表上增加一个用于查询的自增长字段，然后采用该字段进行分页查询，可以很好地解决这个问题。下面举例说明这种分页查询方案。
（1）、在待查询的表格上增加一个long型的自增长列，取名为“queryId”,mssql、sybase直接支持自增长字段，oracle可以用sequence和trigger来实现。然后在该列上加上一个索引。
添加queryId列的语句如下：
Mssql: [QUERYID] [bigint] IDENTITY (1, 1)
Sybase: QUERYID numeric(19) identity
Oracle:
CREATE SEQUENCE queryId_S
INCREMENT BY 1
START WITH 1
MAXVALUE 999999999999999 MINVALUE 1
CYCLE
CACHE 20
ORDER;
CREATE OR REPLACE TRIGGER queryId_T BEFORE INSERT
ON "test_table"
FOR EACH ROW
BEGIN
select queryId_S.nextval into :new.queryId from al;
END;
（2）、在查询第一页时，先按照大小顺序的倒序查出所有的queryId，
语句如下：select queryId from test_table where + 查询条件 +order by queryId desc 。
因为只是查询queryId字段，即使表格中的数据量很大，该查询也会很快得到结果。然后将得到的queryId保存在应用服务器的一个数组中。
（3）、用户在客户端进行翻页操作时，客户端将待查询的页号作为参数传递给应用服务器，服务器通过页号和queyId数组算出待查询的queyId最大和最小值，然后进行查询。
算出queyId最大和最小值的算法如下,其中page为待查询的页号，pageSize为每页的大小，queryIds为第二步生成的queryId数组：
int startRow = (page - 1) * pageSize
int endRow = page * pageSize - 1;
if (endRow >=queryIds.length)
{
endRow = this.queryIds.length - 1;
}
long startId =queryIds[startRow];
long endId =queryIds[endRow];
查询语句如下：
String sql = "select * from test_table" + 查询条件 + "(queryId <= " + startId + " and queryId >= " + endId + ")";
3．效果评价
该分页查询方法对所有数据库都适用，对应用服务器、数据库服务器、查询客户端的cpu和内存占用都较低，查询速度较快，是一个较为理想的分页查询实现方案。经过测试，查询4百万条数据，可以在3分钟内显示出首页数据，以后每一次翻页操作基本在2秒以内。内存和cpu占用无明显增长。
以上也仅仅是分页查询结果查看的问题，你需要写入到Excel的话还需要考虑Excel写入代码的执行效率，这部分是很值得研究的。
④ SQL里存储过程分页问题！急！急！
CREATE PROCEDURE [Zhzuo_GetItemsPage]
@PageIndex INT, /*@PageIndex从计数,0为第一页*/
@PageSize INT, /*页面大小*/
@RecordCount INT OUT, /*总记录数*/
@PageCount INT OUT /*页数*/
AS
/*获取记录数*/
SELECT @RecordCount = COUNT(*) FROM Proction.Proct
/*计算页面数据*/
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
/*TOP记录数*/
DECLARE @TOPCOUNT INT
SET @TOPCOUNT = @RecordCount - @PageSize * @PageIndex
DECLARE @SQLSTR NVARCHAR(1000)
IF @PageIndex = 0 OR @PageCount <= 1
BEGIN
SET @SQLSTR =N'SELECT TOP '+STR(@PageSize)+
'ProctID,Name FROM Proction.Proct ORDER BY ProctID DESC'
END
ELSE
BEGIN
IF @PageIndex = @PageCount - 1
BEGIN
SET @SQLSTR =N'SELECT * FROM ( SELECT TOP ' + STR(@TOPCOUNT) +
'ProctID,Name FROM Proction.Proct ORDER BY ProctID ASC) T ORDER BY ProctID DESC'
END
ELSE
BEGIN
SET @SQLSTR =N' SELECT TOP '+STR(@PageSize)+'* FROM (SELECT TOP ' + STR(@TOPCOUNT) +
'ProctID,Name FROM Proction.Proct ORDER BY ProctID ASC) T ORDER BY ProctID DESC'
END
END
/*执行*/
EXEC (@SQLSTR)
以上存储过程对页数进行判断，如果是第一页或最后一页，进行特殊处理。其他情况使用2次TOP翻转。其中排序条件为ProctID倒序。最后通过EXECUTE执行SQL字符串拼串。
2．SQL SERVER 2005中的TOP分页
CREATE PROCEDURE [dbo].[Zhzuo_GetItemsPage2005TOP]
@PageIndex INT,
@PageSize INT,
@RecordCount INT OUT,
@PageCount INT OUT
AS
/*获取记录数*/
SELECT @RecordCount = COUNT(*) FROM Proction.Proct
/*计算页面数据*/
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
/*TOP记录数*/
DECLARE @TOPCOUNT INT
SET @TOPCOUNT = @RecordCount - @PageSize * @PageIndex
/*基于SQL SERVER 2005 */
IF @PageIndex = 0 OR @PageCount <= 1
BEGIN
SELECT TOP(@PageSize) ProctID,Name FROM Proction.Proct ORDER BY ProctID DESC
END
ELSE
BEGIN
IF @PageIndex = @PageCount - 1
BEGIN
SELECT * FROM ( SELECT TOP(@TOPCOUNT) ProctID,Name FROM Proction.Proct ORDER BY ProctID ASC) T
ORDER BY ProctID DESC
END
ELSE
BEGIN
SELECT TOP(@PageSize) * FROM (SELECT TOP(@TOPCOUNT) ProctID,Name FROM Proction.Proct ORDER BY ProctID ASC) T
ORDER BY ProctID DESC
END
END
以上存储过程是使用2005的TOP (表达式) 新功能，避免了字符串拼串，使结构化查询语言变得简洁。实现的为同样的功能。
3．SQL SERVER 2005中的新分页
CREATE PROCEDURE [dbo].[Zhzuo_GetItemsPage2005]
@PageIndex INT,
@PageSize INT,
@RecordCount INT OUT,
@PageCount INT OUT
AS
/*获取记录数*/
SELECT @RecordCount = COUNT(*) FROM Proction.Proct
/*计算页面数据*/
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
/* 基于SQL SERVER 2005 */
SELECT SerialNumber,ProctID,Name FROM
(SELECT ProctID,Name,ROW_NUMBER() OVER (ORDER BY ProctID DESC) AS SerialNumber FROM Proction.Proct ) AS T
WHERE T.SerialNumber > (@PageIndex * @PageSize) and T.SerialNumber <= ((@PageIndex+1) * @PageSize)
第三个存储过程使用2005下新的功能，实现的分页存储过程功能更加简单明了，而且更加容易理解。注意这里的ProctID为主键,根据ProctID进行排序生成ROW_NUMBER，通过ROW_NUMBER来确定具体的页数。
通过对三个分页存储过程的比较，可见SQL SERVER 的TSQL 语言对分页功能的支持进步不少。使分页实现趋向于简单化。
⑤ 数据库sqlserver如何用存储过程做分页
存储过程：create Procere pname
( @pageIndex int，@pageSize)
as
select * from tableName order by id
offset @pageIndex * pageSize fetch next pageSize rows only
分页：
sqlserver 在2008之前 使用 top 和 not int top 的方式来做分页
2008以后使用 row_number() 函数作为分页关键函数
2012使用 offset 1 fetch next 10 rows only
你问了2个问题，你可以优先把视图，存储过程，触发器等弄明白，分页是查询，在存储过程里可以写复杂的sql文，只是在运行时是预编译和参数化查询防止sql注入
⑥ sql server 存储过程分页
首次加载时根据分页显示全部数据，先求共有多少数据，再根据每页显示条数求出每页显示多少条到多少 条（例如：pageCount=10, pageStart=pageCount*(page-1)+1; pageEnd=pageCount*page）page是显示第几页。
根据分类差不多，只不过根据查询条件查询出所有符合条件的数据，在根据row_number() over(order by '字段名+' '+ 排序方式+') rowNum，得到符合条件的所有数据
例如:
CREATE PROC 存储过程名
@leibie
@page int,--页码
@size int,--页大小
AS
DECLARE @sql varchar(max)
SET @sql='
SET @sql+='SELECT COUNT(*) [count] FROM('
SET @sql+='SELECT * FROM('
SET @sql+='
SELECT ROW_NUMBER() OVER( ORDER BY 字段名 ASC ) Row ,*
FROM 表名 where+条件
SET @sql+='
) T
) TT
'
SET @sql+='WHERE Row>'+CONVERT(varchar(10),(@page-1)*@size)+' AND Row<'+CONVERT(varchar(10),@page*@size+1)
'
PRINT @sql
EXEC(@sql)
⑦ SQL分页与存储过程分页是一样的吗
它们是不一样的。
SQL分页：
SQL数据是以页为单位存储在文件中的，每个页面8KB。当往一个页面中插入或者修改数据并会导致该页面的数据会超出8KB时，SQL Server就会将页面拆分成两页，从而能够容纳新插入的数据。
存储过程分页是另外一个概念。当查询返回的结果集很大时，为了提高效率，将结果集分成若干小页面，并只将用户当前需要的页面返回给应用程序。一个很好的应用案例：当你在网络输入关键字，能搜索到成千上万的网页。如果将所有搜索到的结果全部一次显示出来，将会很费时间和资源。网络按照排名将每10个搜索结果分成一页，每次显示一页。用户总是能很快见到结果。
⑧ SQLServer数据库存储过程分页是怎样的
存储过程：create Procere pname
( @pageIndex int，@pageSize)
as
select * from tableName order by id
offset @pageIndex * pageSize fetch next pageSize rows only
分页：
sqlserver 在2008之前 使用 top 和 not int top 的方式来做分页
2008以后使用 row_number() 函数作为分页关键函数
2012使用 offset 1 fetch next 10 rows only
你问了2个问题，你可以优先把视图，存储过程，触发器等弄明白，分页是查询，在存储过程里可以写复杂的sql文，只是在运行时是预编译和参数化查询防止sql注入
⑨ SQL server 2008 存储过程分页实现，怎么提升效率
创建存储过程： create proc procPaging ( @tablename nvarchar(100), --表名 @column nvarchar(100)=null, --列名 @sort nvarchar(10), --排序asc，desc） @pageIndex int, --当前页码 @pageCount int --每页显示多少条数据 ) as begin declare @strSql nvarchar(max) set @strSql='select * from (select ROW_NUMBER() over(order by '+@column+''+@sort+' ) as rowid,* from '+@tablename+') newtable where rowid between '+ cast((@pageindex-1)*@pagecount+1 as nvarchar(100))+' and '+convert(varchar(20),@pageIndex*@pageCount) exec (@strSql) --执行字符串的SQL语句 end 执行存储过程： exec procPaging 'BookShop_BookType','BookType_ID','asc',1,3 exec procPaging 'BookShop_BookType','BookType_ID','asc',2,3 删除存储过程: drop proc book_Paging
⑩ SQL SERVER用存储过程分页的几种方法
/*----------------------------------------------
*procere name : prcPageResult
* author : FuChun
* create date : 2006-10-04
*/
CREATE PROCEDURE prcPageResult
-- 获得某一页的数据 --
@currPage int = 1, --当前页页码 (即Top currPage)
@showColumn varchar(2000) = '*', --需要得到的字段 (即 column1,column2,......)
@tabName varchar(2000), --需要查看的表名 (即 from table_name)
@strCondition varchar(2000) = '', --查询条件 (即 where condition......) 不用加where关键字
@ascColumn varchar(100) = '', --排序的字段名 (即 order by column asc/desc)
@bitOrderType bit = 0, --排序的类型 (0为升序,1为降序)
@pkColumn varchar(50) = '', --主键名称
@pageSize int = 20 --分页大小
AS
BEGIN -- 存储过程开始
-- 该存储过程需要用到的几个变量 --
DECLARE @strTemp varchar(1000)
DECLARE @strSql varchar(4000) --该存储过程最后执行的语句
DECLARE @strOrderType varchar(1000) --排序类型语句 (order by column asc或者order by column desc)
BEGIN
IF @bitOrderType = 1 -- bitOrderType=1即执行降序
BEGIN
SET @strOrderType = ' ORDER BY '+@ascColumn+' DESC'
SET @strTemp = '<(SELECT min'
END
ELSE
BEGIN
SET @strOrderType = ' ORDER BY '+@ascColumn+' ASC'
SET @strTemp = '>(SELECT max'
END
IF @currPage = 1 -- 如果是第一页
BEGIN
IF @strCondition != ''
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@showColumn+' FROM '+@tabName+
' WHERE '+@strCondition+@strOrderType
ELSE
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@showColumn+' FROM '+@tabName+@strOrderType
END
ELSE -- 其他页
BEGIN
IF @strCondition !=''
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@showColumn+' FROM '+@tabName+
' WHERE '+@strCondition+' AND '+@pkColumn+@strTemp+'('+@pkColumn+')'+' FROM (SELECT TOP '+STR((@currPage-1)*@pageSize)+
' '+@pkColumn+' FROM '+@tabName+@strOrderType+') AS TabTemp)'+@strOrderType
ELSE
SET @strSql = 'SELECT TOP '+STR(@pageSize)+' '+@showColumn+' FROM '+@tabName+
' WHERE '+@pkColumn+@strTemp+'('+@pkColumn+')'+' FROM (SELECT TOP '+STR((@currPage-1)*@pageSize)+' '+@pkColumn+
' FROM '+@tabName+@strOrderType+') AS TabTemp)'+@strOrderType
END
END
EXEC (@strSql)
END -- 存储过程结束
------------------------------------------------
GO