Visual Studio 2005와 SQL Server 2005 최상의 만남: SQLCLR vs. T-SQL 김 정 선 삼성SDS 멀티캠퍼스 전임교수 필라넷 수석 컨설턴트 Microsoft SQL Server MVP Microsoft .NET Advisor

강사 소개 김 정선 ( visualdb@unitel.co.kr ) MCT(1997), MCDBA Microsoft SQL Server MVP Microsoft .NET Advisor 현) 삼성SDS 멀티캠퍼스 / 전임 교수 현) 필라넷(Feel@NET) / 수석 컨설턴트 전) 삼성 중공업 조선해양사업본부 ECIM팀 SQL Server DB 컨설팅 SQL Server Technical Supporting DB Application 개발

목차 요약 SQLCLR 구성 개요 어셈블리 CLR 함수 SQL Server Data Provider CLR 사용자-정의 Types CLR 사용자-정의 Aggregate [Functions] 최적 사례 참고. 이 자료는, SQL Server 2005 CTP 4월 & Visual Studio 2005 Beta2 버전과 그 이전 버전의 내용이 혼재합니다

실무 요약 Microsoft SQL Server에서 비즈니스 로직 작성을 위한 새로운 방법 두 가지 새로운 확장성 기능 CLR 언어를 사용한 함수, 프로시저 와 트리거 T-SQL 내장 함수 라이브러리를 쉽고 효과적으로 확장 가능 SQL Server 외부 데이터 액세스를 위한 효율적이고 쉬운 방법 T-SQL 보다 빠른 프로시저 로직과 연산 구현 방법 투명성 T-SQL 함수, 프로시저, 트리거를 사용하는 것과 동일 두 가지 새로운 확장성 기능 스칼라 타입 시스템을 확장하는 UDT 집계 프레임워크를 확장하는 UDA

SQLCLR 구성 개요 Microsoft® Visual Basic .NET, C#, … Build Microsoft® Visual Studio® 2005 Project Assembly: “TaxLib.dll” deploy SQL Data Definition: create assembly … create function … create procedure … create trigger … create type … Runtime hosted inside SQL Microsoft® SQL Server™ SQL Queries: select sum(tax(sal,state) ) from Emp where county = ‘King’

어셈블리 로딩과 클래스 로딩 SQL Server가 데이터베이스로부터 어셈블리를 로딩 FX는 예외, 파일 시스템에서 가져오지 않는다 SQL Server가 어떤 FX가 사용될 수 있는지 결정 가져올 수 있는 라이브러리의 하드-코드 리스트를 사용 SQL Server가 사용자 코드를 검증 카탈로그 등록 시점에 리플렉션을 통해서 실행 시점에 Host Protection 속성을 경유해서 Hosted CLR Catalog AppDomain Assembly Validate User Asm Asm.Load() Database

어셈블리 카탈로그 등록하기 CREATE ASSEMBLY 어셈블리 카탈로그 등록에 사용 디스크에서 혹은 스트림에서 bits로 가져옴 어셈블리는 symbolic name으로 할당 bits 는 시스템 테이블에 저장 필요한 권한 어셈블리 카탈로그 등록 파일 시스템 액세스 symbolic name assembly name CREATE ASSEMBLY math FROM 'c:\types\math.dll'

CLR 함수 CLR 언어로 작성된 함수를 T-SQL 함수로 사용 클래스는 반드시 public 함수는 반드시 public static 클래스를 포함한 어셈블리가 카탈로그를 등록되어야 한다 함수도 반드시 카탈로그로 등록되어야 한다 math.dll namespace Math { public class Inverter public static int Invert(int x) return -x; } public class public static function

함수 카탈로그 등록 CREATE FUNCTION 함수 카탈로그에 사용 카탈로그 함수만 T-SQL로 사용될 수 있다 T-SQL name 이 CLR 함수 name에 연결 CLR 함수 파라미터는 위치에 의해서 연결 T-SQL 파라미터는 ‘@’ 구문 사용 이름공간(namespace)는 [ ] 로 포함되어야 한다 첫 번째 CLR 함수 파리미터에 연결 T-SQL name Create Function DoInvert(@A int) returns int As EXTERNAL NAME math.[Math.Inverter].Invert CLR name

CLR 함수 사용 CLR 함수를 다른 T-SQL 함수처럼 사용 T-SQL 이름으로 참조 T-SQL 파라미터는 순서대로 넘김 T-SQL 이름은 생성된 database/schema로 참조

적용 시나리오 – CLR 함수 스칼라 함수 테이블-값 함수 Transact-SQL 내장 함수 라이브러리 확장 가능 (예, 산술/재무 계산, 문자열 연산, 등) CLR의 성능 이득 계산, 로직 집약적 컴파일(managed 코드) vs. 인터프리터(Transact-SQL) 테이블-값 함수 테이블 형식으로 외부 데이터를 액세스 파일, 웹서비스, 외부 데이터 원본 등 데이터 액세스를 위해서는 비 적합

적용 시나리오 – CLR 프로시저 CLR 함수에서와 같은 제약을 받지 않음: 시나리오: 데이터 정의 (DDL) / 혹은 데이터 조작 (DML) 연산 가능 결과를 클라이언트로 반환 가능 시나리오: Transact-SQL 이 데이터 액세스 집약적인 프로시저로서 여전히 최적 다음 프로시저에 대해서 CLR 고려: 많은 프로시저 로직 혹은 계산 처리 확장 저장 프로시저(xp_)의 대체

.NET 코드와 Transact SQL .NET code 와 T-SQL 상호 호출 가능 SQL Server 2000 규칙에 종속 .NET 파라미터는 T-SQL에 반드시 호환 .NET code and T-SQL은 각각 최적의 사용이 있다 최적 사례를 따르는 것이 권장

.NET 코드는 빠르고, 안전 .NET 코드가 더 빠를 수 있다 .NET 코드는 안전한 타입 컴파일(.NET 코드) vs. 인터프리터(T-SQL 프로시저) .NET 코드는 안전한 타입 코드가 카탈로그로 등록될 때 자체적 안정 검사 확장 프로시저는 악성 코드 비 검사 .NET 코드는 .NET 프레임워크 사용 가능 특정 어셈블리, 클래스, 메서드만 허용 보안 고려 사항 강제되는 시점: 어셈블리 카탈로그 등록 코드 실행 어플리케이션과 데이터베이스에서 동일 언어 사용

.NET 코드-기술적 요구사항이 용이 어플리케이션과 데이터베이스에 단일 언어가 사용 SqlClient 와 SqlServer 코드가 유사 어플리케이션과 서버에 동일한 프로그래밍 모델/언어 사용 개발자는 보다 친숙한 개체 지향적 기술 사용 가능 구조적 예외 처리

Transact-SQL 데이터 액세스 T-SQL 이 데이터 액세스를 위해서 한층 더 우수 타입 시스템에 대한 강제가 불필요 SQL Server 2005 이전 코드는 모두 T-SQL로 작성 SQL Server 2005 T-SQL에 예외 처리 기능 추가 T-SQL 이 데이터 액세스에 보다 빠를 수 있다 SQL Server의 내부 버퍼에 직접 액세스 풍부한, 데이터-중심적인 함수 라이브러리

.NET 프로시저와 Transact-SQL 데이터 계층에 직접 액세스 프로시저적인 프로그래밍 .NET 코드, 비-데이터 액세스 코드에 한층 유리 산술적 수식 SQL Server 외부의 시스템 자원을 액세스 개체-지향적 프로그래밍 Transact-SQL & .NET 코드 데이터베이스로 코드를 저장하고 불러옴 ANSI 표준에 기반 Transact-SQL은 ANSI SQL PSM 스펙의 변형 .NET 코드는 ANSI SQL JRT 스펙과 유사

SqlTypes System.Data.SqlTypes 코드에서 반드시 사용 SqlTypes 혹은 .NET 타입이 사용될 수 있다 SqlTypes 은 SQL Server 타입과 동일 구조 NULL에 대한 IsNUll 테스트, Null 설정 가능 NULL을 .NET 타입 파라미터로 넘기면 예외 발생

Null NULL 데이터를 코드에서 반드시 고려 SqlTypes 사용 INullable.IsNull을 사용해서 NULL 테스트 <SqlType>.Null로 null 설정 함수는 반드시 SQL 의미와 호환되어야 한다 Null 입력에 null 반환 public static void AddOne(ref SqlInt32 y) { if (!y.IsNull) y = y + 1; else y = SqlInt32.Null; }

데이터베이스 외부의 코드 데이터베이스 외부의 코드는 연결 구성이 필요 버퍼 구성 배치 명령 시작 트랜잭션을 시작하거나 혹은 참여 System.Data.SqlClient Input Buffer Socket Connection Output Buffer Socket Database Transaction

SQL Server 프로그래밍 모델 SQL Server 2005 두 가지 data provider와 동작 Microsoft.SqlServer.Server (Beta2) System.Data.SqlServer (Beta2 이전) System.Data.SqlClient 프로그래밍 모델을 단일화 SqlClient 처럼 SqlConnection을 사용한 프로그래밍 Context Connection(연결 문자열로 지정)으로 서버 구분 System.Data.SqlClient SqlConnection Select * from ... Microsoft.SqlServer.Server Connection “context connection=true” Select * from ... Database

SQL Server In-Process SqlClient와 동일한 프로그래밍 모델 이름공간(namespace) System.Data.SqlClient Microsoft.SqlServer.Server SqlContext, SqlPipe , SqlDataRecord, SqlTriggerContext 오브젝트 Context Connection CLR 함수, 프로시저 등이 실행되는 동일 서버를 액세스 CLR 모듈 호출자의 동일한 세션을 공유 연결 문자열로 지정 … new SqlConnection(“context connection=true”)…

SqlContext SqlContext 는 CLR 코드 호출자의 실행 문맥 SqlPipe 오브젝트 SqlTriggerContext 오브젝트 CLR 트리거 내에서만 처리 트리거를 호출시킨 작업에 대한 정보 업데이트된 칼럼 정보 등을 제공 IsAvailable 속성 현재 실행 코드의 in-process 여부를 반환 WindowsIdentity 오브젝트 호출자의 윈도우 자격증명

SqlPipe SqlPipe 는 통신 매커니즘 사용자 정의 결과 집합 전송 개념적으로는 ASP.NET의 Response와 유사 결과 집합과 메시지 전송을 위한 overload 메서드 void Send(string message) void Send(SqlDataReader reader) void Send(SqlDataRecord record) 명령을 실행하고 결과 집합을 곧바로 호출자에게 반환 ExecuteAndSend(SqlCommand command) 사용자 정의 결과 집합 전송 SendResultsStart(SqlDataRecord record) SendResultsRow(SqlDataRecord record) SendResultsEnd()

SqlTriggerContext SqlTriggerContext 는 트리거 문맥 정보를 반환 TriggerAction ColumnCount IsUpdatedColumn(int) EventData DDL trigger

왜 사용자-정의 type 인가? 타입 시스템을 확장할 수 있는 스칼라 타입 추가 스칼라 값을 효율적으로 구현 가능 정렬, 집계에 사용 사용자 정의 정렬 순서와 산술 계산 스칼라 값을 효율적으로 구현 가능 간결한 표현 컴파일 언어로 작성된 연산

사용자-정의 type과 관계형 모델 타입 시스템에 새로 스칼라 추가를 의미 사용자-정의 Type으로 관계형 모델을 향상 CLR 값(value) 타입이 최적의 후보자 사용자-정의 Type으로 관계형 모델을 향상 사용자 정의 스칼라가 원시 타입처럼 동작 데이터베이스 서버 내에 효율적으로 구현된 동작

UDT 카탈로그 등록 SQL Server 2005 UDT는 .NET 어셈블리에 상주 우선, 어셈블리를 카탈로그로 등록 (CREATE ASSEMBLY) 다음, 타입을 카탈로그로 등록 (CREATE TYPE) external name = .NET class (혹은 struct) 이름 symbolic name 은 원하는 이름으로 CREATE ASSEMBLY Point FROM 'c:\types\Point.dll' GO CREATE TYPE PointCls EXTERNAL NAME Point.PointCls

UDT 사용 Type 사용 varchar 타입에서 암시적 변환 지원 테이블 칼럼 변수 와 파라미터 UDF 리턴 값 명시적 변환 가능 혹은 팩토리 함수를 작성하는 것도 가능 CREATE TABLE point_tab( id int primary key, thepoint PointCls ) INSERT INTO point_tab values(1, '100:200') DECLARE @p PointCls SET @p = convert(PointCls, '300:400')

Accessor 와 mutator 모든 public 필드는 Type의 “멤버”로 사용 가능 .(dot) 구문을 사용 instance.member, instance.method 인스턴스 멤버는 대소문자 구분 “mutators” 로 표시된 메서드만 데이터베이스 업데이트 구문에 사용 가능 SELECT thepoint.m_x, thepoint.m_y FROM point_tab go -- use mutator -- the name of mutator is case-sensitive! UPDATE point_tab SET thepoint.SetXY(20, 30) WHERE thepoint.m_x = 0

UDT 프로그래밍 UDT는 추가 요구사항을 가진 일반 .NET 클래스 SQL 엔진은 상속 관계를 인식 못함 참조 타입(class) 혹은 값 타입(struct)으로 가능 문자열로부터 그리고 문자열로의 변환을 반드시 지원 INullable 반드시 지원 SQL Server가 Type을 물리적으로 어떻게 보존할 것인지 알아야 한다 SQL 엔진은 상속 관계를 인식 못함 클래스 계층구조를 사용할 수 있다 SQL Server 카탈로그에 정의되지는 않는다 Transact-SQL 연산에 사용되지 않는다

UDT 상태 SqlUserDefinedType 은 udt 상태 저장 형식을 설정 Microsoft.SqlServer.Server.Format 형식 종류 선택 Native UserDefined IsFixedLength, IsByteOrdered, MaxSizeBytes Dim Formatting [SqlUserDefinedType( 사용자 정의 포맷 Format. UserDefined , IsByteOrdered=true, IsFixedLength=true, MaxByteSize=19)] public struct Dim {

Native 형식 UDT 상태에 대한 Native 형식 사용 모든 멤버는 값(value) 타입 StructLayout(LayoutKind.Sequential)와 Serializable 사용 모든 멤버는 값(value) 타입 string 도 사용할 수 없다 기본 키를 위해서는 IsByteOrdered = true 순서는 타입 정의 내의 멤버의 순서를 따른다 MaxByteSize 는 지정하지 않는다 [SqlUserDefinedType(Format.Native)] [Serializable] [StructLayout(LayoutKind.Sequential)] public struct Location { X가 첫 번째 순서 public int X; public int Y; Y가 두 번째 순서 ... }

사용자 정의 포맷 사용자 정의 포맷은 udt 구현을 직접 작성 IBinarySerialize 구현 MaxByteSize 설정 멤버는 참조 혹은 값 타입 가능 IBinarySerialize 구현 Read(BinaryReader r) Write(BinaryWriter w) MaxByteSize 설정 8000 까지 IsFixedSize=true 권장, 모든 인스턴스가 동일 크기인 경우 IsByteOrdered=true, 정렬, 기본 키인 경우

사용자 정의 Aggregates UDA 를 .NET 클래스로 작성 Aggregate 사용 누적 계산 특수한 산순 연산을 위해서 적용 컴파일된 .NET 컴포넌트로서 유용 Aggregate 사용 GROUP BY 저장 프로시저, 함수 등의 내부 OVER 절

사용자-정의 Aggregate 구현 단일 스칼라 타입으로 정의 필수 구현 public void Init() 집계 관련 각 그룹 별 한 번 호출 public void Accumulate(input-type Value) 집합의 각 멤버 별 한 번 호출 public void Merge(udagg_class Group) 부분 결과(다른 인스턴스)를 병합하기 위해 호출 public input_type Terminate() 집계를 완료하고 결과를 반환

사용자-정의 Aggregate 힌트 쿼리 최적화기의 효율적 실행 계획 검색을 지원 최적화기 힌트(optimizer hint)는 아님 4가지 특수한 SqlUserDefinedAggregate 속성 IsInvariantToDuplicates 중복 값에 따라 집계 결과가 변하지 않는 경우 true 예 – MIN, MAX IsInvariantToNulls NULL 값에 따라 집계 결과가 변하지 않는 경우 true 예 – MIN, AVG, 등 IsInvariantToOrder 순서에 영향을 받지 않는 경우, 플랜 선택에 유연성 지원 IsNullIfEmpty 빈 그룹이며, 집계 값이 없는 경우 NULL을 반환하면 true

SQLCRL vs. T-SQL 최적 사례 CLR은 집합 기반 쿼리 처리에 대한 대체 기능이 아니다 SQL Server 2005에서 확장된 쿼리 언어의 이득을 적용 단일 쿼리 이상의 내용을 수행하기 위한 CLR 함수로 사용 기존 Transact-SQL 스칼라 함수를 CLR 로 대체 고려 미들 티어로 전송되는 데이터를 줄이기 위한 함수로서 비즈니스 로직 포함을 고려

미들-티어 vs. 서버 최적 사례 서버에서의 CLR 지원이 모든 로직을 서버로 이동함을 의미하지는 않는다 선택: 서버에 부하를 증가시키는 것과 네트워크 데이터 이동 및 라운드 트립을 줄이는 이득

요약 .NET 런타임이 SQL Server 2005 프로세스 내에서 실행 .NET 개념과 SQL 개념의 통합 확장성, 신뢰성, 성능에 대한 특별한 고려 .NET 개념과 SQL 개념의 통합 어셈블리는 데이터베이스 내에 저장 T-SQL 과 같은 저장 프로시저, 함수, 트리거 UDTs 와 UDAggs - .NET 전용 .NET in-proc 데이터 액세스에 최적화된 라이브러리 SqlContext, SqlPipe, SqlDataRecord, etc T-SQL 과 .NET 는 각기 최적 사용을 권장

참조 자료 마이크로소프트 공인 교육 과정 외부 교육 과정, developmentor 서적 Updating Your Database Development Skills to Microsoft SQL Server 2005 외부 교육 과정, developmentor Essential SQL Server 2005 for Developers 서적 A First Look at SQL Server 2005 for Developers, Addison Wesley SQL Server 2005 FOR DEVELOPERS, Microsoft Press SQL Server 2005 Developer Center http://msdn.microsoft.com/SQL/2005/ Managed Data Access Inside SQL Server with ADO.NET and SQLCLR (Visual Studio 2005 Beta2 기준) http://msdn.microsoft.com/sql/2005/2005articles/default.aspx?pull=/library/en-us/dnsql90/html/mandataaccess.asp Using CLR Integration in SQL Server 2005 http://msdn.microsoft.com/sql/2005/2005articles/default.aspx?pull=/library/en-us/dnsql90/html/sqlclrguidance.asp