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