【探讨】通用分页？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#代码

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千万的最后几条数据还是比较慢的。

总结一下这个方法：

[Quote=优点是:]
性能和表结构无关，而且还是比较可靠。

代码简单易懂。

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


[Quote=值得商榷的环节:]

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

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

CLR的部署问题，不过你可以把他部署到类似master库中，其他库都去master引用，这样可以方便些。
[/Quote]
因为没有经过实际使用的考验，所以还有可能考虑不周到的地方，希望大家提出指正，个人觉得这个方法还是很有潜力可挖的。