CREATE TYPE(Transact-SQL)

아티클
08/22/2024

적용 대상: SQL Server Azure SQL 데이터베이스 Azure SQL Managed Instance

SQL Server 또는 Azure SQL Database에서 현재 데이터베이스에 별칭 데이터 형식 또는 사용자 정의 형식을 만듭니다. 별칭 데이터 형식의 구현은 데이터베이스 엔진 네이티브 시스템 형식을 기반으로 합니다. 사용자 정의 형식은 Microsoft.NET Framework CLR(공용 언어 런타임)에서 어셈블리의 클래스를 통해 구현됩니다. 사용자 정의 형식을 구현에 바인딩하려면 먼저 CREATE ASSEMBLY를 사용하여 형식의 구현을 포함하는 CLR 어셈블리를 데이터베이스 엔진 등록해야 합니다.

SQL Server에서는 CLR 코드 실행 기능이 기본적으로 해제되어 있습니다. 관리 코드 모듈을 참조하는 데이터베이스 개체를 만들고 수정하고 삭제할 수 있습니다. 그러나 이러한 참조는 sp_configure 사용하여 clr 사용 옵션을 사용하도록 설정하지 않는 한 SQL Server에서 실행되지 않습니다.

참고 항목

이 항목에서는 .NET Framework CLR을 SQL Server에 통합하는 방법에 대해 설명합니다. CLR 통합은 Azure SQL Database에 적용되지 않습니다.

Transact-SQL 구문 표기 규칙

구문

사용자 정의 데이터 형식 구문:

CREATE TYPE [ schema_name. ] type_name
{
      FROM base_type
      [ ( precision [ , scale ] ) ]
      [ NULL | NOT NULL ]
    | EXTERNAL NAME assembly_name [ .class_name ]
    | AS TABLE ( { <column_definition> | <computed_column_definition> [ , ...n ]
      [ <table_constraint> ] [ , ...n ]
      [ <table_index> ] [ , ...n ] } )
} [ ; ]

<column_definition> ::=
column_name <data_type>
    [ COLLATE collation_name ]
    [ NULL | NOT NULL ]
    [
        DEFAULT constant_expression ]
      | [ IDENTITY [ ( seed , increment ) ]
    ]
    [ ROWGUIDCOL ] [ <column_constraint> [ ...n ] ]

<data type> ::=
[ type_schema_name . ] type_name
    [ ( precision [ , scale ] | max |
                [ { CONTENT | DOCUMENT } ] xml_schema_collection ) ]

<column_constraint> ::=
{ { PRIMARY KEY | UNIQUE }
        [ CLUSTERED | NONCLUSTERED ]
        [
            WITH ( <index_option> [ , ...n ] )
        ]
  | CHECK ( logical_expression )
}

<computed_column_definition> ::=
column_name AS computed_column_expression
[ PERSISTED [ NOT NULL ] ]
[
    { PRIMARY KEY | UNIQUE }
        [ CLUSTERED | NONCLUSTERED ]
        [
            WITH ( <index_option> [ , ...n ] )
        ]
    | CHECK ( logical_expression )
]

<table_constraint> ::=
{
    { PRIMARY KEY | UNIQUE }
        [ CLUSTERED | NONCLUSTERED ]
    ( column [ ASC | DESC ] [ , ...n ] )
        [
    WITH ( <index_option> [ , ...n ] )
        ]
    | CHECK ( logical_expression )
}

<index_option> ::=
{
    IGNORE_DUP_KEY = { ON | OFF }
}

< table_index > ::=
  INDEX index_name
     [ CLUSTERED | NONCLUSTERED ] (column [ ASC | DESC ] [ , ...n ] )
     [INCLUDE (column, ...n)]

사용자 정의 메모리 최적화 테이블 형식 구문:

CREATE TYPE [ schema_name. ] type_name
AS TABLE ( { <column_definition> [ , ...n ] }
    | [ <table_constraint> ] [ , ...n ]
    | [ <table_index> ] [ , ...n ] )
    [ WITH ( <table_option> [ , ...n ] ) ]
 [ ; ]

<column_definition> ::=
column_name <data_type>
    [ COLLATE collation_name ] [ NULL | NOT NULL ]
      [ IDENTITY [ (1 , 1) ]
    ]
    [ <column_constraint> [ , ...n ] ] [ <column_index> ]

<data type> ::=
 [ type_schema_name . ] type_name [ ( precision [ , scale ] ) ]

<column_constraint> ::=
{ PRIMARY KEY { NONCLUSTERED HASH WITH ( BUCKET_COUNT = bucket_count )
                | NONCLUSTERED }
}

< table_constraint > ::=
{ PRIMARY KEY { NONCLUSTERED HASH (column [ , ...n ] )
                   WITH ( BUCKET_COUNT = bucket_count )
               | NONCLUSTERED ( column [ ASC | DESC ] [ , ...n ] )
           }
}

<column_index> ::=
  INDEX index_name
{ [ NONCLUSTERED ] HASH (column [ , ...n ] ) WITH ( BUCKET_COUNT = bucket_count ) 
      | NONCLUSTERED ( column [ ASC | DESC ] [ , ...n ] )
}

< table_index > ::=
  INDEX index_name
{ [ NONCLUSTERED ] HASH (column [ , ...n ] ) WITH ( BUCKET_COUNT = bucket_count )
    | [ NONCLUSTERED ] ( column [ ASC | DESC ] [ , ...n ] )
}

<table_option> ::=
{
    [ MEMORY_OPTIMIZED = { ON | OFF } ]
}

인수

schema_name

별칭 데이터 형식 또는 사용자 정의 형식이 속한 스키마의 이름입니다.

type_name

별칭 데이터 형식 또는 사용자 정의 형식의 이름입니다. 형식 이름은 식별자에 대한 규칙을 따라야 합니다.

base_type

별칭 데이터 형식의 기반이 되는 데이터베이스 엔진 제공된 데이터 형식입니다. base_type은 sysname이고 기본값은 없으며 다음 값 중 하나를 사용할 수 있습니다.

bigint, int, smallint 및 tinyint
binary(n), varbinary(n) 및 varbinary(max)
bit
char(n), nchar(n), nvarchar(n), nvarchar(max), varchar(n), varchar(max)
date, datetime, datetime2, datetimeoffset, smalldatetime 및 time
decimal 및 numeric
float 및 real
image
money and smallmoney
sql_variant
text 및 ntext
uniqueidentifier

base_type은 이러한 시스템 데이터 형식 중 하나에 매핑되는 데이터 형식 동의어도 될 수 있습니다.

전체 자릿수

10진수 또는 숫자의 경우 전체 자릿수는 소수점 왼쪽과 오른쪽 모두에 저장할 수 있는 최대 소수 자릿수를 나타내는 음수가 아닌 정수입니다. 자세한 내용은 decimal 및 numeric(Transact-SQL)을 참조하세요.

scale

소수점 또는 숫자의 경우 소수 자릿수는 소수점 오른쪽에 저장할 수 있는 최대 소수 자릿수를 나타내는 음수가 아닌 정수이며 전체 자릿수보다 작거나 같아야 합니다. 자세한 내용은 decimal 및 numeric(Transact-SQL)을 참조하세요.

NULL | NOT NULL

해당 형식이 Null 값을 보관할 수 있는지 여부를 지정합니다. 지정하지 않으면 NULL이 기본값입니다.

assembly_name

적용 대상: SQL Server

공용 언어 런타임에서 사용자 정의 형식의 구현을 참조하는 SQL Server 어셈블리를 지정합니다. assembly_name은 현재 데이터베이스에 있는 SQL Server의 기존 어셈블리와 일치해야 합니다.

참고 항목

EXTERNAL_NAME 은 포함된 데이터베이스에서 사용할 수 없습니다.

[ . class_name ]

적용 대상: SQL Server

사용자 정의 형식을 구현하는 어셈블리 내 클래스를 지정합니다. class_name은 유효한 식별자여야 하며 어셈블리 표시 유형이 있는 어셈블리의 클래스로 존재해야 합니다. class_name은 데이터베이스의 데이터 정렬과 무관하게 대/소문자를 구분하며 해당 어셈블리의 클래스 이름과 정확히 일치해야 합니다. 클래스 작성에 사용되는 프로그래밍 언어가 C#과 같이 네임스페이스 개념을 사용하는 경우 클래스 이름이 대괄호( [ ] )로 묶은 정식 네임스페이스 이름이 될 수 있습니다. class_name 지정되지 않은 경우 SQL Server는 type_name 동일하다고 가정합니다.

<column_definition>

사용자 정의 테이블 형식의 열을 정의합니다.

<데이터 형식>

사용자 정의 테이블 형식에 대한 열의 데이터 형식을 정의합니다. 데이터 형식에 대한 자세한 내용은 데이터 형식(Transact-SQL)을 참조하세요. 테이블에 대한 자세한 내용은 CREATE TABLE(Transact-SQL)을 참조하세요.

<column_constraint>

사용자 정의 테이블 형식에 대한 열 제약 조건을 정의합니다. 지원되는 제약 조건에는 PRIMARY KEY, UNIQUE및 CHECK. 테이블에 대한 자세한 내용은 CREATE TABLE(Transact-SQL)을 참조하세요.

<computed_column_definition>

계산 열 식을 사용자 정의 테이블 형식의 열로 정의합니다. 테이블에 대한 자세한 내용은 CREATE TABLE(Transact-SQL)을 참조하세요.

<table_constraint>

사용자 정의 테이블 형식의 테이블 제약 조건을 정의합니다. 지원되는 제약 조건에는 PRIMARY KEY, UNIQUE및 CHECK.

<index_option>

고유 클러스터형 또는 고유 비클러스터형 인덱스에 여러 행을 삽입하는 작업에서 중복된 키 값이 있는 경우에 대한 오류 응답을 지정합니다. 인덱스 옵션에 대한 자세한 내용은 CREATE INDEX(Transact-SQL)를 참조하세요.

INDEX index_name [ CLUSTERED | 비클러스터형 ] ( column_name [ ASC | DESC ] [ , ... n ] )

적용 대상: SQL Server 2014(12.x) 이상 버전, Azure SQL Database 및 Azure SQL Managed Instance.

테이블에 인덱스를 만들도록 지정합니다. 이는 클러스터형 인덱스 또는 비클러스터형 인덱스일 수 있습니다. 인덱스에는 나열된 열이 포함되며 데이터를 오름차순 또는 내림차순으로 정렬합니다.

INDEX

문의 일부로 CREATE TABLE 열 및 테이블 인덱스를 지정해야 합니다. CREATE INDEX 메모리 DROP INDEX 최적화 테이블에 대해 지원되지 않습니다.

MEMORY_OPTIMIZED

적용 대상: SQL Server 2014(12.x) 이상 버전, Azure SQL Database 및 Azure SQL Managed Instance. Azure SQL Managed Instance는 범용 계층에서 메모리 최적화 테이블을 지원하지 않습니다.

테이블 형식이 메모리 액세스에 최적화된 형식인지 여부를 나타냅니다. 이 옵션은 기본적으로 꺼져 있습니다. 테이블(형식)은 메모리 최적화 테이블(형식)이 아닙니다. 메모리 최적화 테이블 형식은 메모리 최적화 사용자 테이블입니다. 디스크에 저장된 스키마는 다른 사용자 테이블과 비슷합니다.

BUCKET_COUNT

적용 대상: SQL Server 2014(12.x) 이상 버전, Azure SQL Database, Azure SQL Database 및 Azure SQL Managed Instance.

해시 인덱스에서 만들어야 하는 버킷 수를 나타냅니다. 해시 인덱스의 최대값 BUCKET_COUNT 은 1,073,741,824입니다. 버킷 수에 대한 자세한 내용은 메모리 최적화 테이블의 인덱스를 참조 하세요. bucket_count는 필수 인수입니다.

HASH

적용 대상: SQL Server 2014(12.x) 이상 버전, Azure SQL Database, Azure SQL Database 및 Azure SQL Managed Instance.

인덱스가 HASH 생성되었음을 나타냅니다. 해시 인덱스는 메모리 액세스에 최적화된 테이블에서만 지원됩니다.

설명

assembly_name에서 참조하는 어셈블리의 클래스는 클래스의 메서드와 함께 SQL Server에서 사용자 정의 형식을 구현하기 위한 모든 요구 사항을 만족시켜야 합니다. 이러한 요구 사항에 대한 자세한 내용은 CLR User-Defined Types를 참조하세요.

추가적인 고려 사항은 다음과 같습니다.

클래스는 오버로드된 메서드를 포함할 수 있지만 이러한 메서드는 Transact-SQL이 아닌 관리 코드 내에서만 호출할 수 있습니다.
정적 멤버는 assembly_name 경우 const 또는 readonly EXTERNAL_ACCESS SAFE 로 선언되어야 합니다.

데이터베이스 내의 CLR에서 SQL Server에 업로드하는 각 형식에 대한 사용자 정의 형식은 오직 하나만 등록할 수 있습니다. 사용자 정의 형식이 데이터베이스 CREATE TYPE 에 이미 있는 CLR 형식에 사용자 정의 형식이 만들어지면 오류가 발생합니다. 이런 제한은 CLR 형식을 둘 이상의 사용자 정의 형식에 매핑할 수 있는 경우 SQL 형식 확인 과정의 모호성을 피하기 위해 필요합니다.

형식의 변경자 메서드가 voidCREATE TYPE를 반환하지 않으면 문이 실행되지 않습니다.

사용자 정의 형식을 수정하려면 문을 사용하여 형식을 삭제한 DROP TYPE 다음 다시 만들어야 합니다.

사용 하 여 만든 사용자 정의 형식과 달리 공용 데이터베이스 역할은 사용 하 여 sp_addtypeCREATE TYPE만든 형식에 대 한 권한을 자동으로 부여 REFERENCES 되지 않습니다. 이 권한은 별도로 허가해야 합니다.

사용자 정의 테이블 형식에서 column_name<data type>에 사용되는 구조적 사용자 정의 형식은 해당 테이블 유형이 정의된 데이터베이스 스키마 범위의 일부입니다. 데이터베이스 내의 다른 범위에 있는 구조적 사용자 정의 형식에 액세스하려면 두 부분으로 된 이름을 사용하세요.

사용자 정의 테이블 형식에서 계산 열의 기본 키는 다음과 여야 PERSISTED NOT NULL합니다.

메모리 최적화 테이블 형식

SQL Server 2014(12.x)부터 테이블 형식의 데이터 처리는 디스크가 아닌 기본 메모리에서 수행될 수 있습니다. 자세한 내용은 메모리 내 OLTP 개요 및 사용 시나리오를 참조하세요. 메모리 최적화 테이블을 만드는 방법을 보여주는 코드 샘플은 메모리 최적화 테이블 및 고유하게 컴파일된 저장 프로시저 만들기를 참조하세요.

사용 권한

CREATE TYPE 현재 데이터베이스에 대한 사용 권한 및 ALTER schema_name 대한 권한이 필요합니다. schema_name 지정되지 않은 경우 현재 사용자의 스키마를 결정하기 위한 기본 이름 확인 규칙이 적용됩니다. assembly_name 지정한 경우 사용자는 어셈블리를 소유하거나 REFERENCES 해당 어셈블리에 대한 권한이 있어야 합니다.

CREATE TABLE 문의 열이 사용자 정의 형식으로 정의되면 해당 형식에 대한 REFERENCES 권한이 필요합니다.

사용자 정의 형식을 사용하는 열이 있는 테이블을 만드는 사용자는 사용자 정의 형식에 대한 권한이 필요합니다 REFERENCES . 이 테이블을 만들어야 tempdbREFERENCES 하는 경우 테이블을 만들기 전에 매번 사용 권한을 명시적으로 부여해야 하거나 이 데이터 형식과 REFERENCES 사용 권한을 데이터베이스에 model 추가해야 합니다. 예시:

CREATE TYPE dbo.udt_money FROM varchar(11) NOT NULL;
GO
GRANT REFERENCES ON TYPE::dbo.udt_money TO public

이 작업을 수행하면 이 데이터 형식 및 REFERENCES 사용 권한을 영구적으로 사용할 수 있습니다 tempdb . 그렇지 않으면 SQL Server를 다시 시작할 경우 사용자 정의 데이터 형식 및 사용 권한이 사라집니다. 자세한 내용은 CREATE TABLE을 참조하세요.

모든 새 데이터베이스가 모델에서 이 사용자 정의 데이터 형식에 대한 정의 및 권한을 상속하지 않도록 하려면 시작 저장 프로시저를 사용하여 데이터베이스에서 tempdb 만 적절한 권한을 만들고 할당할 수 있습니다. 예시:

USE master
GO
CREATE PROCEDURE setup_udt_in_tempdb
AS
EXEC ( 'USE tempdb;
CREATE TYPE dbo.udt_money FROM varchar(11) NOT NULL;
GRANT REFERENCES ON TYPE::dbo.udt_money TO public;')
GO
EXEC sp_procoption 'setup_udt_in_tempdb' , 'startup' , 'on'
GO

또는 임시 테이블을 사용하는 대신 임시 스토리지 요구 사항에 대해 사용자 정의 데이터 형식을 참조해야 하는 경우 테이블 변수를 사용하는 것이 좋습니다. 테이블 변수가 사용자 정의 데이터 형식을 참조하려면 사용자 정의 데이터 형식에 대한 권한을 명시적으로 부여할 필요가 없습니다.

예제

A. varchar 데이터 형식을 기반으로 별칭 형식 만들기

다음 예에서는 시스템이 제공하는 varchar 데이터 형식을 기반으로 별칭 유형을 만드는 방법을 보여 줍니다.

CREATE TYPE SSN
FROM VARCHAR(11) NOT NULL;

B. 사용자 정의 형식 만들기

적용 대상: SQL Server

다음 예에서는 utf8string 어셈블리 내의 utf8string클래스를 참조하는 Utf8String 형식을 만드는 방법을 보여 줍니다. 형식을 만들기 전에 로컬 데이터베이스에 utf8string 어셈블리를 등록합니다. 문의 이진 부분을 CREATE ASSEMBLY 유효한 설명으로 바꿉니다.

CREATE ASSEMBLY utf8string
AUTHORIZATION [dbi]
FROM 0x4D... ;
GO

CREATE TYPE Utf8String
EXTERNAL NAME utf8string.[Microsoft.Samples.SqlServer.utf8string];
GO

C. 사용자 정의 테이블 형식 만들기

다음 예에서는 두 개의 열이 있는 사용자 정의 테이블 형식을 만듭니다. 테이블 반환 매개 변수를 만들고 사용하는 방법은 테이블 반환 매개 변수 사용(데이터베이스 엔진)을 참조하세요.

CREATE TYPE LocationTableType AS TABLE (
    LocationName VARCHAR(50),
    CostRate INT
);
GO

D. 기본 키 및 인덱스가 있는 사용자 정의 테이블 형식 만들기

다음 예제에서는 세 개의 열이 있는 사용자 정의 테이블 형식을 만듭니다. Name 열에는 기본 키가 있고, 다른 Price 열에는 비클러스터형 인덱스가 있습니다. 테이블 반환 매개 변수를 만들고 사용하는 방법은 테이블 반환 매개 변수 사용(데이터베이스 엔진)을 참조하세요.

CREATE TYPE InventoryItem AS TABLE (
    [Name] NVARCHAR(50) NOT NULL,
    SupplierId BIGINT NOT NULL,
    Price DECIMAL(18, 4) NULL,
    PRIMARY KEY (Name),
    INDEX IX_InventoryItem_Price(Price)
);
GO

다음을 통해 공유

CREATE TYPE(Transact-SQL)

구문

인수

schema_name

type_name

base_type

전체 자릿수

scale

NULL | NOT NULL

assembly_name

[ . class_name ]

<column_definition>

<데이터 형식>

<column_constraint>

<computed_column_definition>

<table_constraint>

<index_option>

INDEX index_name [ CLUSTERED | 비클러스터형 ] ( column_name [ ASC | DESC ] [ , ... n ] )

INDEX

MEMORY_OPTIMIZED

BUCKET_COUNT

HASH

설명

메모리 최적화 테이블 형식

사용 권한

예제

A. varchar 데이터 형식을 기반으로 별칭 형식 만들기

B. 사용자 정의 형식 만들기

C. 사용자 정의 테이블 형식 만들기

D. 기본 키 및 인덱스가 있는 사용자 정의 테이블 형식 만들기

피드백

추가 리소스

다음을 통해 공유

CREATE TYPE(Transact-SQL)

구문

인수

schema_name

type_name

base_type

전체 자릿수

scale

NULL | NOT NULL

assembly_name

[ . class_name ]

<column_definition>

<데이터 형식>

<column_constraint>

<computed_column_definition>

<table_constraint>

<index_option>

INDEX index_name [ CLUSTERED | 비클러스터형 ] ( column_name [ ASC | DESC ] [ , ... n ] )

INDEX

MEMORY_OPTIMIZED

BUCKET_COUNT

HASH

설명

메모리 최적화 테이블 형식

사용 권한

예제

A. varchar 데이터 형식을 기반으로 별칭 형식 만들기

B. 사용자 정의 형식 만들기

C. 사용자 정의 테이블 형식 만들기

D. 기본 키 및 인덱스가 있는 사용자 정의 테이블 형식 만들기

관련 콘텐츠

피드백

추가 리소스