【探讨】通用分页？SQL2005使用CLR函数获取行号,该如何处理-MSSQL教程-爱易网页

【探讨】通用分页？SQL2005使用CLR函数获取行号,该如何处理

日期：2014-05-18　浏览次数：20464 次

【探讨】通用分页？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';
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语句

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

免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。

【探讨】通用分页？SQL2005使用CLR函数获取行号,该如何处理

相关资料更多>

推荐阅读更多>