SQL Server의 사용자 정의 형식 등록

  • 아티클

Microsoft SQL Server에서 UDT(사용자 정의 형식)를 사용하려면 먼저 등록해야 합니다. UDT를 등록하려면 해당 형식을 사용할 데이터베이스에 어셈블리를 등록하고 형식을 만듭니다. UDT는 범위가 단일 데이터베이스로 한정되며, 데이터베이스마다 동일한 어셈블리와 UDT를 등록하지 않는 한 여러 데이터베이스에 사용할 수 없습니다. UDT 어셈블리가 등록되고 형식이 만들어지면 Transact-SQL과 클라이언트 코드에 UDT를 사용할 수 있습니다. 자세한 내용은 CLR 사용자 정의 형식을 참조하십시오.

Visual Studio를 사용하여 UDT 배포

Microsoft Visual Studio를 사용하면 UDT를 간편하게 배포할 수 있습니다. 그러나 배포 시나리오가 복잡하거나 높은 유연성이 요구되는 경우에는 이 항목에서 설명하는 대로 Transact-SQL을 사용해야 합니다.

다음 단계에 따라 Visual Studio를 사용하여 UDT를 만들고 배포하십시오.

  1. Visual Basic 또는 Visual C# 언어 노드에 새 데이터베이스 프로젝트를 만듭니다.

  2. UDT를 포함할 SQL Server 데이터베이스에 대한 참조를 추가합니다.

  3. 사용자 정의 형식 클래스를 추가합니다.

  4. UDT를 구현하는 코드를 작성합니다.

  5. 빌드 메뉴에서 배포를 선택합니다. 그러면 SQL Server 데이터베이스에 어셈블리가 등록되고 형식이 만들어집니다.

Transact-SQL을 사용하여 UDT 배포

UDT를 사용할 데이터베이스에 어셈블리를 등록할 때는 Transact-SQL CREATE ASSEMBLY 구문을 사용합니다. 등록된 어셈블리는 파일 시스템에 외부적으로 저장되는 것이 아니라 데이터베이스 시스템 테이블에 내부적으로 저장됩니다. UDT가 외부 어셈블리에 종속되어 있는 경우 해당 어셈블리도 데이터베이스에 로드해야 합니다. UDT를 사용할 데이터베이스에 UDT를 만들 때는 CREATE TYPE 문을 사용합니다. 자세한 내용은 CREATE ASSEMBLY(Transact-SQL) 및 CREATE TYPE(Transact-SQL)을 참조하십시오.

CREATE ASSEMBLY 사용

CREATE ASSEMBLY 구문은 UDT를 사용할 데이터베이스에 어셈블리를 등록하는 데 사용됩니다. 등록된 어셈블리에는 종속성이 없습니다.

특정 데이터베이스에 같은 어셈블리를 여러 버전으로 만들 수 없습니다. 그러나 특정 데이터베이스의 culture에 따라 같은 어셈블리를 여러 버전으로 만들 수는 있습니다. SQL Server에서는 어셈블리의 여러 culture 버전을 SQL Server 인스턴스에 서로 다른 이름으로 등록하여 구분합니다. 자세한 내용은 .NET Framework SDK에서 "강력한 이름의 어셈블리 생성 및 사용"을 참조하십시오.

SAFE 또는 EXTERNAL_ACCESS 권한 집합을 사용하여 CREATE ASSEMBLY를 실행하면 확인할 수 있으며 형식이 안전한지 여부에 대해 어셈블리 검사가 수행됩니다. 권한 집합을 지정하지 않으면 SAFE가 사용됩니다. UNSAFE 권한 집합을 사용한 코드는 검사되지 않습니다. 어셈블리 권한 집합에 대한 자세한 내용은 어셈블리 디자인을 참조하십시오.

예

다음 Transact-SQL 문은 SAFE 권한 집합을 사용하여 AdventureWorks 데이터베이스의 SQL Server에 Point 어셈블리를 등록합니다. WITH PERMISSION_SET 절을 생략하면 어셈블리가 SAFE 권한 집합을 사용하여 등록됩니다.

USE AdventureWorks;
CREATE ASSEMBLY Point
FROM '\\ShareName\Projects\Point\bin\Point.dll' 
WITH PERMISSION_SET = SAFE;

다음 Transact-SQL 문은 FROM 절에 <assembly_bits> 인수를 사용하여 어셈블리를 등록합니다. 이 varbinary 값은 파일을 바이트 스트림으로 나타냅니다.

USE AdventureWorks;
CREATE ASSEMBLY Point
FROM 0xfeac4 … 21ac78

CREATE TYPE 사용

어셈블리가 데이터베이스에 로드되면 Transact-SQL CREATE TYPE 문을 사용하여 형식을 만들 수 있습니다. 그러면 해당 데이터베이스에 사용 가능한 형식 목록에 해당 형식이 추가됩니다. 형식은 범위가 데이터베이스로 제한되므로 해당 형식을 만든 데이터베이스에만 사용할 수 있습니다. 데이터베이스에 해당 UDT가 이미 있으면 오류가 발생하며 CREATE TYPE 문이 실패합니다.

[!참고]

CREATE TYPE 구문은 네이티브 SQL Server 별칭 데이터 형식을 만드는 데도 사용되며 별칭 데이터 형식을 만들기 위한 수단으로서 sp_addtype을 대체합니다. CREATE TYPE 구문의 일부 선택적 인수는 UDT 만들기와 관련이 있으며 기본 유형과 같은 별칭 데이터 형식을 만드는 데 사용할 수 없습니다.

자세한 내용은 CREATE TYPE(Transact-SQL)을 참조하십시오.

예

다음 Transact-SQL 문은 Point 형식을 만듭니다. EXTERNAL NAME은 AssemblyName.UDTName이라는 두 부분으로 구성된 명명 구문을 사용하여 지정됩니다.

CREATE TYPE dbo.Point 
EXTERNAL NAME Point.[Point];

데이터베이스에서 UDT 제거

DROP TYPE 문은 현재 데이터베이스에서 UDT를 제거합니다. UDT를 삭제한 경우 DROP ASSEMBLY 문을 사용하여 데이터베이스에서 어셈블리를 삭제할 수 있습니다.

다음과 같은 상황에서는 DROP TYPE 문이 실행되지 않습니다.

  • UDT를 사용하여 정의한 열이 포함된 데이터베이스의 테이블

  • 데이터베이스에 WITH SCHEMABINDING 절로 만든 함수, 저장 프로시저 또는 트리거가 있고 이러한 루틴이 UDT의 변수 또는 매개 변수를 사용하는 경우

예

다음 Transact-SQL은 다음과 같은 순서로 실행해야 합니다. 먼저 Point UDT를 참조하는 테이블을 삭제한 다음 형식, 어셈블리를 차례로 삭제합니다.

DROP TABLE dbo.Points;
DROP TYPE dbo.Point;
DROP ASSEMBLY Point;

UDT 종속성 찾기

UDT 열 정의가 있는 테이블과 같은 종속 개체가 있으면 DROP TYPE 문이 실패합니다. 또한 WITH SCHEMABINDING 절을 사용하여 데이터베이스에 만든 함수, 저장 프로시저 또는 트리거가 있고 이러한 루틴에서 사용자 정의 형식의 변수 및 매개 변수를 사용하는 경우에도 실패합니다. 따라서 먼저 모든 종속 개체를 삭제한 다음 DROP TYPE 문을 실행해야 합니다.

다음 Transact-SQL 쿼리는 AdventureWorks 데이터베이스에서 UDT를 사용하는 열과 매개 변수를 모두 찾습니다.

USE Adventureworks;
SELECT o.name AS major_name, o.type_desc AS major_type_desc
     , c.name AS minor_name, c.type_desc AS minor_type_desc
     , at.assembly_class
  FROM (
        SELECT object_id, name, user_type_id, 'SQL_COLUMN' AS type_desc
          FROM sys.columns
     UNION ALL
        SELECT object_id, name, user_type_id, 'SQL_PROCEDURE_PARAMETER'
          FROM sys.parameters
     ) AS c
  JOIN sys.objects AS o
    ON o.object_id = c.object_id
  JOIN sys.assembly_types AS at
    ON at.user_type_id = c.user_type_id;

UDT 유지 관리

SQL Server 데이터베이스에 만든 UDT는 수정할 수 없습니다. 단, 해당 형식의 기반이 되는 어셈블리는 변경할 수 있습니다. 대부분의 경우 Transact-SQL DROP TYPE 문을 사용하여 데이터베이스에서 UDT를 제거하고 기본 어셈블리를 변경한 다음 ALTER ASSEMBLY 문을 사용하여 다시 로드해야 합니다. 그런 다음 UDT와 모든 종속 개체를 다시 만들어야 합니다.

예

UDT 어셈블리의 원본 코드를 변경하여 다시 컴파일한 후에는 ALTER ASSEMBLY 문이 사용됩니다. 이 문은 .dll 파일을 서버에 복사하고 새 어셈블리에 다시 바인딩합니다. 전체 구문을 보려면 ALTER ASSEMBLY(Transact-SQL)를 참조하십시오.

다음 Transact-SQL ALTER ASSEMBLY 문은 디스크의 지정된 위치에서 Point.dll 어셈블리를 다시 로드합니다.

ALTER ASSEMBLY Point
FROM '\\Projects\Point\bin\Point.dll'

ALTER ASSEMBLY를 사용하여 원본 코드 추가

ALTER ASSEMBLY 구문의 ADD FILE 절은 CREATE ASSEMBLY에 없습니다. 이 절을 사용하여 원본 코드나 어셈블리와 연결된 다른 파일을 추가할 수 있습니다. 이렇게 하면 파일이 원래 위치에서 복사되어 데이터베이스의 시스템 테이블에 저장됩니다. 따라서 UDT의 현재 버전을 다시 만들거나 문서화해야 할 경우 간편하게 원본 코드나 기타 파일을 사용할 수 있습니다.

다음 Transact-SQL ALTER ASSEMBLY 문은 Point UDT의 Point.cs 클래스 원본 코드를 추가합니다. 이 문은 Point.cs 파일에 있는 텍스트를 복사하여 "PointSource"라는 이름으로 데이터베이스에 저장합니다.

ALTER ASSEMBLY Point
ADD FILE FROM '\\Projects\Point\Point.cs' AS PointSource;

어셈블리 정보는 어셈블리가 설치된 데이터베이스의 sys.assembly_files 테이블에 저장됩니다. sys.assembly_files 테이블에는 다음 열이 있습니다.

  • assembly_id
    어셈블리에 대해 정의되는 식별자입니다. 해당 어셈블리와 관련한 모든 개체에 이 번호가 할당됩니다.

  • name
    개체 이름입니다.

  • file_id
    각 개체를 식별하는 번호이며 지정된 assembly_id와 연결된 첫 번째 개체에 값 1이 할당됩니다. 같은 assembly_id에 연결된 개체가 여러 개 있으면 차례로 1씩 증가한 file_id 값이 할당됩니다.

  • content
    어셈블리 또는 파일의 16진수 표현입니다.

CAST 또는 CONVERT 함수를 사용하면 content 열의 내용을 읽기 쉬운 텍스트로 변환할 수 있습니다. 다음 쿼리는 WHERE 절에 이름을 사용하여 결과 집합을 단일 행으로 제한함으로써 Point.cs 파일의 내용을 읽기 쉬운 텍스트로 변환합니다.

SELECT CAST(content AS varchar(8000)) 
  FROM sys.assembly_files 
  WHERE name='PointSource';

결과를 복사하여 텍스트 편집기에 붙여 넣으면 원래 내용에 있던 줄 바꿈과 공백이 그대로 유지되는 것을 알 수 있습니다.

UDT 및 어셈블리 관리

UDT 구현을 계획할 때는 UDT 어셈블리 자체에 필요한 메서드와 별도의 어셈블리에 만들고 사용자 정의 함수 또는 저장 프로시저로 구현해야 할 메서드를 고려해야 합니다. 메서드를 별도의 어셈블리로 분리하면 테이블의 UDT 열에 저장되어 있을 수 있는 데이터에 영향을 주지 않고 코드를 업데이트할 수 있습니다. 새 정의에서 변경되지 않은 형식의 이전 값과 서명을 읽을 수 있는 경우에만 UDT 열 및 기타 종속 개체를 삭제하지 않고 UDT 어셈블리를 수정할 수 있습니다.

변경될 수 있는 프로시저 코드를 UDT 구현에 필요한 코드와 분리하면 유지 관리가 훨씬 쉬워집니다. UDT 작동에 필요한 코드만 포함하고 UDT 정의를 가능한 한 단순하게 하면 코드 수정이나 버그 수정을 위해 UDT 자체를 데이터베이스에서 삭제해야 하는 위험을 줄일 수 있습니다.

Currency UDT 및 통화 변환 함수

AdventureWorks 예제 데이터베이스의 Currency UDT에서는 권장되는 UDT 및 관련 함수 구성 방법의 예를 보여 줍니다. Currency UDT는 특정 culture의 통화 시스템을 기준으로 금액을 처리하는 데 사용되며 달러, 유로 등 다양한 통화 유형을 저장할 수 있도록 합니다. UDT 클래스는 culture 이름을 문자열로 표시하고 금액을 decimal 데이터 형식으로 표시합니다. 필요한 모든 직렬화 메서드는 클래스를 정의하는 어셈블리에 들어 있습니다. 특정 culture에서 다른 culture로의 통화 변환을 구현하는 함수는 ConvertCurrency라는 외부 함수로 구현되며 별도의 어셈블리에 들어 있습니다. ConvertCurrency 함수는 AdventureWorks 데이터베이스의 테이블에서 환율을 검색하여 변환을 수행합니다. 환율의 원본을 변경하거나 기존 코드를 변경해야 하는 경우 Currency UDT에 영향을 주지 않고 쉽게 어셈블리를 수정할 수 있습니다.

Currency UDT 및 ConvertCurrency 함수의 코드 목록은 CLR(공용 언어 런타임) 예제를 설치하면 확인할 수 있습니다.

여러 데이터베이스에 UDT 사용

UDT는 기본적으로 단일 데이터베이스로 범위가 한정됩니다. 따라서 한 데이터베이스에 정의된 UDT를 다른 데이터베이스의 열 정의에 사용할 수 없습니다. 여러 데이터베이스에서 UDT를 사용하려면 각 데이터베이스의 동일한 어셈블리에서 CREATE ASSEMBLY 문과 CREATE TYPE 문을 실행해야 합니다. 어셈블리는 해당 이름, 강력한 이름, culture, 버전, 권한 집합 및 이진 내용이 같으면 동일한 것으로 간주됩니다.

두 데이터베이스에 UDT가 등록되어 있고 액세스할 수 있는 경우 한 데이터베이스의 UDT 값을 다른 데이터베이스에서 사용할 수 있도록 변환할 수 있습니다. 다음 시나리오에서 여러 데이터베이스에 동일한 UDT를 사용할 수 있습니다.

  • 서로 다른 데이터베이스에 정의된 저장 프로시저 호출

  • 서로 다른 데이터베이스에 정의된 여러 테이블 쿼리

  • 한 데이터베이스 테이블의 UDT 열에서 UDT 데이터를 선택하여 동일한 UDT 열이 있는 다른 한 데이터베이스에 삽입

이러한 시나리오에서는 필요한 변환이 서버에서 자동으로 수행됩니다. Transact-SQL CAST 함수나 CONVERT 함수를 사용하여 변환을 명시적으로 수행할 수는 없습니다.

SQL Server 데이터베이스 엔진이 tempdb 시스템 데이터베이스에 작업 테이블을 만드는 경우 UDT를 사용하기 위해 별도의 동작을 수행할 필요는 없습니다. 여기에는 UDT를 포함하고 명시적으로 tempdb를 사용하는 커서, 테이블 변수 및 사용자 정의 테이블 반환 함수에 대한 처리가 포함됩니다. 그러나 tempdb에 UDT 열을 정의하는 임시 테이블을 명시적으로 만든 경우에는 사용자 데이터베이스의 경우와 마찬가지로 tempdb에 UDT를 등록해야 합니다.

참고 항목

개념

CLR 사용자 정의 형식