【探讨】通用分页？SQL2005使用CLR函数获取行号
blog原文
http://blog.csdn.net/jinjazz/archive/2009/04/16/4082793.aspx

SQL2005使用Row_Number来获取，但这个需要配合Order来处理，数据量大的情况下可能会影响性能。如果你还不知道CLR函数如何使用，到网上去搜索一下，或者参考我以前的文章
http://blog.csdn.net/jinjazz/archive/2008/12/05/3455854.aspx

CLR的C#代码

C# code

using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;/// <summary>/// 引用请保留以下信息：/// /// 用户自定CLR函数，用来生成一个序列/// by:jinjazz（近身剪）/// http://blog.csdn.net/jinjazz/// /// </summary>public partial class UserDefinedFunctions{    /// <summary>    /// 初始化查询标识    /// </summary>    public static System.Collections.Generic.Dictionary<string, long> rnList =        new System.Collections.Generic.Dictionary<string, long>();    /// <summary>    /// 根据标识获取序列    /// </summary>    /// <param name="key">查询标识</param>    /// <returns></returns>    [Microsoft.SqlServer.Server.SqlFunction]    public static SqlInt64 GetRowNum(SqlString key)    {        try        {            if (rnList == null)                rnList = new System.Collections.Generic.Dictionary<string, long>();            if (rnList.ContainsKey(key.Value) == false)                rnList.Add(key.Value, 1);            return rnList[key.Value]++;        }        catch        {            return -1;        }    }    /// <summary>    /// 销毁查询标识    /// </summary>    /// <param name="key"></param>    [Microsoft.SqlServer.Server.SqlProcedure]    public static void GetRowNumEnd(SqlString key)    {        try        {            if (rnList == null || rnList.ContainsKey(key.Value) == false) return ;            rnList.Remove(key.Value);            return ;        }        catch        {                     }    }};

部署上面的CLR函数可以运行如下SQL语句，我们在测试环境中部署

SQL code

exec sp_configure 'show advanced options', '1';goreconfigure;goexec sp_configure 'clr enabled', '1'goreconfigure;exec sp_configure 'show advanced options', '1';go --测试数据库create database testDBgouse testDBgoALTER DATABASE  testDB SET TRUSTWORTHY On goCREATE ASSEMBLY testAss FROM 'E:\SqlServerProject3.dll' WITH PERMISSION_SET = UnSAFE;--goCREATE FUNCTION dbo.GetRowNum  (    @key nvarchar(100))    RETURNS bigintAS EXTERNAL NAME testAss.[UserDefinedFunctions].GetRowNumgoCREATE proc dbo.GetRowNumEnd  (    @key nvarchar(100))    AS EXTERNAL NAME testAss.[UserDefinedFunctions].GetRowNumEnd

接下来我们做个简单测试，如下sql语句

SQL code

--获取带行号的结果select *,rn=dbo.GetRowNum(1) from sysobjects--清理结果exec GetRowNumEnd 1

你就能看到一行带行号的结果了，当然别忘了查询之后把key清理掉，否则下次的1为key的序列行号就不是从1开始了。
是不是这个语法比row_number函数简练而且方便了很多呢？

下面我们来看一个具体测试用例，比如分页。分页就是看行号在某个范围内，但是这里不推荐用where 行号 between and，因为这个是函数，用where会引起全表计算，改为 top和where 行号>起始 就可以了，这样效率只和起始值有关系。
我们测试用系统表syscolumns，数据太少多做几次全交叉就可以了，比如

SQL code

select count(*) from syscolumns a,syscolumns b,syscolumns c--75151448

这个数据量算是比较bt了，7千500万...最关键的是他没有主键，没有排序规则定义，这么一个东西用以前的分页方法是很难处理的。现在却很简单

SQL code

declare @key varchar(100)set @key=newid()select top 10 * from(select a.* ,dbo.GetRowNum(@key) as rn     from syscolumns a,syscolumns b,syscolumns c)t where rn >200000exec dbo.GetRowNumEnd @key 
 

返回第200001到200010之间的10条数据，只需要1秒。当然如果用这个方法返回的是7千万的最后几条数据还是比较慢的。

总结一下这个方法：

优点是:

性能和表结构无关，而且还是比较可靠。

代码简单易懂。

通用性比较很好，任何查询，只要在字段后面把函数调用一下，再在外部嵌套一个top n和where 就可以了。

值得商榷的环节:

因为不能反过来计算序列，所以大量数据的后面页会比较慢。

序列是通过key来控制的，key的初始化代码必须严格控制，否则并发会有问题，不过guid是一定保险的

CLR的部署问题，不过你可以把他部署到类似master库中，其他库都去master引用，这样可以方便些。

因为没有经过实际使用的考验，所以还有可能考虑不周到的地方，希望大家提出指正，个人觉得这个方法还是很有潜力可挖的。

[解决办法]
学习！ 强烈支持未来的BI版主...
[解决办法]
bd来学习
[解决办法]
学习来了..
[解决办法]
研究深厚啊,学习
[解决办法]
学习！关注...
[解决办法]
.
[解决办法]
学习来了..
[解决办法]
跟 top top max 的性能比起来如何？
[解决办法]
研究深厚啊,学习

[解决办法]
学习.
[解决办法]
学习！ 强烈支持未来的BI版主...
[解决办法]
LZ战斗力可真强，辛苦了~~~
[解决办法]
up
[解决办法]

SQL code

关注.......
[解决办法]
jj总能另辟蹊径，学习了！

自己做了一些row_number()的测试。
SQL code
select count(*) from syscolumns a,syscolumns b,syscolumns c/*331373888*/set statistics time ongo--test1:select top 10 * from(select a.* ,row_number() over(order by a.id) as rn     from syscolumns a,syscolumns b,syscolumns c)t where rn >=200000go/*SQL Server 分析和编译时间:    CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。(10 行受影响)SQL Server 执行时间:   CPU 时间 = 94 毫秒，占用时间 = 97 毫秒。*/--test2:select top 10 * from(select a.* ,row_number() over(order by newid()) as rn     from syscolumns a,syscolumns b,syscolumns c)t where rn >=200000go/*执行3分钟无果，不想等了*/--test3:select top 10 * from(select a.* ,row_number() over(order by getdate()) as rn     from syscolumns a,syscolumns b,syscolumns c)t where rn >=200000go/*SQL Server 分析和编译时间:    CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。(10 行受影响)SQL Server 执行时间:   CPU 时间 = 93 毫秒，占用时间 = 99 毫秒。*/set statistics time offgo
[解决办法]
不懂么那个
[解决办法]
支持一个，推荐！
[解决办法]
推荐啊。支持
[解决办法]
支持
[解决办法]
哇，好文，收藏，辛苦啦
[解决办法]
顶个
------解决方案-------------------- 
 
支持，学习~\()/~啦啦啦！
[解决办法]
.
[解决办法]
帮顶。
[解决办法]
通用性很不错！

不过通过CLR调用应该比内置的函数要差吧，毕竟要多一层与CLR的通讯。

Dictionary内部应该是通过HASH探测比较的，似乎比直接row_number() over( order by) 多了一个步骤？
[解决办法]
mark@@@
[解决办法]
探讨

学习！ 强烈支持未来的BI版主...

[解决办法]
改天测一下,目前我生产环境是用api游标做的,通用性不错,不过100+W数据就不行了,不知道各位生产环境的分页都如何实现的,能应付多少数据量?
[解决办法]
理解
学习
[解决办法]
想法不错，但感觉还是ROW_NUMBER()好，都是产生行号，通用性也不错，至少不用去维护key创建与销毁。
[解决办法]
学习
[解决办法]
收藏！
[解决办法]
支持.
[解决办法]
学习
[解决办法]
.
[解决办法]
纯路过..
[解决办法]
收藏之
[解决办法]
mark
[解决办法]
。。。
[解决办法]
学习学习，
[解决办法]
学学
[解决办法]
xue xue
[解决办法]
这个得好好学习
[解决办法]
学习，崇拜剪剪！
[解决办法]
学习
[解决办法]
有建设性大虾
[解决办法]
思路很开放..
[解决办法]
学习了，CLR还可以这么用啊
[解决办法]
好啊啊啊
[解决办法]
学习了
[解决办法]
好
[解决办法]
不错，谢谢LZ分享 ；）
[解决办法]
好，谢谢搂主的贡献
[解决办法]
支持版主~~
[解决办法]
想法很好啊... 
 
[解决办法]
说实话，一直没找到性能非常好的分页
[解决办法]
学习了。。。
不过我还是喜欢 row_number() over(order by getdate() )  
近1000w的数据，发现是很快的。。。。
[解决办法]
路过,看看!
[解决办法]
学习下
[解决办法]
郁闷，怎么看呀！！！现在看了，天亮就忘记了！
[解决办法]
学习了
[解决办法]
ORDER BY 的话,建立在主键上试试.
大数据量分区后比较好处理.
个人还是比较倾向于ROW_NUMBER().
偶目前的一种方案是按照ROWVERSION,是个时间变量.
作为分区和主键.并且按照这个栏位来分页.
没有做过大数据量的测试,
有空可以试试这种方案的性能.