CLR函数脚本
---------------------------------------------------------------------------------
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
/// <summary>
www.atcpu.com /// 用户自定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)
{
www.atcpu.com 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
www.atcpu.com -----------------------------------------------------------------------
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()
www.atcpu.com 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
www.atcpu.com 时间匆忙，经多次测试，初步结果
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要停顿
