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

아티클
05/30/2023

적용 대상:SQL Server

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

Visual Studio를 사용하여 UDT 배포

UDT를 배포하는 가장 쉬운 방법은 Microsoft Visual Studio를 사용하는 것입니다. 그러나 더 복잡한 배포 시나리오와 가장 큰 유연성의 경우 이 항목의 뒷부분에서 설명한 대로 Transact-SQL을 사용합니다.

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

Visual Basic 또는 Visual C# 언어 노드에서 새 데이터베이스 프로젝트를 만듭니다.
UDT를 포함할 SQL Server 데이터베이스에 대한 참조를 추가합니다.
사용자 정의 형식 클래스를 추가합니다.
UDT를 구현하는 코드를 작성합니다.
빌드 메뉴에서 배포를 선택합니다. 이렇게 하면 어셈블리가 등록되고 SQL Server 데이터베이스에 형식이 만들어집니다.

Transact-SQL을 사용하여 UDT 배포

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

CREATE ASSEMBLY 사용

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

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

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

예제

다음 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 연결된 여러 개체가 있는 경우 이후 의 각 file_id 값이 1씩 증가합니다.

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

CAST 또는 CONVERT 함수를 사용하여 콘텐츠 열의 내용을 읽을 수 있는 텍스트로 변환할 수 있습니다. 다음 쿼리는 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 및 관련 함수를 구성하는 권장 방법의 유용한 예제를 제공합니다. 통화 UDT는 특정 문화권의 통화 시스템을 기반으로 돈을 처리하는 데 사용되며 달러, 유로 등과 같은 다양한 통화 유형의 저장을 허용합니다. UDT 클래스는 문화권 이름을 문자열로, 금액은 10진 수 데이터 형식으로 노출합니다. 필요한 모든 직렬화 메서드는 클래스를 정의하는 어셈블리에 들어 있습니다. 한 문화권에서 다른 문화권으로의 통화 변환을 구현하는 함수는 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를 투명하게 사용하는 커서, 테이블 변수 및 사용자 정의 테이블 반환 함수의 처리가 포함됩니다. 그러나 UDT 열을 정의하는 임시 테이블을 tempdb 에 명시적으로 만드는 경우 UDT는 사용자 데이터베이스와 동일한 방식으로 tempdb 에 등록되어야 합니다.

참고 항목

CLR 사용자 정의 형식