(function() { var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true; ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js'; var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s); })();

再谈分页

Categories: 未分类
Tags: No Tags
Comments: No Comments
Published on: 2011 年 04 月 11 日

分页已经是很久远的话题了,很多时候都是靠着一些技巧来实现分页的,随着SQL Server版本的不断更新我们有了更多的选择,之前有人总结了一下我们现在可以使用的一些分页技术 http://www.cnblogs.com/qiuwuyu/archive/2011/03/21/1989870.html。在SQL 05/08里面我们可以使用CTE&Row_Number的方式实现分页,在SQL 2000里面我们基本上都是使用Top和Set RowCount的方式实现。你会发现高版本上提供的语句更加简洁和可读,降低了逻辑的复杂性。在下一个版本中Denali中,SQL Server在Order By函数中提供了一组语句可以实现分页并且从性能到程序的可读性有很大提高。

下面是程序的代码

USE AdventureWorks2008R2;
GO
CREATE TABLE dbo.AppSettings (AppSettingID int NOT NULL, PageSize int NOT NULL);
GO
INSERT INTO dbo.AppSettings VALUES(1, 10);
GO
DECLARE @StartingRowNumber tinyint = 1;
SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
ORDER BY DepartmentID ASC
OFFSET @StartingRowNumber ROWS
FETCH NEXT (SELECT PageSize FROM dbo.AppSettings WHERE AppSettingID = 1) ROWS ONLY;
怎么样不错吧,而且可以结合上Snapshot,确保返回结果的稳定

USE AdventureWorks2008R2;
GO

— Ensure the database can support the snapshot isolation level set for the query.
IF (SELECT snapshot_isolation_state FROM sys.databases WHERE name = N’AdventureWorks2008R2′) = 0
ALTER DATABASE AdventureWorks2008R2 SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

— Set the transaction isolation level  to SNAPSHOT for this query.
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
GO

— Beging the transaction
BEGIN TRANSACTION;
GO
— Declare and set the variables for the OFFSET and FETCH values.
DECLARE @StartingRowNumber int = 1
, @RowCountPerPage int = 3;

— Create the condition to stop the transaction after all rows have been returned.
WHILE (SELECT COUNT(*) FROM HumanResources.Department) >= @StartingRowNumber
BEGIN

— Run the query until the stop condition is met.
SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
ORDER BY DepartmentID ASC
OFFSET @StartingRowNumber – 1 ROWS
FETCH NEXT @RowCountPerPage ROWS ONLY;

— Increment @StartingRowNumber value.
SET @StartingRowNumber = @StartingRowNumber + @RowCountPerPage;
CONTINUE
END;
GO
COMMIT TRANSACTION;
GO

Offset N Rows Fetch Next N Rows Only 这种可读性已经是最好了,简单到不能再简单了,如果我的程序员还不会用,那只能Fire他了。
在Warm cache的情况下(没有物理读,只有逻辑读),新的语句对于CPU的使用比较低,大家可以测试一下。
这个功能目前在CTP1中都可以实现了,大家可以从http://www.microsoft.com/downloads/en/details.aspx?FamilyID=6a04f16f-f6be-4f92-9c92-f7e5677d91f9 进行下载。

目前由于还没有发布,所以在EF中也无法得到支持,个人感觉应该在后续的EF中提供这种支持,这样对于程序员来说就一点SQL都不用写了,否则还要自己写一个extension

No Comments - Leave a comment

Leave a comment


Welcome , today is 星期六, 2017 年 06 月 24 日