CLR函数脚本

---------------------------------------------------------------------------------

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Collections;

/// <summary>

/// 用户自定CLR 函数，用来生成一个序列

/// </summary>

public partial class test

{

    

    public static Hashtable ht=new Hashtable(); //创建一个Hashtable实例

 

    [Microsoft.SqlServer.Server.SqlFunction]

    public static SqlInt64 GetRowNum(SqlGuid Guid1)

    {

        try

        {

            if (!ht.Contains(Guid1)) //判断哈希表是否包含特定键,其返回值为true或false

            {

                ht[Guid1] = 0;

            }

            Int64 i = Convert.ToInt64(ht[Guid1].ToString());

            i++;

            ht[Guid1] = i.ToString();

            return i;

        }

        catch

        {

            return -1;

        }

    }

    /// <summary>

    /// 删除哈希表值

    /// </summary>

    /// <param name="Guid1"></param>

    [Microsoft.SqlServer.Server.SqlProcedure ]

    public static void ClearGuid(SqlGuid Guid1)

    {

        try

        {                

            ht.Remove(Guid1);

            return ;

        }

        catch

        {

        }

    }

};

 

------------------------------------------------------------------------------------------------------------------------------

启动CLR

exec sp_configure 'show advanced options','1';

go

reconfigure  WITH OVERRIDE

go

exec sp_configure 'clr enabled','1'

go

reconfigure WITH OVERRIDE 

ALTER DATABASE   test SET TRUSTWORTHY On

-----------------------------------------------------------------------

drop FUNCTION  GetRowNum

drop proc ClearGuid

drop ASSEMBLY testss

CREATE ASSEMBLY testss FROM 'C:\Documents and Settings\Administrator\桌面\test\SqlServerProject1\SqlServerProject1\bin\Debug\SqlServerProject1.dll' WITH PERMISSION_SET = UnSAFE;

 

go

创建函数

CREATE FUNCTION dbo.GetRowNum

(

 @Guid uniqueidentifier

)

RETURNS bigint

AS EXTERNAL NAME

  testss.test.GetRowNum

 

CREATE proc ClearGuid

(

  @Guid uniqueidentifier

)

AS EXTERNAL NAME

  testss.test.ClearGuid

 

 ----------------------------------------------------------------------------------------------------

 

GetRowNum与identity对比

 

declare @Guid uniqueidentifier

set @Guid=newid()

select dbo.GetRowNum(@Guid)

  from list

 

--清理哈希表

exec ClearGuid  @Guid

 

select identity(int ,1,1) as id into #t  from list

 

select id  from #t

drop table #t

数据量943761

 

时间匆忙，经多次测试，初步结果

1.GetRowNum(5591)消耗时间比identity(6278)少近700毫秒

2.GetRowNum(2171)的CPU比identity（922）高很多

3.GetRowNum（2352）读是identity（4828）的一半

4.GetRowNum没有写入,identity的写入是1519

 

初步结论

1.GetRowNum消耗更多的CPU，进行运算

2.GetRowNum的磁盘消耗比identity低，而且是没有写入的

3.如果表已经有自增(不连续),identity就不能用，GetRowNum可以

4.GetRowNum瞬间返回数据，identity要停顿

 

......................