Sql Server CLR Integration

사용자 함수를 만드는 것은 이전에도 가능했지만, CLR의 도입으로 그 일이 훨씬 편해졌다.

예제

'Inside Microsoft SQL Server 2005: T-SQL Programming'에 나온 예제로서, CLR 쪽의 정규식 함수를 SQL Server 쪽에서 사용하는 예제다. .NET 버전 차이 때문에 에러나는 부분들은 수정했다. (2.0 기준)

프로젝트 생성

New Project → Visual C# → Windows → 클래스 라이브러리
- 서버를 지정해주지는 않아도 되나, 바인딩이 귀찮다.
New Project → Visual C# → 데이터베이스 → SQL Server 프로젝트
- 개발이 편리해지기는 하나, 서버를 지정해야 한다는 점이 좀 껄끄럽다.

UDF 제작

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을 어딘가에 둔다. 어트리뷰트 쪽 좀 조사해봐야할 것 같은데.

어셈블리 등록

SET NOCOUNT ON;
USE master;
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
GO
IF DB_ID('ClrUtilityDatabase') IS NOT NULL
    DROP DATABASE ClrUtilityDatabase;
GO
CREATE DATABASE ClrUtilityDatabase;
GO
USE ClrUtilityDatabase;
GO
CREATE ASSEMBLY ClrUtility
    FROM 'C:\ClrUtility.dll'
    WITH PERMISSION_SET = SAFE;

PERMISSION_SET 부분에 들어가는 내용은 다음과 같다.

SAFE: 파일, 네트워크, 환경변수, 레지스트리 등에 접근할 수 없다. 기본값이다.
EXTERNAL_ACCESS: 파일, 네트워크, 환경변수, 레지스트리 등에 접근할 수 있다.
UNSAFE: EXTERNAL_ACCESS 권한 + unmanaged 코드 실행 권한

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

#!SQL
ALTER DATABASE ClrUtilityDatabase SET TRUSTWORTHY ON

UDF 등록

USE ClrUtilityDatabase;
GO
 
IF OBJECT_ID('dbo.fn_RegExMatch') IS NOT NULL
	DROP FUNCTION dbo.fn_RegExMatch;
GO
 
CREATE FUNCTION dbo.fn_RegExMatch
    (@inpStr AS NVARCHAR(MAX), @regExStr AS NVARCHAR(MAX))
RETURNS BIT
EXTERNAL NAME ClrUtility.[ClrUtility.ClrUtility].fn_RegExMatch;

네임스페이스 부분 개같으니 주의.

UDF 사용

USE ClrUtilityDatabase;
SELECT dbo.fn_RegExMatch('abcd', '[a-z]+');

링크

see also SqlServer

사용자 도구

사이트 도구

목차