SQL Server 2005에서 CLR 통합 사용

DataBase 2006. 3. 27. 17:43

SQL Server 2005에서 CLR 통합 사용

Balaji Rathakrishnan
Christian Kleinerman
Brad Richards
Ramachandran Venkatesh
Vineet Rao
Isaac Kunen

발행일: 2005년 5월

요약: 이 문서에서는 SQL Server 2005의 새로운 CLR 통합 기능에 대해 설명하고 또한 데이터베이스 응용 프로그램 개발자와 설계자가 그 기능을 사용하여 사용자 정의 프로시저, 함수, 트리거를 작성하고 나아가 새로운 형식 및 집계를 정의하는 방법에 대해 설명합니다.

Transact-SQL과 확장 저장 프로시저 등과 같은 SQL Server에서 지원하는 기존 프로그래밍 모델과 CLR 기반 프로그래밍을 비교하고 각 기법의 상대적 장점과 단점을 설명하며 사용 가능한 프로그래밍 대안 중에서 하나를 선택하는 방법에 대한 자세한 지침을 제공합니다. 또한 CLR 통합 기능을 예시하는 코드 예제를 제공합니다.

목차

소개
CLR 통합의 개요
수동 배포
Visual Studio를 사용한 작성, 배포 및 디버깅
CLR 및 대안
CLR과 Transact-SQL 비교
CLR과 XP 비교
중간 계층의 코드
예제: 생산 일정
일반 데이터베이스 프로그래밍 작업 및 문제 해결
.NET Framework 라이브러리를 사용한 데이터 유효성 검사
결과 집합 생성
데이터에 대한 사용자 지정 집계 수행
사용자 정의 형식
결론

소개

Microsoft SQL Server 2005는 Microsoft .NET Framework 2.0 공용 언어 런타임(CLR)을 채용하여 데이터베이스 프로그래밍 모델을 크게 향상시켰습니다. 이 기능을 이용하여 개발자는 모든 CLR 언어, 특히 Microsoft Visual C# .NET, Microsoft Visual Basic .NET, Microsoft Visual C++를 사용하여 프로시저, 트리거, 함수를 작성할 수 있습니다. 또한 개발자가 새로운 형식과 집계를 사용하여 데이터베이스를 확장할 수 있습니다.

이 문서에서는 CLR 통합 기법을 SQL 서버에서 기존에 지원하던 프로그래밍 언어, 즉 Transact-SQL(T-SQL)과 확장 저장 프로시저(XP)에 비교하여, 데이터베이스 응용 프로그램 개발자 관점에서 이 기술을 최대한 이용하는 방법을 설명하고 이 문서는 이러한 기능에 대한 참조용이 아닙니다. 참조 정보가 필요할 경우 SQL Server 2005 온라인 설명서를 참조하십시오. 이 문서의 예제는 SQL Server 2005의 2005년 4월 CTP 이후의 사용을 전제로 합니다.

이 문서는 데이터베이스 개발자, 설계자, 관리자를 대상으로 쓰여졌습니다. 이 문서는 .NET Framework 기반 프로그래밍과 데이터베이스 프로그래밍에 대한 실질적 지식을 갖추고 있다고 가정합니다.

CLR 통합의 개요

다음에서는 CLR 통합에서 사용할 수 있는 SQL Server 기능과 Visual Studio 2005가 이러한 기능을 지원하는 방식에 대해 간략히 설명합니다.

수동 배포

데이터베이스에서 관리되는 코드의 등록과 실행은 다음 단계로 구성됩니다.

  1. 개발자가 클래스 정의 집합으로서 관리되는 프로그램을 작성합니다. SQL Server 루틴(저장 프로시저, 함수, 트리거)은 클래스의 static (Microsoft Visual Basic .NET의Shared) 메서드로 작성됩니다. 사용자 정의 형식 및 집계가 전체 클래스로 작성됩니다. 개발자가 코드를 컴파일하고 어셈블리를 만듭니다.
  2. 어셈블리는 SQL Server 데이터베이스에 업로드되고 CREATE ASSEMBLY 데이터 정의 언어(DDL) 문을 사용하여 시스템 카탈로그에 저장됩니다.
  3. 루틴, 형식, 집계 등 Transact-SQL(T-SQL) 개체가 작성되고, 이미 업로드된 어셈블리의 진입점(루틴의 경우 메서드, 형식 및 집계의 경우 클래스)에 바인딩됩니다. 이러한 작업은 CREATE PROCEDURE/FUNCTION/TRIGGER/TYPE/AGGREGATE 문을 사용하여 수행됩니다.
  4. 루틴이 작성되면 응용 프로그램에서 T-SQL 루틴과 같이 사용할 수 있습니다. 예를 들어 T-SQL 프로시저인 것처럼 CLR 함수는 T-SQL 쿼리에서 호출될 수 있으며 CLR 프로시저는 클라이언트 응용 프로그램 또는 T-SQL 배치에서 호출될 수 있습니다.

Visual Studio를 사용한 작성, 배포 및 디버깅

Visual Studio 2005는 SQL Server 2005에서의 관리되는 코드 개발, 배포 디버깅을 지원합니다. 새로운 SQL Server 프로젝트는 개발자가 CLR 기반 데이터베이스 루틴, 형식, 집계에 대한 코드 작성을 쉽게 시작할 수 있도록 지원하는 코드 템플릿을 제공합니다. 또한 이 프로젝트를 사용하여 데이터베이스의 다른 어셈블리에 참조를 추가할 수 있습니다.

SQL Server 프로젝트가 작성되면 어셈블리로 컴파일됩니다. 프로젝트를 배포하면 프로젝트와 연결된 SQL Server 데이터베이스로 어셈블리 이진수가 업로드됩니다. 또한 배포 작업을 수행하면 코드의 사용자 지정 속성(SqlProcedure, SqlFunction, SqlTrigger 등)을 기준으로 데이터베이스의 어셈블리에서 정의된 루틴, 형식, 집계가 자동으로 만들어집니다. 또한 배포 시 소스 코드와 어셈블리와 연결된 디버깅 기호(.pdb 파일)가 업로드됩니다.

어떤 플랫폼이든 디버깅은 개발자 작업의 기본적 부분이므로 SQL Server 2005와 Visual Studio 2005는 데이터베이스 프로그래머에게 그러한 기능을 제공합니다. SQL Server 2005 개체 디버깅의 주요 특징은 설치 및 사용의 용이성입니다. SQL Server가 실행되는 컴퓨터로의 연결은 일반 운영 체제에서 실행되는 프로세스와 거의 동일한 방식으로 디버깅됩니다. 디버거의 기능은 클라이언트가 취하는 서버 연결 유형에 의해 영향을 받지 않습니다. TDS(Tabular Data Stream) 및 HTTP 연결을 모두 디버깅할 수 있습니다. 뿐만 아니라 디버깅은 모든 언어에서 원활하게 기능하므로 사용자가 T-SQL에서 CLR 메서드로 또는 그 반대로 디버깅할 수 있습니다.

CLR 및 대안

CLR 통합의 사용을 평가할 때 개발자는 다른 사용 가능한 옵션과 비교해야 합니다. 여기에서는 그러한 비교의 기초를 제공하여 기존 프로그래밍 기법(즉 Transact-SQL, 확장 저장 프로시저, 중간 계층의 코드)과 비교하여 CLR 통합의 장점을 파악할 수 있도록 합니다. 이 단원에서는 사용자 정의 루틴을 중심으로 설명합니다.

CLR과 Transact-SQL 비교

Transact-SQL(T-SQL)은 SQL Server가 지원하는 기본 프로그래밍 언어입니다. 대부분의 SQL 버전과 같이 T-SQL에는 데이터 조작 기능과 데이터 정의 기능이 있습니다. 데이터 조작 기능은 크게 두 부분으로 분류할 수 있습니다. 그 하나는 선언적 쿼리 언어(SELECT/INSERT/UPDATE/DELETE 문으로 구성됨)이고 다른 하나는 절차 언어(WHILE, 할당, 트리거, 커서 등)입니다. 개략적으로 말하면 SQL Server의 CLR 지원은 T-SQL의 절차 부분에 대한 대안입니다.

CLR 지원이 없는 경우에도 데이터베이스 응용 프로그램에서 가능한 한 많이 선언적 쿼리 언어를 사용한다는 점을 알아야 합니다. 언어의 이 부분은 대량 작업을 가장 잘 최적화하고 수행할 수 있는 쿼리 프로세서의 기능을 사용할 수 있습니다. 데이터베이스 응용 프로그램은 절차 프로그래밍에만 의지하여 쿼리 언어 내에서 표현할 수 없는 로직을 표현합니다.

이러한 점은 SQL Server의 CLR 지원에서도 마찬가지입니다. T-SQL 언어의 선언적 기능을 사용하여 표현할 수 있는 절차적 코드를 작성하는 데 CLR을 사용하지 않습니다. 개발자는 SQL Server 2005의 T-SQL 쿼리 언어에 많은 중요한 향상 기능이 있어 T-SQL 쿼리 언어의 표현력이 강화되었다는 것을 알고 있어야 하며 CLR을 사용하든 사용하지 않든 절차적 코드를 작성하기 전에 T-SQL 기능을 십분 활용할 수 있어야 합니다. 이러한 추가 기능은 다음과 같습니다.

  • 테이블에서 재귀 계층을 통과하는 재귀 쿼리를 작성하는 기능
  • 결과 집합에서 행의 순위를 지정할 수 있는 RANK 및 ROW_NUMBER와 같은 분석 함수
  • EXCEPT, INTERSECT, APPLY, PIVOT, UNPIVOT과 같은 새로운 관계형 연산자

개발자는 쿼리 언어로 선언적으로 표현할 수 없는 로직에 대한 효과적인 대안으로서 CLR을 생각해야 합니다.

CLR 기반 프로그래밍으로 T-SQL 쿼리 언어의 표현력을 보완할 수 있는 시나리오를 검토해 보겠습니다. 종종 쿼리 내에 함수라고 할 수 있는 절차적 로직을 삽입할 필요가 있습니다. 다음과 같은 경우가 이에 해당합니다.

  • 데이터베이스 테이블에 저장된 값에 대하여 행 기준으로 복잡한 계산(절차적 로직을 사용하여 표현해야 하는 계산)을 수행하는 경우 여기에는 이러한 계산 결과를 클라이언트로 전송하는 작업 또는 다음 예제와 같이 계산을 사용하여 클라이언트로 전송된 행 집합을 필터링하는 작업이 포함될 수 있습니다. SELECT <complex-calculation>(<column-name>,...)
    FROM <table>
    WHERE <complex-calculation>(<column-name>,...) = ...
  • 절차적 로직을 사용하여 SELECT 또는DML 문의 FROM 절에서 쿼리되는 테이블 형식 결과를 평가하는 작업

SQL Server 2000에서는 이러한 시나리오를 수행할 수 있는 T-SQL 함수(스칼라 함수 및 테이블 반환 함수)를 도입했습니다. SQL Server 2005에서는 개발자가 .NET Framework API에서 훨씬 더 광범위한 라이브러리를 이용할 수 있기 때문에 CLR 언어를 사용하여 이러한 함수를 쉽게 작성할 수 있습니다. 이 외에도 CLR 프로그래밍 언어는 T-SQL에서는 지원하지 않는 다양한 데이터 구조(배열 목록 등)를 제공하고 CLR과 T-SQL의 여러 실행 모델 덕분에 성능이 크게 향상되었습니다.

일반적으로 함수 내에서 데이터베이스에 액세스할 필요가 거의 없기 때문에 함수는 CLR을 사용하여 작성하기에 좋습니다. 함수에서는 보통 인수로서 데이터베이스의 값이 전달됩니다. 이 점이 CLR의 장점이며 T-SQL보다 연산 작업에 더 유리합니다.

CLR에서 데이터 액세스

데이터 액세스를 수행하는 루틴을 작성하는 옵션으로서의 CLR을 프로그래밍 모델 관점과 성능 관점에서 살펴보겠습니다.

T-SQL에서 SELECT, INSERT, UPDATE, DELETE와 같은 쿼리 언어 문은 절차적 코드에 삽입됩니다. 한편 관리되는 코드는 SQL Server(SqlClient)용 ADO.NET 데이터 액세스 공급자를 사용합니다. 이 접근 방법에서 모든 쿼리 언어 문은 인수로서 ADO.NET API의 속성과 메서드에 전달되는 동적 문자열로 표현됩니다.

이 차이 때문에 CLR을 사용하여 작성된 데이터 액세스 코드가 T-SQL보다 더 자세할 수 있습니다. 더욱 중요한 것은, SQL 문이 동적 문자열로 인코딩되기 때문에 SQL 문이 실행될 때까지 컴파일되거나 검사되지 않아 코드의 디버깅과 성능에 효과를 미친다는 점입니다. 그러나 ADO.NET을 이용한 데이터베이스 프로그래밍 모델은 클라이언트 또는 중간 계층에서 사용되는 모델과 매우 유사하며 계층 간에 코드를 이동하고 기존 기술을 사용하기가 용이합니다.

다시 한번 강조하여 설명하면 T-SQL과 CLR 기반 프로그래밍 모델은 동일한 SQL 쿼리 언어를 사용하며 단지 절차적 부분만 다릅니다.

앞에서 이미 언급한 바와 같이 관리되는 코드는 대부분의 절차적 계산 면에서 T-SQL에 비해 확실한 성능 이점이 있지만 데이터 액세스의 경우에는 T-SQL이 일반적으로 더 낫습니다. 그러므로 일반적으로 CLR 구현의 경우 계산 중심 및 로직 중심 코드가 데이터 액세스 중심 코드보다 더 나은 선택입니다.

데이터 액세스 프로그래밍의 일반적인 기본 사항 및 패턴을 살펴보고 통합된 CLR과 ADO.NET을 사용하는 관리되는 프로그래밍과 T-SQL이 이러한 시나리오에서 수행하는 방식을 비교해 보겠습니다.

클라이언트로 결과 전송

첫 번째 시나리오는 서버에서 행 집합을 "소비하지" 않고 클라이언트로 행 집합을 전송합니다. 즉 루틴 내에서 행의 탐색이 수행되지 않습니다. T-SQL의 경우 단순히 T-SQL 프로시저에 SELECT 문을 삽입하면 SELECT에 의해 생성된 행이 클라이언트로 전송됩니다. 관리되는 코드의 경우 SqlPipe 개체를 사용하여 결과를 클라이언트로 전송합니다. 이 시나리오에서 T-SQL과 ADO.NET의 성능은 거의 같습니다.

SQL 문 제출

CLR에서 SQL 문의 제출은 관리되는 코드와 기본 SQL 코드 간을 전환하려면 코드의 추가 계층을 통과해야 합니다. 이 때문에 T-SQL이 SQL 쿼리를 실행할 때 성능 우위를 보입니다. 쿼리 프로세서에 문이 제출된 후 문의 소스(T-SQL 또는 관리되는 코드)를 기준으로 하면 성능 면에서 차이가 없습니다. 쿼리가 복잡하고 평가하는 데 오랜 시간이 걸릴 경우 T-SQL과 관리되는 코드 간의 성능 차이는 미미합니다. 짧고 단순한 쿼리의 경우 추가 코드 계층의 오버헤드가 관리되는 프로시저의 성능에 영향을 미칠 수 있습니다.

일반적인 데이터 액세스 중심의 저장 프로시저는 일련의 SQL 문 제출을 수반할 가능성이 큽니다. SQL 문이 단순하고 실행하는 데 상당한 시간이 걸리지 않을 경우 관리되는 코드의 호출 오버헤드가 실행 시간의 대부분을 차지할 수 있습니다. 그러한 프로시저는 T-SQL로 쓰여질 경우 성능이 더 좋습니다.

전진 전용, 읽기 전용 행 탐색

T-SQL에서 전진 전용 탐색과 읽기 전용 탐색은 커서를 사용하여 구현됩니다. CLR 코드에서는 SqlDataReader를 사용하여 구현됩니다. 일반적으로 각 데이터에 대해 약간의 프로세싱이 수행됩니다. 이것을 무시할 경우 CLR을 사용하는 행 탐색이 T-SQL을 사용하는 것보다 약간 느리기 때문에 T-SQL이 유리합니다. 그러나 데이터에 대해 수행되는 모든 프로세싱에서 CLR이 T-SQL보다 성능이 훨씬 우수하기 때문에 프로세싱 시간이 증가할 때 CLR 성능이 T-SQL의 성능을 능가합니다.

또한 T-SQL 커서를 사용할 경우 대기 시간이 증가할 가능성이 있다는 것을 알아야 합니다. 일부 쿼리가 중간 결과를 구체화해야 하므로 약간의 대기 시간을 반드시 발생시키지만 STATIC 또는 KEYSET 커서는 결과를 생성하기 전에 임시 테이블에 최종 결과 집합을 항상 구체화합니다. 커서는 STATIC 또는 KEYSET으로서 명시적으로 선언되거나 쿼리와 데이터의 특정 속성 때문에 암시적으로 변환될 수 있습니다. CLR SqlDataReader는 결과가 사용 가능해질 때 결과를 생성하여 이러한 대기 시간의 발생을 방지합니다.

업데이트 시 행 탐색

커서의 현재 위치를 기준으로 행 업데이트가 문제에 포함될 경우 적절한 성능 비교가 없습니다. 이 기능은 ADO.NET에서 지원되지 않으며 T-SQL 업데이트 가능 커서를 사용하여 수행해야 합니다. 그러나 선언적 SQL로 표현할 수 없을 경우 커서 기반 수정을 제외하고 UPDATE 문을 사용하여 대량으로 행을 업데이트하는 것이 일반적으로 더 좋습니다.

요약

다음은 지금까지 살펴본, CLR과 T-SQL 중 하나를 선택할 때 사용할 수 있는 지침에 대한 요약입니다.

  • 가능할 경우 선언적 T-SQL SELECT, INSERT, UPDATE, DELETE 문을 사용합니다. 절차 및 행 기반 처리는 선언적 언어를 사용하여 로직을 표현할 수 없는 경우에만 사용해야 합니다.
  • 프로시저가 단순히 선언적 T-SQL 명령에 대한 래퍼인 경우 T-SQL로 프로시저를 작성해야 합니다.
  • 프로시저가 각 행에 대한 특정 프로세싱과 함께 전체 결과 집합에 대한 전송 전용과 읽기 전용 탐색을 기본적으로 수반할 경우 CLR을 사용하는 것이 더 효율적입니다.
  • 프로시저가 상당한 데이터 액세스와 계산을 수반하는 경우, T-SQL 프로시저로 호출하여 데이터 액세스를 수행하는 CLR 부분 또는 CLR로 호출하여 계산을 수행하는 T-SQL 프로시저로 절차적 코드를 분리하는 것을 고려하십시오. 또 다른 대안은 관리되는 코드에서 한 번 실행하여 관리되는 코드에서 T-SQL 문을 제출하는 왕복 횟수를 줄이는 쿼리 집합을 포함하는 단일 T-SQL 배치를 사용하는 것입니다.

결과 집합에서 작업할 때 T-SQL과 CLR의 적절한 사용 시기 및 방법에 대해서는 후반부의 단원에서 자세히 설명합니다.

CLR과 XP 비교

SQL Server의 이전 릴리스에서는 확장 저장 프로시저(XP)가 T-SQL로 작성하기 어려운 로직이 포함된 서버 쪽 코드를 작성할 경우 T-SQL의 유일한 대안이었습니다. 이제 CLR 통합은 XP에 대한 더 우수한 대안을 제공합니다. 이 외에도 CLR 통합을 사용할 경우 많은 저장 프로시저가 테이블 반환 함수로 더 잘 표현될 수 있으며 저장 프로시저에서 쿼리 언어를 사용하여 테이블 반환 함수를 호출하고 조작할 수 있습니다.

XP와 비교하여 CLR 프로시저를 사용할 경우의 이점은 다음과 같습니다.

  • 세분화된 제어: SQL Server 관리자는 XP가 할 수 있는 작업과 할 수 없는 작업에 대하여 거의 제어할 수 없습니다. 코드 액세스 보안 모델을 사용할 경우 SQL Server 관리자는 세 권한 버킷(SAFE, EXTERNAL_ACCESS, UNSAFE)을 지정하여 관리되는 코드가 수행할 수 있는 작업에 대해 다양한 수준의 제어를 행할 수 있습니다.
  • 신뢰성: 관리되는 코드가 특히 SAFE 및 EXTERNAL_ACCESS 권한 집합이 지정된 경우 XP보다 더 신뢰 가능한 프로그래밍 모델을 제공합니다. 검증 가능한 관리되는 코드는 개체에 대한 모든 액세스가 강력하게 형식화된 인터페이스를 통하여 수행되도록 보장하고 프로그램이 SQL Server에 속하는 메모리 버퍼에 액세스하거나 그 버퍼를 손상시킬 가능성을 줄입니다.
  • 데이터 액세스: XP를 사용할 경우 로컬 SQL Server 데이터베이스에 액세스하려면 데이터베이스에 다시 명시적 연결(루프백 연결)을 수행하여야 합니다. 그리고 XP가 호출된 트랜잭션에 참여하도록 하려면 이 루프백 연결을 원래 세션의 트랜잭션 컨텍스트에 명시적으로 바인딩해야 합니다. 관리되는 CLR 코드가 현재의 연결 및 트랜잭션 컨텍스트를 이용하는 더 자연스럽고 효율적인 프로그래밍 모델을 사용하여 로컬 데이터에 액세스할 수 있습니다.
  • 기타 데이터 형식: 관리되는 API는 SQL Server 2005에서 도입된 새 데이터 형식(예: XML, (n)varchar(max), varbinary(max))을 지원하지만 ODS API는 이러한 새 형식을 지원할 수 있도록 확장되지 않았습니다.
  • 확장성: 메모리, 스레드와 같은 리소스를 노출하는 관리되는 API와 동기화가 SQL Server 리소스 관리자 상단에서 구현되며 이를 통하여 SQL Server에서 CLR 코드에 대한 이러한 리소스를 관리할 수 있습니다. 역으로 말하면 SQL Server에는 XP의 리소스 사용에 대한 보기 또는 컨트롤이 없습니다. 따라서 XP가 CPU 시간 또는 메모리를 너무 많이 사용할 경우에 SQL Server에서 이를 발견하고 제어할 방법이 없습니다. CLR 코드를 사용할 경우에는 특정 스레드가 장시간 동안 완료되지 않을 경우 SQL Server가 그 사실을 감지하고 다른 작업을 예약할 수 있도록 그 작업을 강제로 완료할 수 있습니다. 결과적으로 관리되는 코드를 사용하는 것이 확장성과 견고성 면에서 더 우수합니다.

위에서 언급한 바와 같이 데이터 액세스와 클라이언트로 결과 집합의 전송 면에서 CLR 루틴이 XP보다 성능이 우수합니다. 데이터 액세스나 결과 전송을 포함하지 않는 코드의 경우 XP와 관리되는 코드의 성능 비교는 관리되는 코드와 기계어 코드의 비교입니다. 일반적으로 이러한 시나리오에서 관리되는 코드의 성능이 기계어 코드의 성능을 능가할 수 없습니다. 게다가 기계어 코드로 또는 그 반대로 전환될 때 SQL Server가 스레드별 설정에 대한 추가적인 기입(book-keeping) 작업을 수행해야 하기 때문에 SQL Server에서 관리되는 코드가 실행될 때 관리되는 코드에서 기계어 코드로 전환 시 추가적 비용이 발생합니다. 결과적으로 관리되는 코드와 기계어 코드 간에 빈번하게 전환이 수행될 경우 XP가 SQL Server에서 실행되는 관리되는 코드보다 성능 면에서 우수합니다.

대부분 프로시저의 경우 관리되는 코드의 장점 때문에 CLR 프로시저가 XP보다 더 매력적인 대안이 됩니다. 성능이 주로 계산 중심 프로세싱과 빈번한 관리되는 코드와 기계어 간 전환에 의하여 결정되는 경우 CLR의 이점은 XP의 성능 이점과 비교하여 평가되어야 합니다.

중간 계층의 코드

개발자가 선택할 수 있는 또 다른 옵션은 로직을 데이터베이스 외부에 위치시키는 것입니다. 그러면 개발자는 선택한 언어로 코드를 작성할 수 있습니다. CLR 통합은 데이터베이스에서 다양한 프로그래밍 모델을 제공함으로써 개발자는 그러한 로직을 데이터베이스 내부로 이동시키는 옵션을 사용할 수 있습니다. 물론 이것이 모든(또는 대부분) 코드를 데이터베이스로 이동해야 한다는 의미는 아닙니다.

로직을 데이터베이스 계층으로 이동하면 네트워크에서 이동하는 데이터의 양을 줄일 수 있으나 서버의 귀중한 CPU 리소스에 추가 로드가 부과됩니다. 응용 프로그램의 코드 배치 결정을 내리기 전에 이 장단점을 신중하게 고려해야 합니다. 다음 사항을 고려한다면 기본 코드 위치로서 데이터베이스 계층을 선택하는 것이 더 유리할 수 있습니다.

  • 데이터 유효성: 데이터베이스에서 데이터 유효성 검사 로직을 유지할 경우 데이터와 이 로직의 캡슐화가 더 우수하며 백 엔드 프로세싱, 대량 업로드, 중간 계층에서 데이터 업데이트 등 서로 다른 데이터 접점에서 유효성 검사 로직의 중복을 방지합니다.
  • 네트워크 트래픽 감소: 많은 양의 데이터를 처리하지만 매우 작은 비율의 데이터를 생성하는 데이터 처리 작업의 경우 데이터베이스에 로직을 두는 것이 적합할 수 있습니다. 전형적인 예로 수요 예측, 예측 수요에 근거한 생산 계획 등의 데이터 분석 응용 프로그램이 있습니다.

물론 CLR 통합이 아니더라도 이러한 고려 사항은 중요합니다. CLR 통합은 단지 프로그래밍 언어 선택이 올바른 코드 위치 결정을 방해하지 않도록 도와줍니다.

예제: 생산 일정

생산 계획은 제조 회사의 일반적인 작업입니다. 높은 수준에서 생산 계획은 품목을 생산하고 저장하는 총 비용을 최소화하면서 수요를 충족하기 위해 품목을 언제 생산할 것인가에 대한 계획을 수립하는 작업을 수반합니다. 입력으로서 수요 예측, 재고 보관 비용, 생산 라인 설치 비용을 사용하고 출력으로서 생산 전략을 산출하는 여러 알고리즘이 있습니다.

미래 수요 예측을 SQL Server의 테이블에 저장할 경우 그러한 알고리즘의 구현에는 다음과 같은 특징이 있습니다.

  1. 입력으로서 많은 양의 데이터(수요 예측)를 사용합니다.
  2. 적은 양의 결과(해당 일자에 생산할 단위 수 등)를 산출합니다.
  3. 입력에서 결과를 도출하기 위해 상당한 계산이 필요합니다.

중간 계층에서 그러한 알고리즘을 구현하는 것이 하나의 옵션이긴 하지만 데이터베이스에서 수요 데이터를 전달하는 데 큰 비용이 발생할 것입니다. 저장 프로시저로서 T-SQL로 그러한 알고리즘을 구현하는 것도 가능하지만 복잡한 데이터 형식이 없기 때문에 구현이 어렵고 필요한 계산의 양과 복잡성 때문에 T-SQL의 성능이 문제가 될 것입니다. 물론 성능 특성은 실제 데이터 양과 알고리즘의 복잡성에 따라 달라질 것입니다.

이러한 시나리오에 대한 CLR 통합의 적합성을 검증하기 위해 특정 생산 계획 알고리즘(Wagner-Whitin 알고리즘의 동적 프로그래밍 구현)을 선택하고 CLR과 T-SQL을 사용하여 구현하였습니다. 예상한 대로 CLR 통합이 T-SQL보다 성능이 크게 앞섰습니다. 또한 알고리즘이 T-SQL에서 사용할 수 없는 단차원 및 다차원 배열을 사용하기 때문에 C#으로 구현하는 작업이 수월했습니다. 전체적으로 T-SQL 구현보다 CLR 버전의 성능이 월등하게 우수하였습니다.

다음과 같은 생산할 수 있는 제품 목록을 추적하는 단순한 데이터베이스 스키마를 가정해 봅시다.

테이블 t_products:

열 이름형식설명
pidint제품의 기본 키 ID
pNamenvarchar(256)제품 이름
inventoryCostint기간 당 이 제품의 보관 비용
startupCostint이 제품의 제조를 시작하기 위한 제조 라인의 설치 비용

그리고 다음 테이블에 제품 당 하루 수요 예측 정보를 저장합니다. pid 열이 테이블 t_products의 외래 키이고 pid, demandDate 쌍에 대한 고유성 제약 조건이 있다고 가정합니다.

테이블 t_salesForecast:

열 이름형식설명
pidint제품 ID
demandDatesmalldatetime수요가 예측되는 일자
demandQtyint해당 제품에 대한 수요 예측

이 제품 데이터 외에 생산 계획을 만들 날짜 범위를 나타내는 매개 변수를 사용하는 저장 프로시저를 만들었습니다.

이 저장 프로시저가 다음 테이블에 이 스키마가 지정된 행 집합을 반환합니다.

열 이름형식설명
productnvarchar(256)제품 이름
perioddatetime생산일
quantityint제조할 품목의 수량

아래에 표시된 코드의 C# 버전은 CLR 통합의 이점을 두드러지게 이용할 수 있는 시나리오를 보여줍니다.

using System;
using System.Data;
using System.Collections;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public partial class ProductionScheduler
{
const int MAXNAME = 256; // The maximum name size

[Microsoft.SqlServer.Server.SqlProcedure] // Flag as a SQL procedure
public static void Schedule(SqlDateTime start, SqlDateTime end)
{
// Guarantee that we have a valid connection while we run
using (SqlConnection conn =
new SqlConnection("context connection=true"))
{
conn.Open(); // open the connection
SqlPipe pipe = SqlContext.Pipe; // get the pipe

// Find all the products in the database with any demand
// whatsoever along with data about their production costs.
// Make sure they are ordered.
ArrayList items = new ArrayList();
SqlCommand cmd = new SqlCommand(
" SELECT DISTINCT tp.pid, pname, startupCost,"
" inventoryCost" +
" FROM t_products tp" +
" JOIN t_salesForecast ts" +
" ON tp.pid = ts.pid" +
" ORDER BY pid",
conn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
items.Add(new Item(
reader.GetInt32(0), reader.GetSqlChars(1),
reader.GetInt32(2), reader.GetInt32(3)));
}
reader.Close();

// Now get all the production schedule information, ordered
// by PID and demand date
"SELECT pid, demandDate, demandQty" +
" FROM t_salesForecast" +
" WHERE demandDate >= @start" +
" AND demandDate <= @end" +
" ORDER BY pid, demandDate",
conn);
cmd.Parameters.AddWithValue("@start", start);
cmd.Parameters.AddWithValue("@end", end);
reader = cmd.ExecuteReader();

// Read each section of schedule information into the items.
reader.Read();
for (int i = 0; (i < items.Count) && (!reader.IsClosed); i++)
{
((Item)(items[i])).readData(reader);
}

// ensure the reader is closed
if (!reader.IsClosed) reader.Close();


foreach (Item item in items)
{
// Compute the schedule and report it
item.ComputeSchedule();
item.OutputSchedule(pipe);
}
}
}

class Item
{
// Information about the product we are scheduling. These will
// be pulled from the database.
private int pid;
private SqlChars name;
private int startCost;
private int holdCost;

// Store how many dates we have.
private int size = 0;

// The dates on which we have demand. These are guaranteed to
// be unique by the database, and we will load them in order.
private ArrayList dates = new ArrayList();
// Store what the demand was on each date.
private ArrayList quantities = new ArrayList();

// Our schedule, which we have not yet computed.
int[] schedule = null;

// Set up the metadata for the return
SqlMetaData[] metadata = new SqlMetaData[] {
new SqlMetaData("product", SqlDbType.NVarChar, MAXNAME),
new SqlMetaData("period", SqlDbType.DateTime),
new SqlMetaData("quantity", SqlDbType.Int)
};

public Item(int pid, SqlChars name, int startCost, int holdCost)
{
this.pid = pid;
this.name = name;
this.startCost = startCost;
this.holdCost = holdCost;
}

/*
* Read data from the stream until the PID does not match
* ours anymore. We assume the reader is cued up to our
* information and we leave it cued to the next item's
* information UNLESS there is no more information, in which
* case we close the reader to indicate as much.
*/
public void readData(SqlDataReader reader)
{
size = 0;
do
{
if (reader.GetInt32(0) == pid)
{
size++;
dates.Add(reader.GetDateTime(1));
quantities.Add(reader.GetInt32(2));
}
else
{
return;
}
}
while (reader.Read());

// reader ran out. close it.
reader.Close();
}

/*
* This method is called to compute the production schedule
* for the item. It does no I/O, but puts in motion the
* dynamic programming algorithm which produces the schedule.
*/
public void ComputeSchedule()
{
int[] days = ComputeProductionDays();
schedule = new int[size];
for (int i = 0; i < size; i++)
{
schedule[days[i]] += (Int32)(quantities[i]);
}
}

/*
* Pipe the schedule to the user, computing it if need be.
*/
public void OutputSchedule(SqlPipe pipe)
{
// Ensure that the schedule has been computed.
if (schedule == null)
{
ComputeSchedule();
}

// Make a record in which to store the data.
SqlDataRecord record = new SqlDataRecord(metadata);
record.SetSqlChars(0, name);

// Start up the output pipe.
pipe.SendResultsStart(record);
for (int i = 0; i < size; i++)
{
// Pipe each day of production. Omit zero production
// days.
if (schedule[i] != 0)
{
record.SetDateTime(1, (DateTime)(dates[i]));
record.SetInt32(2, schedule[i]);
pipe.SendResultsRow(record);
}
}
pipe.SendResultsEnd();
}

/*
* Compute the table and then walk it to find the best
* days to produce the item.
*/
private int[] ComputeProductionDays()
{
// We fill this in. It says when each day's quota is
// actually produced.
int[] productionDays = new int[size];

// First, compute the table.
int[][] table = ComputeTable();

// Then walk the table, creating a second table which encodes
// the best production days.
int[] optimal = new int[size + 1];
int[] optimalLoc = new int[size];
optimal[size] = 0;
for (int i = size - 1; i >= 0; i--)
{
int min = table[i][i] + optimal[i + 1];
int minloc = i;
for (int j = i+1; j < size; j++)
{
int temp = table[i][j] + optimal[j + 1];
if (temp < min)
{
min = temp;
minloc = j;
}
}
optimal[i] = min;
optimalLoc[i] = minloc;
}

// Finally, decode the optimal values into production days.
int pday = 0;
int until = optimalLoc[0] + 1;
for (int i = 0; i < size; i++)
{
if (until == i)
{
pday = i;
until = optimalLoc[i] + 1;
}
productionDays[i] = pday;
}

// We now have a list of days which we will produce the good.
return productionDays;
}

/*
* The main part of the dynamic programming solution. Each entry
* table[i,j] stores the cost of producing enough of the good on
* day i to meet needs through day j. This table is only half-
* filled when complete.
*/
private int[][] ComputeTable()
{
int[][] table = new int[size][];
for (int i = 0; i < size; i++) table[i] = new int[size];
for (int i = 0; i < size; i++)
{

// If we produce the good on the same day we ship it we
// incur a startup cost.
table[i][i] = startCost;

// For other days, we have the cost for the previous
// cell plus the cost of storing the good for this long.
for (int j = i + 1; j < size; j++)
{
table[i][j] = table[i][j - 1] +
(((int)quantities[j]) * holdCost *
diff((DateTime)(dates[i]), (DateTime)(dates[j])));
}
}
return table;
}

/*
* A utility to compute the difference between two days.
*/
private int diff(DateTime start, DateTime end)
{
TimeSpan diff = end.Subtract(start);
return diff.Days;
}

}
};

일반 데이터베이스 프로그래밍 작업 및 문제 해결

이전 단원에서는 T-SQL, 확장 저장 프로시저(XP), 중간 계층의 코드와 비교하여 CLR 기반 프로그래밍의 장점에 대해 설명했습니다. 이 단원에서는 데이터베이스 응용 프로그램 개발자에게 문제가 될 수 있는 프로그래밍 작업과 패턴에 대해 살펴보고 CLR 통합을 사용하여(또는 CLR 통합을 사용하지 않고) 이러한 문제를 해결하는 방법을 설명합니다. 여기에서는 C#과 Visual Basic .NET로 작성된 몇 개의 코드 예제를 제공합니다.

.NET Framework 라이브러리를 사용한 데이터 유효성 검사

SQL Server 2005의 CLR 통합을 사용하면 사용자가 .NET Framework 클래스 라이브러리에서 제공하는 다양한 기능을 이용하여 데이터베이스 프로그래밍 문제를 해결할 수 있습니다.

이 예제에서는 정규식을 사용하여 T-SQL의 LIKE 연산자를 사용할 때 제공할 수 있는 것보다 더 완벽한 텍스트 패턴 일치를 제공합니다. System.Text.RegularExpressions 네임스페이스의 RegEx 클래스에 대한 간단한 래퍼인 다음 코드를 살펴보십시오.

Visual Basic .NET:

Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Text.RegularExpressions

Partial Public Class Validation

<SqlFunction(IsDeterministic:=True, IsPrecise:=True)> _
Public Shared Function RegExMatch(ByVal pattern As String, _
ByVal matchString As String) As Boolean

Dim r1 As Regex = New Regex(pattern.TrimEnd(Nothing))
Return r1.Match(matchString.TrimEnd(Nothing)).Success
End Function

<SqlFunction(IsDeterministic:=True, IsPrecise:=True)> _
Public Shared Function ExtractAreaCode(ByVal matchString As String)_
As SqlString

Dim r1 As Regex = New Regex("\((?<ac>[1-9][0-9][0-9])\)")
Dim m As Match = r1.Match(matchString)

If m.Success Then
Return m.Value.Substring(1, 3)
Else
Return SqlString.Null
End If

End Function
End Class

C#:

using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;

public partial class Validation
{
[SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static bool RegExMatch(string pattern, string matchString)
{
Regex r1 = new Regex(pattern.TrimEnd(null));
return r1.Match(matchString.TrimEnd(null)).Success;
}

[SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static SqlString ExtractAreaCode(string matchString)
{

Regex r1 = new Regex("\\((?<ac>[1-9][0-9][0-9])\\)");
Match m = r1.Match(matchString);
if (m.Success)
return m.Value.Substring(1, 3);
else return SqlString.Null;
}
}

RegExMatch() 및 ExtractAreaCode() 메서드가 RETURNS NULL ON NULL INPUT 옵션과 함께 데이터베이스 사용자 정의 함수로서 등록되었으며 따라서 함수가 어떤 입력이 널일 경우 널을 반환한다고 가정합시다. 그러면 함수 내부에서 특별히 널 처리 코드를 작성하지 않아도 됩니다.

위 코드를 사용하여 전자 메일 주소와 전화 번호의 유효성을 검사하는 제약 조건을 테이블의 열에 다음과 같이 정의할 수 있습니다.

CREATE TABLE contacts
(
firstName nvarchar(30),
lastName nvarchar(30),
emailAddress nvarchar(30) CHECK
(dbo.RegExMatch('[a-zA-Z0-9_\-]+@([a-zA-Z0-9_\-]+\.)+(com|org|edu)',
emailAddress) = 1),
usPhoneNo nvarchar(30) CHECK
(dbo.RegExMatch(
'\([1-9][0-9][0-9]\) [0-9][0-9][0-9]\-[0-9][0-9][0-9][0-9]',
usPhoneNo)=1),
areaCode AS dbo.ExtractAreaCode(UsPhoneNo) PERSISTED
)

areaCode 열은 ExtractAreaCode() 함수를 사용하여 usPhoneNo 열에서 지역 코드를 추출하는 지속적인 계산 열입니다. areaCode 열을 인덱싱함으로써 테이블에 대해 지역 번호를 기준으로 연락처를 검색하는 쿼리를 촉진할 수 있습니다.

일반적으로 설명하면 이 예제는 .NET Framework 라이브러리를 활용하여 T-SQL에서 노출하기 어려운 유용한 함수를 사용하여 T-SQL 기본 제공 함수 라이브러리를 확대할 수 있는 방법을 보여줍니다.

결과 집합 생성

서버 내에서 실행되는 데이터베이스 개체(예: 저장 프로시저 또는 뷰)에서 결과 집합의 생성은 가장 일반적인 데이터베이스 프로그래밍 작업 중 하나입니다. 단일 쿼리를 사용하여 결과 집합을 생성할 수 있을 경우 뷰 또는 인라인 테이블 반환 함수를 사용하여 결과 집합을 생성할 수 있습니다. 그러나 결과 집합을 생성할 때 여러 문과 절차 로직이 필요할 경우 두 가지 옵션, 즉 저장 프로시저와 테이블 반환 함수를 사용할 수 있습니다. SQL Server 2000에서는 테이블 반환 함수를 지원하나 이 함수를 T-SQL로만 작성할 수 있습니다. SQL Server 2005의 CLR 통합을 사용할 경우 이러한 함수를 관리되는 언어로도 작성할 수 있습니다. 이 단원에서는 CLR을 사용하여 저장 프로시저와 테이블 반환 함수를 작성하는 방법에 대해 알아보겠습니다.

T-SQL에서 테이블 반환 함수의 반환 값으로서 관계형 결과를 반환하거나 또는 저장 프로시저 내에 항상 존재하는 암시적 "호출자의 파이프"를 통하여 즉 실행된 SELECT 문이 결과를 호출자에게 반환하는 저장 프로시저 내의 어느 위치에서나(실행 수준의 중첩과 관계 없이) 관계형 결과를 반환할 수 있습니다. 더 정확하게 설명하면 변수 할당을 수행하지 않는 SELECT 문에서 이와 같이 작동합니다. FETCH, READTEXT, PRINT, RAISERROR 문 또한 호출자에게 결과를 암시적으로 반환합니다.

"호출자"가 정확히 정의되지 않은 점을 주의하여 보십시오. 그러므로 호출자는 저장 프로시저의 호출 컨텍스트에 따라 달라집니다.

임의의 클라이언트 데이터 액세스 API(예: ODBC, OLEDB, SQLClient)에서 저장 프로시저가 호출된 경우 호출자는 실제 API이자 결과를 표시하기 위해 호출자가 제공하는 모든 추상화(예: hstmt, IRowset, SqlDataReader)입니다. 따라서 일반적으로 다음 예제에서와 같이 저장 프로시저에서 생성된 결과는 스택의 모든 T-SQL 프레임을 우회하여 호출 API로 반환됩니다.

CREATE PROC proc1 AS
SELECT col1 FROM dbo.table1;
CREATE PROC proc2 AS
EXEC proc1;

프로시저 proc2의 실행 시 proc1에서 생성된 결과가 호출자 proc2로 반환됩니다. proc2가 생성된 결과를 캡처할 수 있는 방법은 하나뿐입니다. 즉, 영구 또는 임시 테이블이나 테이블 변수에 INSERT 또는 EXEC를 사용하여 결과를 디스크에 스트리밍하여 그렇게 할 수 있습니다.

CREATE PROC proc2 AS
DECLARE @t TABLE(col1 INT);
INSERT @t (col1) EXEC proc1;
-- do something with results

INSERT 또는 EXEC의 경우 호출자는 INSERT 문의 대상 테이블 또는 뷰입니다.

SQL Server 2005 CLR 저장 프로시저는 새로운 유형의 호출자를 도입했습니다. 관리되는 프레임 내에서 ADO.NET 공급자를 사용하여 쿼리를 실행할 때 결과를 SqlDataReader 개체를 통해 사용할 수 있게 되었으며 저장 프로시저 내에서 소비할 수 있습니다.

Visual Basic .NET:

...
Using conn As New SqlConnection("context connection = true")
conn.Open()
Dim cmd As SqlCommand = new SqlCommand( _
"SELECT col1 FROM dbo.table1", conn)
Dim reader As SqlDataReader = cmd.ExecuteReader()

Do While reader.Read()
' Do something with current row
Loop
End Using
...

C#:

...
using (SqlConnection conn= new SqlConnection("contect connection = true"))
{
...
SqlCommand cmd = new SqlCommand(
"SELECT col1 FROM dbo.table1", conn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
// do something with current row
}
...
}
...

나머지 문제는 관리되는 루틴이 결과를 호출자에게 결과를 반환하는 방법입니다. 이 작업은 CLR 저장 프로시저와 테이블 반환 함수에서 서로 다른 방식으로 수행됩니다. 저장 프로시저는 SqlContext 클래스의 SqlPipe 정적 인스턴스를 사용하여 데이터를 반환하는 반면에 테이블 반환 함수는 SQL Server가 결과를 가져올 수 있는 인터페이스를 구현합니다. 이러한 두 방법은 나중에 다시 설명합니다.

CLR 저장 프로시저와 SqlPipe

SqlPipe 클래스에서 사용할 수 있는 메서드 중에서 가장 간단한 메서드는 ExecuteAndSend()로 명령 개체를 인수로 사용합니다. 이 메서드는 명령을 실행하나 실행 결과를 관리되는 프레임에서 사용할 수 없고 그 대신, 결과가 저장 프로시저의 호출자에게 전송됩니다. 이것은 의미론적으로 T-SQL 저장 프로시저 내부에 문을 삽입하는 것에 해당하며 성능 면에서 해당 T-SQL 사용 방법과 동등합니다.

T-SQL에서 SELECT를 실행하는 간단한 저장 프로시저:

CREATE PROC proc1 AS
SELECT col1 FROM dbo.table1;

C#에서 그에 해당하는 메서드:

...
[Microsoft.SqlServer.Server.SqlProcedure]
public static void proc1()
{
using (SqlConnection conn =
new SqlConnection("context connection = true"))
{
conn.Open();
SqlCommand cmd = new SqlCommand(
"SELECT col1 FROM dbo.table1", conn);
SqlContext.Pipe.ExecuteAndSend(cmd);
conn.Close();
}
}
...

Visual Basic .NET:

...
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub VBproc1()
Using conn As New SqlConnection("context connection=true")
conn.Open()
Dim cmd As SqlCommand = new SqlCommand( _
"SELECT col1 FROM dbo.table1", conn)
SqlContext.Pipe.ExecuteAndSend(cmd)
conn.Close()
End Using
End Sub
...

SqlPipe.ExecuteAndSend()는 실행 중인 쿼리가 반환되는 데이터를 직접 생성하는 시나리오에서 잘 작동합니다. 그러나 데이터를 전송하기 전에 조작하거나 로컬 SQL Server 인스턴스 외부의 소스에서 획득한 데이터를 전송하는 것이 바람직한 경우가 있을 수 있습니다.

SqlPipe은 함께 작동하여 응용 프로그램이 임의의 결과를 호출자에게 반환하게 하는 메서드 그룹을 제공합니다. 그 메서드에는 SendResultsStart()와 SendResultsRow(), SendResultsEnd()가 있습니다. 이러한 API의 대부분은 확장 저장 프로시저에 사용할 수 있는 srv_describe 및 srv_sendrow API와 유사합니다.

SendResultsStart()는 SqlDataRecord를 인수로 사용하며 새 결과 집합의 시작을 나타냅니다. 이 API는 레코드 개체에서 메타 데이터 정보를 읽어 호출자에게 전송합니다. 그런 다음 전송될 각 행에 대해 한 번씩 SendResultsRow()를 호출하여 행을 반환할 수 있습니다. 모든 행이 전송된 후 SendResultsEnd()를 호출하여 결과 집합의 끝임을 나타내야 합니다.

예제: CLR 저장 프로시저에서 RSS Feed 반환

다음 C# 코드 조각은 웹에서 XML 문서(MSDN의 RSS(Really Simple Syndication) feed)를 읽고 System.Xml 클래스를 사용하여 구문 분석한 다음 정보를 관계형 형식으로 정보를 반환하는 저장 프로시저의 일부입니다. 인터넷에 액세스하는 데 필요한 코드 액세스 보안(CAS) 권한을 이러한 권한 집합에서만 사용할 수 있기 때문에 EXTERNAL_ACCESS 또는 UNSAFE 어셈블리에 코드를 배포해야 한다는 점에 유념하십시오.

...
using (SqlConnection conn =
new SqlConnection("context connection = true"))
{
// Retrieve the RSS feed
XPathDocument doc = new
PathDocument("http://msdn.microsoft.com/sql/rss.xml");
XPathNavigator nav = doc.CreateNavigator();
XPathNodeIterator i = nav.Select("//item");

// create metadata for four columns
// three of them are string types and one of the is a datetime
SqlMetaData[] rss_results = new SqlMetaData[4];
rss_results[0] = new SqlMetaData("Title", SqlDbType.NVarChar, 250);
rss_results[1] = new SqlMetaData("Publication Date",
SqlDbType.DateTime);
rss_results[2] = new SqlMetaData("Description",
SqlDbType.NVarChar, 2000);
rss_results[3] = new SqlMetaData("Link", SqlDbType.NVarChar, 1000);

// construct the record which holds metadata and data buffers
SqlDataRecord record = new SqlDataRecord(rss_results);

// cache a SqlPipe instance to avoid repeated calls to
// SqlContext.GetPipe()
SqlPipe sqlpipe = SqlContext.Pipe;

// send the metadata, do not send the values in the data record
sqlpipe.SendResultsStart(record);

// for each xml node returned, extract four pieces
// of information and send back each item as a row
while (i.MoveNext())
{

record.SetString(0, (string)

i.Current.Evaluate("string(title[1]/text())"));
record.SetDateTime(1, DateTime.Parse((string)

i.Current.Evaluate("string(pubDate[1]/text())")));
record.SetString(2, (string)

i.Current.Evaluate("string(description[1]/text())"));
record.SetString(3, (string)

i.Current.Evaluate("string(link[1]/text())"));
sqlpipe.SendResultsRow(record);
}

// signal end of results
sqlpipe.SendResultsEnd();
}
...

SendResultsStart() 호출과 SendResultsEnd() 호출 사이에 SqlPipe는 사용 중 상태로 설정되고 SendResultsRow() 이외의 Send 메서드를 호출하면 오류가 발생합니다. SendingResults 속성은 SqlPipe이 이러한 사용 중 상태인 동안 True로 설정됩니다.

테이블 반환 함수

CLR 통합은 또한 관리되는 언어로 작성된 테이블 반환 함수(TVF)를 지원합니다. 해당 T-SQL 함수와 마찬가지로 CLR TVF는 기본적으로 테이블 형식 결과를 반환하는 데 사용됩니다. 가장 두드러진 차이점은 T-SQL 테이블 반환 함수가 임시로 작업 테이블에 결과를 저장하는 데 반해 CLR TVF는 생성된 결과를 스트리밍할 수 있어 함수에서 반환하기 전에 결과를 완전히 구체화할 필요가 없다는 점입니다.

T-SQL에도 결과를 일시적으로 저장하지 않은 인라인 TVF라는 개념이 있으나 인라인 T-SQL TVF는 대부분이 매개 변수를 이용하여 하위 쿼리를 지정하기 위한 구문적 변경(Syntactic sugar)이라는 점을 알아 두십시오.

관리되는 TVF는 표준 IEnumerable 인터페이스를 반환합니다. 이 인터페이스가 테이블의 행을 나타내는 개체의 IEnumerator를 제공하며 열거의 MoveNext() 메서드가 False를 반환할 때까지 쿼리 프로세서가 하나씩 이 인터페이스를 검색합니다.

이러한 개체는 SQL Server에 대해 불투명하며 다른 함수에 의해 분해되어야 합니다. 행 채우기 메서드와 테이블 스키마는 TVF에 대한 주석에서 정의됩니다. 이 메서드는 입력으로서 개체를 사용하고 참조 매개 변수를 사용하여 행 필드를 반환하여 이 메서드의 서명이 고정되지 않았음을 암시합니다. 이 서명은 스키마와 일치합니다.

예제: RSS Feed를 가져오는 테이블 반환 함수

여기서는 C#의 테이블 반환 함수로서 RSS 가져오기 리캐스트를 제시합니다. RSS_TVF() 메서드의 SqlFunction 주석과 FillTVFRow()메서드의 서명 간의 일치에 대해 주목하십시오.

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Xml.XPath;
using System.Collections;

public partial class UserDefinedFunctions
{
[SqlFunction(FillRowMethodName = "FillTVFRow",
TableDefinition = "Title nvarchar(250), " +
"PublicationDate datetime, " +
"Description nvarchar(2000), " +
"Link nvarchar(1000)")
]
public static IEnumerable RSS_TVF()
{
return new RssReader();
}

public static void FillTVFRow(object row, out SqlString str,
out SqlDateTime date, out SqlString desc, out SqlString link)
{
// split each object array
object[] rowarr = (object[])row;
str = (SqlString)(rowarr[0]);
date = (SqlDateTime)(rowarr[1]);
desc = (SqlString)(rowarr[2]);
link = (SqlString)(rowarr[3]);
}
}

public class RssReader : IEnumerable
{
XPathDocument doc;
XPathNavigator nav;

// Construct helper class, initializing metadata for the results
// reading from the RSS feed, creating the iterator
public RssReader(string site)
{
// Retrieve the RSS feed
//doc = new XPathDocument("http://msdn.microsoft.com/sql/rss.xml");
doc = new XPathDocument(site);
nav = doc.CreateNavigator();
}

public IEnumerator GetEnumerator()
{
return new RSSEnumerator(this);
}

private class RSSEnumerator : IEnumerator
{
XPathNodeIterator i;
Object[] current;
RssReader reader;

public RSSEnumerator(RssReader reader)
{
this.reader = reader;
Reset();
}

public void Reset()
{
i = reader.nav.Select("//item");
}

public bool MoveNext()
{
if (i.MoveNext())
{
current = new Object[4];
current[0] = new SqlString((string)
i.Current.Evaluate("string(title[1]/text())"));
current[1] = new SqlDateTime(DateTime.Parse((string)
i.Current.Evaluate("string(pubDate[1]/text())")));
current[2] = new SqlString((string)
i.Current.Evaluate("string(description[1]/text())"));
current[3] = new SqlString((string)
i.Current.Evaluate("string(link[1]/text())"));
return true;
}
else return false;

}

public Object Current
{
get
{
return current;
}
}
}
}

이 테이블 반환 함수의 결과를 소비하는 간단한 쿼리는 다음과 같습니다.

SELECT *
FROM RSS_TVF()

물론 이 데이터의 TVF 형식에 비해 다양한 쿼리를 표현할 수 있습니다. URL의 정식 버전을 반환하는 CanonicalURL() 함수가 있을 경우 정식 URL을 사용하여 RSS feed에서 데이터를 쉽게 반환할 수 있습니다.

select title, publicationDate, description, dbo.CanonicalURL(link)
from dbo.RSS_TVF()
order by publicationDate

이 예제에서 전체 RSS Feed를 사용하고 상단에 탐색기를 만든 다음 MoveNext()를 호출할 때 개별 항목에 대해 반복하기 때문에 TVF의 스트리밍 기능을 사용하지 않습니다. 그러나 스트리밍 API를 사용하여 웹 소스의 결과를 소비하고 XmlReader를 사용하여 생성된 XML에 대해 반복할 수 있습니다. CLR 테이블 반환 함수와 T-SQL 테이블 반환 함수 간의 실행 모델 차이를 고려하면 결과를 스트리밍할 수 있는 시나리오의 경우에 특히 CLR TVF의 성능이 훨씬 우수하다는 사실을 인식하는 것이 중요합니다.

예제: 스칼라를 행으로 분해

응용 프로그램에서 다중 값 인수를 전달해야 할 경우가 종종 있습니다. 예를 들어 주문 처리 시스템에서 주문을 주문 테이블에 삽입하는 저장 프로시저가 필요할 수 있습니다. 저장 프로시저의 필요한 인수가 주문의 일련 품목(line-item)일 수 있습니다. 그러나 여기에서는 T-SQL이 테이블 반환 인수를 지원하지 않고 컬렉션과 배열이 없다는 제한에 부딪힙니다.

이것을 해결하는 한 가지 방법은 컬렉션을 스칼라 값으로(예: nvarchar 또는 xml) 인코딩하여 저장 프로시저에 인수로서 전달하는 것입니다. 저장 프로시저는 스칼라 입력을 사용하여 행 집합으로 변환하는 테이블 반환 함수를 사용할 수 있으며 그 행 집합은 나중에 일련 품목(line-item) 테이블에 삽입하거나 조작할 수 있습니다.

테이블 반환 함수는 T-SQL로 작성될 수 있으나 CLR로 작성할 경우 그 성능이 더 우수합니다. 또한 CLR 테이블 반환 함수는 System.Text 네임스페이스에서 문자열 조작 함수를 사용할 수 있기 때문에 구현하기가 매우 간단합니다.

다음은 세미 콜론으로 구분된 입력 문자열을 사용하고 행 집합의 형식으로 데이터를 반환하는 테이블 반환 함수의 구현을 나타냅니다.

Visual Basic .NET:

Imports System.Collections
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Partial Public Class UserDefinedFunctions

' This needs to return an IEnumerable, but since an array
' does, in this case we do not need to define a new class
' that implements it: we can simply return the array.
<SqlFunction(FillRowMethodName:="FillRow", _
TableDefinition:="value nvarchar(60)")> _
Public Shared Function GetStrings(ByVal str As SqlString) _
As IEnumerable

Return str.Value.Split(";"c)
End Function

' This method does the decoding of the row object. Since the
' row is already a string, this method is trivial. Note that
' this method is pointed to by the annotation on the
' GetString method.
Public Shared Sub FillRow(ByVal row As Object, _
ByRef str As String)

str = CType(row, String)
End Sub

End Class

C#:

using System.Collections;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
/*
* This needs to return an IEnumerable, but since an array
* does, in this case we do not need to define a new class
* that implements it: we can simply return the array.
*/
[SqlFunction(FillRowMethodName="FillRow",
TableDefinition="value nvarchar(60)")]
public static IEnumerable GetStrings(SqlString str)
{
return str.Value.Split(';');
}

/*
* This method does the decoding of the row object. Since the
* row is already a string, this method is trivial. Note that
* this method is pointed to by the annotation on the
* GetString method.
*/
public static void FillRow(object row, out string str)
{
str = (string)row;
}
}

GetStrings() 메서드가 동일한 이름의 TVF로 등록되었다고 가정할 경우 다음은 이 TVF를 사용하여 테이블 형식으로 주문에서 일련 품목(line-item)을 추출하는 저장 프로시저의 T-SQL 조각입니다.

CREATE PROCEDURE Insert_Order @cust_id int, @lineitems nvarchar(8000)
AS
BEGIN
...
INSERT LineItems
SELECT * FROM dbo.GetStrings(@lineitems)
...
END

어느 것을 사용해야 하나?

SqlPipe와 함께 저장 프로시저를 사용할 것인지(T-SQL에서 암시적으로 또는 CLR 루틴에서 명시적으로) 아니면 테이블 반환 함수를 사용할 것인지에 관한 결정은 조합성 요구 사항, 데이터 소스, 부수적 효과의 필요성, 결과의 형식화 요구 사항 등 고려해야 하는 여러 요소에 달려있습니다. 차례로 이러한 요소들을 하나씩 살펴보겠습니다.

조합성 요구 사항

TVF 또는 저장 프로시저에서 생성된 결과를 다시 사용하거나 조작하는 것이 바람직합니다. TVF의 반환 형식이 그러한 구문이 허용되는 어떠한 곳에서도 사용될 수 있는 관계형 행 집합이기 때문에 테이블 반환 함수가 조합성 관점에서 볼 때 더 다양한 용도로 사용될 수 있습니다. 특히 TVF는 SELECT 문의 FROM 절에서 사용될 수 있고 따라서 생성된 결과가 하위 쿼리, INSERT...SELECT 문, 파생 테이블, 테이블 표현식 등에서 SELECT의 조합성의 이점을 누릴 수 있습니다.

한편 T-SQL 언어에서 생성된 결과를 영구 테이블 또는 임시 테이블에 저장할 수 있는 INSERT...EXEC 조합의 일부로서만 저장 프로시저를 조합할 수 있습니다. INSERT 연산은 실제 데이터 복사를 나타내며 이 데이터 복사는 다분히 성능에 영향을 미칠 것입니다.

서버에서 결과의 재사용과 조합성이 요구될 경우 TVF를 사용하는 것이 더 좋습니다. 생성된 결과가 단지 클라이언트 계층 또는 중간 계층으로 스트리밍되어야 할 경우에는 어느 방법이나 적절합니다.

데이터의 소스

반환되고 있는 데이터의 소스가 T-SQL 기반 구현과 CLR 기반 구현 중에서 하나를 결정할 때 고려해야 할 또 다른 중요한 요소입니다. ADO.NET 공급자를 사용하는 로컬 인스턴스의 소스 또는 SQL Server의 외부 소스를 읽어 결과를 생성할 수 있습니다. 외부 소스를 읽어올 경우 외부 데이터에 액세스하는 로직을 구현하기가 쉽기 때문에 CLR 기반 구현을 선택하는 것이 낫습니다.

ADO.NET 공급자를 사용하는 로컬 인스턴스에서 실행된 쿼리를 기반으로 결과를 생성하는 경우 저장 프로시저가 일반적으로 쿼리를 실행하고 결과를 반복하고 행에 대한 작업을 수행한 후 SqlPipe을 통하여 행을 반환합니다.

TVF를 사용할 경우 결과가 읽혀질 때 결과를 변환하는 IEnumerable의 사용자 지정 구현에서 SqlDataReader를 래핑할 것입니다. 그러나 SQL Server 2005는 테이블 반환 함수가 반환할 때까지 요청을 대기하도록 허용하지 않습니다. ADO.NET 공급자를 통하여 실행된 모든 쿼리는 완전히 실행되어야 하고 결과는 함수 본문이 반환되기 전에 완전히 소비되어야 합니다. ADO.NET 공급자로부터의 SqlDataReader 연산이 대기 중일 때 Return 문이 실행될 경우 오류가 발생합니다. 그러므로 데이터가 로컬 데이터베이스 인스턴스에서 반환되고 있는 대부분의 경우 CLR TVF를 통하여 데이터를 스트리밍할 수 없습니다. 조합성 등 다른 요소로 인해 TVF로서 쿼리를 작성해야 하는 경우 T-SQL로만 작성할 수 있습니다. 또는 SqlPipe를 사용하는 관리되는 저장 프로시저를 사용할 수 있습니다.

로컬 인스턴스의 데이터를 기반으로 저장 프로시저 내에서 결과가 생성될 경우 결과에 절차적 수정 또는 조작이 필요한 때에만 SendResults API를 사용하는 것이 적절합니다. 수정되지 않고 결과가 호출자에게 전송될 경우 그 성능이 더 우수하므로 SqlPipe.ExecuteAndSend()를 사용해야 합니다.

부수적 효과가 있는 연산

일반적으로 부수적 효과를 발생시키는 연산 즉 DML 문 또는 트랜잭션 작업 등 데이터베이스 상태를 변경하는 연산은 테이블 반환 함수를 포함하여 사용자 정의 함수에서 허용되지 않습니다. 그러나 이러한 연산이 필요할 수 있습니다. 예를 들어 SAVEPOINT 트랜잭션을 설정하고 일부 작업을 수행하고 오류가 발생할 경우 SAVEPOINT로 롤백해야 할 경우가 있습니다.

부수적 효과가 사용자 정의 함수에서 허용되지 않을 경우 그러한 시나리오는 저장 프로시저를 통하여 구현할 수 있고 결과가 SqlPipe를 통하여 반환되어야 합니다. 그러나 SqlPipe이 결과를 전송 중일 때 부수적 효과가 있는 연산은 ADO.NET 공급자를 통하여 실행될 수 없습니다. 이러한 연산은 결과 집합이 시작되기 전이나 완료된 후에만 허용됩니다.

결과 형식화 및 결과 개수

SqlPipe를 통하여 CLR 저장 프로시저에 의해 생성된 결과에 대한 설명은 CLR TVF에 대한 설명과 다르며 T-SQL의 설명과 일치합니다. TVF는 강력하게 형식화되었으며 등록(CREATE FUNCTION) 문의 일부로서 TVF는 반환 값의 형식을 정적으로 정의해야 합니다.

한편 저장 프로시저 선언에서는 생성된 결과 또는 결과를 생성하는지 여부에 대해서도 설명하지 않습니다. 이것은 편리해 보일 수 있습니다. 그리고 이로 인해 우수한 유연성을 얻을 수 있지만, 동적으로 결과의 형태를 다시 정의할 수 있는 저장 프로시저를 실행하는 응용 프로그램을 작성할 때에 매우 주의해야 합니다. 그러나 결과의 스키마가 전체 호출에서 가변적이어야 할 경우 SqlPipe만이 이러한 유연성을 제공하므로 저장 프로시저를 사용해야 합니다.

사실 저장 프로시저 내에서 SqlPipe를 통하여 생성된 결과의 약한 형식화는 단일 결과의 스키마 범위를 벗어나며 여러 개의 결과 집합을 반환할 가능성이 있습니다. 결과 집합의 형식과 수는 저장 프로시저에 의해 동적으로 결정될 수 있습니다.

요약

다음 표에서는 특정 응용 프로그램을 T-SQL 또는 CLR 중에서 어느 것으로 작성해야 할지에 대한 선택 지침과 저장 프로시저 또는 테이블 반환 함수 중 어느 것을 사용해야 할지에 대한 선택 지침을 요약합니다.

조건예아니오
조합성이 필요합니까?TVF 프로시저 또는 TVF
외부 데이터 소스(로컬 데이터만 액세스하는 경우와 비교)CLR TVF 또는 CLR 프로시저(로컬 데이터에만 액세스하는 경우) T-SQL TVF 또는 프로시저
부수적 효과가 필요합니까?프로시저프로시저 또는 TVF
고정 결과 스키마?프로시저 또는 TVF 프로시저
하나 이상의 결과 집합?프로시저프로시저 또는 TVF
결과를 스트리밍하는 기능?CLR TVFT-SQL TVF

이 단원의 대부분에서 SqlPipe를 통한 결과의 전송은 프로시저와 밀접하게 연관되어 있습니다. SqlPipe와 결과의 반환 가능성을 CLR 트리거의 본문에서 사용할 수 있더라도 이 방법은 대상 개체에 대해 정의된 트리거를 사용하여 데이터 조작 언어 또는 데이터 정의 언어 문을 실행할 경우 예상치 못한 결과가 생성될 수 있으므로 이 방법은 권장하지 않습니다.

데이터에 대한 사용자 지정 집계 수행

평균, 표준 편차 등 통계적 계산의 수행을 포함하여 데이터에 대해 집계를 수행해야 하는 여러 시나리오가 있습니다. 필요한 집계 함수가 기본으로 제공되지 않을 경우 SQL Server 2005에서 그 기능을 추가하는 여러 방법이 있습니다.

  • 사용자 정의 집계(UDA)로서 집계 작성.
  • CLR 저장 프로시저를 사용하여 집계 작성
  • T-SQL에서 서버 쪽 커서 사용

주어진 값의 곱을 계산하는 간단한 집계 함수에 대한 세 가지 대안을 살펴보겠습니다.

예제: 사용자 정의 집계 함수로서 구현된 곱셈

다음은 사용자 정의 집계로서 작성된 이 작업 코드입니다. 곱셈을 계산하는 로직은 Accumulate() 메서드에 있습니다. Merge() 메서드는 그러한 두 집계가 병합될 경우 그 결과를 정의합니다.

Visual Basic .NET:

Imports System
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

<Serializable()> _
<SqlUserDefinedAggregate(Format.Native)> _
Public Structure ProductAgg

Private product As SqlInt32

Public Sub Init()
product = 1
End Sub

Public Sub Accumulate(ByVal value As SqlInt32)
product = product * value
End Sub

Public Sub Merge(ByVal group As ProductAgg)
product = product * group.product
End Sub

Public Function Terminate() As SqlInt32
Return product
End Function

End Structure

C#:

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

[Serializable]
[SqlUserDefinedAggregate(Format.Native)]
public struct ProductAgg
{
private SqlInt32 product;

public void Init()
{
product = 1;
}

public void Accumulate(SqlInt32 value)
{
product = product * value;
}

public void Merge(ProductAgg group)
{
product = product * group.product;
}

public SqlInt32 Terminate()
{
return product;
}
}

이 형식을 작성하고 SQL Server에 등록한 후 이 형식을 T-SQL에서 기본 제공 집계로서 사용할 수 있습니다.

SELECT dbo.ProductAgg(intcol)
FROM tbl
GROUP BY col

예제: 관리되는 저장 프로시저로서 곱셈

데이터에 대해 계산을 반복 수행하는 저장 프로시저를 만들 수 있습니다. 이 반복은 아래와 같이 SqlDataReader 클래스를 사용하여 수행됩니다.

Visual Basic .NET:

Imports Microsoft.SqlServer.Server
Imports System.Data.SqlTypes
Imports System.Data.SqlClient

Partial Public Class StoredProcedures

<SqlProcedure()> _
Public Shared Sub VBProductProc(ByRef value As SqlInt32)

' The empty product is 1
value = 1

Using conn As New SqlConnection("context connection = true")
conn.Open()
Dim cmd As SqlCommand = New SqlCommand()
cmd.Connection = conn
cmd.CommandText = "SELECT intcolumn FROM tbl"
Dim r As SqlDataReader = cmd.ExecuteReader()
Using r
Do While r.Read()
value = value * r.GetSqlInt32(0)
Loop
End Using
conn.Close()
End Using
End Sub

End Class

C#:

using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.Data.SqlClient;
public partial class StoredProcedures
{
[SqlProcedure]
public static void ProductProc(out SqlInt32 value)
{
// Ensure that we write to value.
// Empty product is 1.
value = 1;

using (SqlConnection conn =
new SqlConnection("context connection = true"))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT intcolumn FROM tbl";
SqlDataReader r = cmd.ExecuteReader();
using (r)
{
while (r.Read()) //skip to the next row
{
value *= r.GetSqlInt32(0);
}
}
}
}
}

다음의 T-SQL EXEC 문을 사용하여 이 프로시저를 호출할 수 있습니다.

EXEC Product @p OUTPUT

예제: 커서를 사용하는 T-SQL 저장 프로시저로서 곱셈

마지막으로 쿼리를 실행하고 T-SQL 커서를 사용하여 데이터를 반복 계산을 수행하는 T-SQL 저장 프로시저를 만들 수 있습니다..

T-SQL:

create procedure TSQL_ProductProc (@product int output)
as
begin
declare @sales int
declare c insensitive cursor for select intcolumn from tbl

set @product = 1

open c
fetch next from c into @sales

while @@FETCH_STATUS = 0
begin
set @product = @product * @sales
fetch next from c into @sales
end

close c
deallocate c
end
go

요약

결과를 생성하기 위해 UDA를 사용할 것인지 아니면 다른 솔루션을 사용할 것인지에 관한 결정은 조합성 요구 사항, 집계 알고리즘의 세부 사항, 부수적 효과의 필요성 등 여러 요소에 달려있습니다.

UDA는 모든 T-SQL 쿼리에서 사용할 수 있는 독립 실행형 개체로 일반적으로 시스템 집계를 사용할 수 있는 동일한 곳에서 사용됩니다. UDA가 작동되는 쿼리에 대한 가정은 없습니다. 예를 들어 UDA는 뷰 정의(인덱스된 뷰가 아닌 경우에도) 스칼라 하위 쿼리에 포함될 수 있습니다.

UDA는 집계 함수로 값이 제공되는 순서에 대한 보장이 없기 때문에 쿼리의 ORDER BY 절 전에 계산될 수 있습니다. 그러므로 집계 알고리즘이 특정 순서로 값을 소비해야 할 경우 UDA를 사용할 수 없습니다. 그리고 UDA는 전체 그룹의 값을 소비하고 단일 값을 반환합니다. 이러한 조건이 문제와 맞지 않을 경우 다른 방법을 사용해야 합니다.

또한 UDA는 데이터 액세스를 수행할 수 없으며 부수적 효과도 없습니다. 이러한 사항이 필요할 경우 저장 프로시저를 사용해야 합니다.

UDA에는 여러 제한이 따르지만 UDA가 설명한 옵션 중 가장 성능이 우수하며 따라서 다른 요구 사항 때문에 사용할 수 없는 경우가 아니라면 일반적으로 UDA를 통해 집계를 수행해야 합니다.

사용자 정의 형식

이제 매우 강력하지만 종종 이해하기 어려운 SQL Server 2005 기능에 대해 설명할 차례입니다. 사용자 정의 형식(UDT)을 사용하여 데이터베이스의 스칼라형 시스템을 확장할 수 있습니다. 이것은 시스템 유형의 별칭을 단순히 정의하는 것 이상으로 이전 SQL Server 릴리스에서도 사용할 수 있었습니다. UDT의 정의는 관리되는 코드로 클래스를 작성하고 어셈블리를 만든 다음 CREATE TYPE 문을 사용하여 SQL Server에 유형을 SQL Server에 등록하는 것만큼 간단합니다. 다음은 UDT가 충족해야 하는 계약을 나타내는 코드 골격입니다.

Visual Basic .NET:

<Serializable()> _
<Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)> _
Public Structure SimpleType
Implements INullable

Public Overrides Function ToString() As String
...
End Function

Public ReadOnly Property IsNull() As Boolean Implements _
INullable.IsNull
...
End Property

Public Shared ReadOnly Property Null() As SimpleType
...
End Property

Public Shared Function Parse(ByVal s As SqlString) As SimpleType
...
End Function

End Structure

C#:

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)]
public struct SimpleUdt : INullable
{
public override string ToString() { ... }
public bool IsNull { ... }
public static SimpleUdt Null
{
get { ... }
}
public static SimpleUdt Parse(SqlString s) { ... }
}

이제 이것을 로드하고 T-SQL에서 사용할 수 있습니다.

CREATE TYPE simpleudt FROM [myassembly].[SimpleUdt]
CREATE TABLE t (mycolumn simpleudt)

UDT를 만드는 경우

SQL Server 2005의 UDT는 개체-관계형 확장성 메커니즘이 아닙니다. UDT는 데이터베이스의 스칼라형 시스템을 확장하는 방법입니다. 스칼라형 시스템에는 SQL Server에서 기본으로 제공하는 열 형식(예: int, nvarchar, uniqueidentifier 등)이 있습니다. UDT를 사용하여 기본 제공 스칼라형 대신에 사용할 수 있는 새로운 형식을 정의할 수 있습니다. 형식이 열로서 모델링하기에 적합한 원자 값일 경우 UDT를 만드십시오.

SQL Server 2005의 UDT는 개체-관계형 확장성 메커니즘이 아닙니다. UDT는 데이터베이스의 스칼라형 시스템을 확장하는 방법입니다. 스칼라형 시스템에는 SQL Server에서 기본으로 제공하는 열 형식(예: int, nvarchar, uniqueidentifier 등)이 있습니다. UDT를 사용하여 기본 제공 스칼라형 대신에 사용할 수 있는 새로운 형식을 정의할 수 있습니다. 형식이 열로서 모델링하기에 적합한 원자 값일 경우 UDT를 만드십시오.

UDT를 만들지 않는 경우

직원, 연락처, 고객 등 복잡한 비즈니스 개체를 모델링하는 데 UDT를 사용하지 마십시오. UDT는 SQL Server에서 단위로 처리되며 불투명합니다. 복잡한 UDT의 문제로는 형식에 대한 8KB 크기 제한, 인덱싱 제한, UDT의 어떤 값이 업데이트될 때 전체 값이 업데이트되어야 하는 문제가 있습니다.

점 또는 색상 클래스 등 유형이 상대적으로 단순한 경우에도 UDT보다 중간 계층 개체 관계형 매핑 기술을 사용하는 것이 더 좋습니다. UDT는 데이터가 실제로 원자성일 경우에 저장되어야 합니다.

UDT 디자인 시 고려해야 할 요소

UDT는 열 형식이므로 고유성 등 참조 무결성 제약 조건을 정의할 수 있는 것처럼 인덱스를 전체 UDT 값에 대해 정의할 수 있습니다. 또한 UDT는 비교 시나리오와 순서 지정 시나리오에서도 사용할 수 있습니다.

UDT 값 비교는 형식의 기본 이진 표현을 비교함으로써 수행됩니다. Format.Native가 지속 메커니즘으로 사용되고 유형에 정의된 필드 순서와 동일한 순서로 지속 형식이 만들어지는 경우 정확한 순서로 배치되도록 주의해야 합니다.

비교를 제외하고 UDT에 대한 모든 작업은 UDT 값이 직렬화가 해제되고 메서드가 호출되어야 합니다. 이 패턴에는 그와 관련한 비용이 있으며 유형을 UDT로 모델링할 것인지 여부를 평가할 때 고려되어야 합니다. UDT는 모델링해야 하는 유형에 복잡한 동작이 있을 경우 가장 적합합니다. 유형이 상대적으로 단순할 경우 UDT 구문을 피하는 것이 좋습니다.

마지막으로 관련 함수 라이브러리를 저장하는 편리한 패키지 메커니즘으로서 UDT의 정적 메서드를 사용할 수 있습니다. 정적 메서드는 다음 구문을 사용하여 T-SQL에서 호출할 수 있습니다.

select Type::Function(@arg1)

예제: 비 서구형 날짜

Um Al Qura(코란) 달력을 사용하여 날짜와 시간 값을 저장하려고 합니다. 이 달력은 SQL Server datetime 데이터 형식에 사용되는 그레고리력과 다릅니다. 문자열 변환, 날짜 부분을 검색하고 날짜 계산을 수행하는 기능 등의 동일한 기본 동작 집합을 이 데이터 형식에 지정하려고 합니다.

다음 사용자 정의 형식 예제는 이와 같은 데이터 형식의 단순한 구현입니다. 이 예제에서는 .NET Framework, 2.0 버전의 새로운 형식인 UmAlQuraCalendar 형식을 사용합니다. 필요한 모든 메서드를 제공하기 위해 이 예제를 확대하는 일은 수월할 것입니다.

C#로 작성된Um Al Qura UDT:

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Globalization;

[Serializable]
[SqlUserDefinedType(Format.Native, IsByteOrdered = true)]
public struct UmAlQuraDateTime : INullable
{

/*
* Private state.
*/

private long dtTicks;
private bool isNull;

// Calendar object used for all calendar-specific operations
private static readonly UmAlQuraCalendar s_calendar =
new UmAlQuraCalendar();

// For correct formatting we need to provie a culture code for
// a country that uses the Um Al Qura calendar: Saudi Arabia.
private static readonly CultureInfo ci =
new CultureInfo("ar-SA", false);


/*
* Null-Handling
*/

// get a null instance
public static UmAlQuraDateTime Null
{
get
{
UmAlQuraDateTime dt = new UmAlQuraDateTime();
dt.isNull = true;
return dt;
}
}

public bool IsNull
{
get
{
return this.isNull;
}
}

/*
* Constructors
*/

public UmAlQuraDateTime(long ticks)
{
isNull = false;
dtTicks = ticks;
}

public UmAlQuraDateTime(DateTime time) : this(time.Ticks)
{
}

/*
* Factory routines.
*/

public static UmAlQuraDateTime Parse(SqlString s)
{
if (s.IsNull) return Null;
DateTime t = DateTime.Parse(s.Value);
return new UmAlQuraDateTime(t);
}

public static UmAlQuraDateTime ParseArabic(SqlString s)
{
if (s.IsNull) return Null;
DateTime t = DateTime.Parse(s.Value, ci);
return new UmAlQuraDateTime(t);
}

public static UmAlQuraDateTime FromSqlDateTime(SqlDateTime d)
{
if (d.IsNull) return Null;
return new UmAlQuraDateTime(d.Value);
}

public static UmAlQuraDateTime Now
{
get
{
return new UmAlQuraDateTime(DateTime.Now);
}
}

/*
* Conversion Routines
*/

public DateTime DateTime
{
get { return new DateTime(this.dtTicks); }
}

public SqlDateTime ToSqlDateTime()
{
return new SqlDateTime(this.DateTime);
}

public override String ToString()
{
return this.DateTime.ToString(ci);
}

public String ToStringUsingFormat(String format)
{
return this.DateTime.ToString(format, ci);
}

/*
* Methods for getting date parts.
*/

public int Year
{
get
{
return s_calendar.GetYear(this.DateTime);
}
}

public int Month
{
get
{
return s_calendar.GetMonth(this.DateTime);
}
}

public int Day
{
get
{
return s_calendar.GetDayOfMonth(this.DateTime);
}
}

/*
* Date arithmetic methods.
*/

public UmAlQuraDateTime AddYears(int years)
{
return new
UmAlQuraDateTime(s_calendar.AddYears(this.DateTime, years));
}

public UmAlQuraDateTime AddDays(int days)
{
return new
UmAlQuraDateTime(s_calendar.AddDays(this.DateTime, days));
}

public double DiffDays(UmAlQuraDateTime other)
{
TimeSpan diff = DateTime.Subtract(other.DateTime);
return diff.Days;
}
}

이 형식을 SQL Server에 로드하면 T-SQL를 통하여 이 형식을 사용할 수 있습니다. 다음은 이 UDT를 사용한 T-SQL 예제와 생성된 결과입니다.

먼저 Um Al Qura 날짜를 구문 분석하고 서양 형식과 함께 두 형식으로 날짜를 인쇄합니다.

DECLARE @d UmAlQuraDateTime
SET @d = UmAlQuraDateTime::ParseArabic('01/02/1400')
PRINT @d.ToString()
PRINT @d.ToStringUsingFormat('F')
PRINT @d.ToSqlDateTime()

사용 결과:

또한 서양 형식 날짜를 Um Al Qura로 변환할 수 있습니다.

DECLARE @n DateTime
SET @n = 'March 20, 2005'
DECLARE @d UmAlQuraDateTime
SET @d = UmAlQuraDateTime::FromSqlDateTime(@n)
PRINT @n
PRINT @d.ToString()

결과

마지막으로 이 형식의 열을 사용하여 테이블을 만들고 수정할 수 있습니다.

CREATE TABLE dates (
western DateTime,
umalqura UmAlQuraDateTime
)

INSERT INTO dates(western) VALUES ('June 1, 2005')
INSERT INTO dates(western) VALUES ('July 1, 2005')

UPDATE dates
SET umalqura = UmAlQuraDateTime::FromSqlDateTime(dates.western)

SELECT western, umalqura.ToString() as umalqura FROM dates

결과 테이블:

결론

이 문서에서는 SQL Server 2005의 CLR 통합 기능 사용에 관한 지침, 구체적인 사용 시나리오, 예제를 설명하였습니다. 데이터베이스 응용 프로그램 개발자와 설계자는 이 문서를 Transact-SQL, XML, Service Broker 등 다른 SQL Server 2005 기능에 대한 설명서(http://www.microsoft.com/sql/2005/techinfo/default.asp (영문))와 함께 사용해야 합니다.

조만간 CLR 통합의 관리 효율성, 모니터링, 문제 해결을 다루는 지침과 함께 이 분야에 대한 더 많은 문서를 게재할 예정입니다.

'DataBase' 카테고리의 다른 글

SQL 에서 대소문자 구분법..  (0) 2006.06.19
SQL 서버 사용관련 문서  (0) 2006.04.25
SQL 성능을 높이는 5가지 방법  (0) 2006.03.27
SQL 서버 2005 보안 가이드 1  (0) 2006.03.27
응용 프로그램에 Reporting Services 통합  (0) 2006.01.29
Posted by 퓨전마법사
,

티스토리툴바