MS SQL Server 2005以降、非常に強力な
SQL CLRテクノロジがデータベース開発者に追加されました。
このテクノロジーにより、C＃やVB.NETなどの.NET言語を使用してSQLサーバーの機能を拡張できます。
SQL CLRを使用すると、独自のストアドプロシージャ、トリガー、ユーザーの種類と関数、および高性能言語で記述された集計を作成できます。 これにより、パフォーマンスを大幅に改善し、サーバーの機能を想像を絶する境界まで拡張できます。
簡単な例を考えてみましょう。SQL構文とC＃に基づいたSQL CLRを使用して、区切り線で行を切るためのユーザー定義関数を作成し、結果を比較します。
テーブルを返すカスタム関数
CREATE FUNCTION SplitString (@text NVARCHAR( max ), @delimiter nchar (1))
RETURNS @Tbl TABLE (part nvarchar( max ), ID_ORDER integer ) AS
BEGIN
declare @ index integer
declare @part nvarchar( max )
declare @i integer
set @ index = -1
set @i=1
while (LEN(@text) > 0) begin
set @ index = CHARINDEX(@delimiter, @text)
if (@ index = 0) AND (LEN(@text) > 0) BEGIN
set @part = @text
set @text = ''
end else if (@ index > 1) begin
set @part = LEFT (@text, @ index - 1)
set @text = RIGHT (@text, (LEN(@text) - @ index ))
end else begin
set @text = RIGHT (@text, (LEN(@text) - @ index ))
end
insert into @Tbl(part, ID_ORDER) values (@part, @i)
set @i=@i+1
end
RETURN
END
go
この関数は、区切り文字を使用して入力文字列を切り取り、テーブルを返します。 たとえば、一時テーブルにレコードをすばやく入力する場合など、このような関数を使用すると非常に便利です。
select part into #tmpIDs from SplitString( '11,22,33,44' , ',' )
その結果、テーブル#tmpIDsには次が含まれます。
11
22
33
44
C＃で記述されたCLRモジュール
次の内容でSplitString.csファイルを作成します。
using System;
using System.Collections;
using System.Collections. Generic ;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public class UserDefinedFunctions {
[SqlFunction(FillRowMethodName = "SplitStringFillRow" , TableDefinition = "part NVARCHAR(MAX), ID_ORDER INT" )]
static public IEnumerator SplitString(SqlString text, char [] delimiter)
{
if (text.IsNull) yield break ;
int valueIndex = 1;
foreach ( string s in text.Value.Split(delimiter, StringSplitOptions.RemoveEmptyEntries)) {
yield return new KeyValuePair< int , string >(valueIndex++, s.Trim());
}
}
static public void SplitStringFillRow( object oKeyValuePair, out SqlString value , out SqlInt32 valueIndex)
{
KeyValuePair< int , string > keyValuePair = (KeyValuePair< int , string >) oKeyValuePair;
valueIndex = keyValuePair.Key;
value = keyValuePair.Value;
}
}
モジュールをコンパイルします。
%SYSTEMROOT%\Microsoft.NET\Framework\v2.0.50727\csc.exe /target:library c:\SplitString.cs
出力はSplitString.dllです
次に、SQL ServerでCLRの使用を有効にする必要があります。
sp_configure 'clr enabled' , 1
go
reconfigure
go
すべて、モジュールを接続できます。
CREATE ASSEMBLY CLRFunctions FROM 'C:\SplitString.dll'
go
そして、カスタム関数を作成します。
CREATE FUNCTION [dbo].SplitStringCLR(@text [nvarchar]( max ), @delimiter [ nchar ](1))
RETURNS TABLE (
part nvarchar( max ),
ID_ODER int
) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME CLRFunctions.UserDefinedFunctions.SplitString
CLRの詳細
1.アセンブリがサーバーにアップロードされ、そこに保存されます。 アセンブリを参照する関数は既にデータベースに保存されています。 したがって、データベースが転送されるサーバーにアセンブリをロードする必要があります。
2.アセンブリを作成するときに、必要に応じてPERMISSION_SET引数が指定され、アセンブリのアクセス許可が決定されます。
MSDNをご覧になることをお勧めします。 要するに：SAFE-データベースのみで作業することができます。 EXTERNAL_ACCESS-他のサーバー、ファイルシステム、およびネットワークリソースを操作できます。 危険-WinAPIを含むすべて。
3.デバッグ時に、
MSDNで指定されている機能があります。
結果
通常の
SplitStringと
SplitStringCLRの速度を比較するために、100個のコンマ区切りの数字で構成される入力文字列でこれらの関数を1000回呼び出しました。
SplitStringの平均動作時間は
6.152ミリ秒で、
SplitStringCLRの平均動作時間は
1.936ミリ秒でした。
違いは3倍以上です。
これが誰かに役立つことを願っています。