Sql Server CLR Integration

                       컴파일 해보실수 없는 분들 위해 DLL 파일 첨부 합니다.

invalid-file

SQL 2005부터 .NET CLR 을 SQL 에서 사용 할수 있게 됨으로써 복잡한 계산이나

복잡한 쿼리를 짤 필요 없이 프로그램을 이용하 가능 해져서 유용하게 사용 할수

있게 되었다.

'Inside Microsoft SQL Server 2005: T-SQL Programming'에 나온 예제를 이용

SQL 에서 사용 할수 있는 CLR 정규식 함수를 한번 만들어 보자.

  

우선 비쥬얼 스튜디오에서 프로젝트 생성을 한다.

 

New Project -> Visual C# -> Windows -> 클래스 라이브러리

C# 아래 소스를 컴파일 하여 DLL 파일로 만든다.

using System;
using System.Collections.Generic;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
//using System.Diagnostics;
//using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;

namespace ClrUtility
{
    public partial class ClrUtility
    {
        [SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.None)]
        public static SqlBoolean fn_RegExMatch(SqlString inpStr, SqlString regExStr)
        {
            if (inpStr.IsNull || regExStr.IsNull)
                return SqlBoolean.Null;
            else
                return (SqlBoolean)Regex.IsMatch(inpStr.Value, regExStr.Value, RegexOptions.CultureInvariant);
        }
    }
}

만들어진 DLL 파일을 SSMS 에서 아래 쿼리를 실행하여 등록 한다.

SET NOCOUNT ON;
USE master;
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
GO
USE AdventureWorks;

CREATE ASSEMBLY ClrUtility
    FROM 'C:\SqlServerProject1.dll'
    WITH PERMISSION_SET = SAFE;

SAFE: 파일, 네트워크, 환경변수, 레지스트리 등에 접근할 수 없다. 기본값이다.

EXTERNAL_ACCESS: 파일, 네트워크, 환경변수, 레지스트리 등에 접근할 수 있다.

UNSAFE: EXTERNAL_ACCESS 권한 + unmanaged 코드 실행 권한

EXTERNAL_ACCESS 이상의 권한을 주기 위해서는 아래와 같은 명령어를 통해 TRUSTWORTHY
속성을 변경 해야 한다.

USE AdventureWorks;

CREATE FUNCTION dbo.fn_RegExMatch
    (@inpStr AS NVARCHAR(MAX), @regExStr AS NVARCHAR(MAX))
RETURNS BIT
EXTERNAL NAME ClrUtility.[ClrUtility.ClrUtility].fn_RegExMatch;

등록된 DLL 파일을 사용자 정의 함수로 등록 한다.

USE AdventureWorks;

SELECT
 dbo.fn_RegExMatch(FirstName, '[a-z]+')
FROM Person.Contact;

SQL 쿼리로 구현하기 힘들었거나 복잡했던 걸 .NET 환경 CLR 를 통해 쉽게 구현
할수 있는 좋은 기능인 듯 하다. 프로그램을 조금 할수 있는 사람이면 누구나 쉽게 구현하고
SQL 에서 가져다 쓸수 있는 좋은 기능 인듯 하다.
참고 : Inside Microsoft SQL Server 2005