SQL Server 和CLR集成

通过在 Microsoft SQL Server 中托管 CLR（称为 CLR 集成），可以在托管代码中编写存储过程、触发器、用户定义函数、用户定义类型和用户定义聚合函数。 因为托管代码在执行之前会编译为本机代码，所以，在有些方案中可以大大提高性能。此处举例的是把dll文件的16进制文件流拷贝出来案例。

1.0x...是文件XXX的十六进制流，可以使用UltraEdit等编辑器把相关dll文件的十六进制流copy出来。

GO
--/****** Object:  SqlAssembly [MySoftSqlFunctions]    Script Date: 02/27/2015 14:17:10 ******/
IF NOT EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'MySoftSqlFunctions' and is_user_defined = 1)
--DROP ASSEMBLY [MySoftSqlFunctions]
/****** Object:  SqlAssembly [MySoftSqlFunctions]    Script Date: 02/27/2015 14:17:10 ******/
BEGIN
CREATE ASSEMBLY [MySoftSqlFunctions]
AUTHORIZATION [dbo]
FROM 0x...(此处是dll十六进制流)
WITH PERMISSION_SET = SAFE
end
 
--/****** Object:  UserDefinedFunction [dbo].[SeqNewID]    Script Date: 02/28/2015 11:30:42 ******/
IF NOT  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SeqNewID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
EXEC('CREATE FUNCTION [dbo].[SeqNewID]()
RETURNS [uniqueidentifier] WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [MySoftSqlFunctions].[MysoftSqlFunctions.CLRFunctions].[SeqNewID]')
 
GO
IF EXISTS ( SELECT  name
            FROM    sysobjects
            WHERE   name = 'usp_NewSqeGUID'
                    AND type = 'P' )
    BEGIN
        DROP PROCEDURE usp_NewSqeGUID  
    END
GO
 
CREATE proc [dbo].[usp_NewSqeGUID]
 @NewGUID VARCHAR(50) output
as
 begin
  declare @tOut table(newguid uniqueidentifier default(NewSequentialId()));
  insert into @tOut default values
 select @NewGUID=newguid from @tOut
 end
GO
SeqNewID.sql SeqNewID.sql

 案例下载地址：http://files.cnblogs.com/files/lijiebolg/CLR%E9%9B%86%E6%88%90%E6%A1%88%E4%BE%8B.rar

 

转载于:https://www.cnblogs.com/lijiebolg/p/4903633.html