SQL2005使用CLR函数获取行号
行号可以广泛使用在分页或者报表当中。SQL2005使用Row_Number来获取，但这个需要配合Order来处理，数据量大的情况下可能会影响性能。
要实现CLR自定函数返回行号的关键问题是一个自增长的序列如何保存，并且需要考虑并发和释放的问题。这里我用了一个静态的集合来保持这个事物，缺点是每次查询必须初始化一个集合的随机Key，并在本次查询后销毁。
如果你还不知道CLR函数如何使用，到网上去搜索一下，或者参考我以前的文章
http://blog.csdn.net/jinjazz/archive/2008/12/05/3455854.aspx
下面我就归入正文，一个可以实现上述思路的c#代码如下：
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语句，我们在测试环境中部署
exec sp_configure 'show advanced options' , '1' ;
go
reconfigure ;
go
exec sp_configure 'clr enabled' , '1'
go
reconfigure ;
exec sp_configure 'show advanced options' , '1' ;
go
-- 测试数据库
create database testDB
go
use testDB
go
ALTER DATABASE testDB SET TRUSTWORTHY On
go
CREATE ASSEMBLY testAss FROM 'E:/SqlServerProject3.dll' WITH PERMISSION_SET = UnSAFE;
--
go
CREATE FUNCTION dbo. GetRowNum
(
@key nvarchar ( 100)
)
RETURNS bigint
AS EXTERNAL NAME testAss. [UserDefinedFunctions]. GetRowNum
go
CREATE proc dbo. GetRowNumEnd
(
@key nvarchar ( 100)
)
AS
EXTERNAL
NAME
testAss.
[UserDefinedFunctions].
GetRowNumEnd
接下来我们做个简单测试，如下sql语句
-- 获取带行号的结果
select *, rn= dbo. GetRowNum( 1) from sysobjects
-- 清理结果
exec GetRowNumEnd 1
你就能看到一行带行号的结果了，当然别忘了查询之后把key清理掉，否则下次的1为key的序列行号就不是从1开始了。
是不是这个语法比row_number函数简练而且方便了很多呢？
下面我们来看一个具体测试用例，比如分页。分页就是看行号在某个范围内，但是这里不推荐用where 行号 between and，因为这个是函数，用where会引起全表计算，改为 top和where 行号>起始 就可以了，这样效率只和起始值有关系。
我们测试用系统表syscolumns，数据太少多做几次全交叉就可以了，比如
select count (*) from syscolumns a, syscolumns b, syscolumns c
--75151448
这个数据量算是比较bt了，7千500万...最关键的是他没有主键，没有排序规则定义，这么一个东西用以前的分页方法是很难处理的。现在却很简单
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 > 200000
exec dbo. GetRowNumEnd @key
返回第200001到200010之间的10条数据，只需要1秒。当然如果用这个方法返回的是7千万的最后几条数据还是比较慢的。
总结一下这个方法：
优点是：
性能和表结构无关，而且还是比较可靠。
代码简单易懂。
通用性比较很好，任何查询，只要在字段后面把函数调用一下，再在外部嵌套一个top n和where 就可以了。
值得商榷的环节：
因为不能反过来计算序列，所以大量数据的后面页会比较慢。
序列是通过key来控制的，key的初始化代码必须严格控制，否则并发会有问题，不过guid是一定保险的
CLR的部署问题，不过你可以把他部署到类似master库中，其他库都去master引用，这样可以方便些。
因为没有经过实际使用的考验，所以还有可能考虑不周到的地方，希望大家提出指正，个人觉得这个方法还是很有潜力可挖的。
