다음을 통해 공유

CREATE FUNCTION(Transact-SQL)

  • 아티클

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

Transact-SQL 또는 CLR(공용 언어 런타임) 루틴인 UDF(사용자 정의 함수)를 만듭니다. 사용자 정의 함수는 매개 변수를 허용하고, 복잡한 계산과 같은 작업을 수행하고, 해당 작업의 결과를 값으로 반환합니다. 반환 값은 단일 스칼라 값이나 테이블일 수 있습니다. 이 문을 사용하여 다음과 같은 상황에서 다시 사용할 수 있는 루틴을 만들 수 있습니다.

  • Transact-SQL 문에서 SELECT
  • 함수를 호출하는 애플리케이션에서
  • 다른 사용자 정의 함수의 정의
  • 뷰를 매개 변수화하거나 인덱싱된 뷰의 기능 향상
  • 테이블의 열 정의
  • 열에 대한 제약 조건을 CHECK 정의하려면
  • 저장 프로시저 바꾸기
  • 인라인 함수를 보안 정책의 필터 조건자로 사용

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

Azure Synapse Analytics 또는 Microsoft Fabric의 경우 CREATE FUNCTION(Azure Synapse Analytics 및 Microsoft Fabric)을 참조하세요.

Transact-SQL 구문 표기 규칙

구문

Transact-SQL 스칼라 함수에 대한 구문입니다.

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
 [ = default ] [ READONLY ] }
    [ , ...n ]
  ]
)
RETURNS return_data_type
    [ WITH <function_option> [ , ...n ] ]
    [ AS ]
    BEGIN
        function_body
        RETURN scalar_expression
    END
[ ; ]

Transact-SQL 인라인 테이블 반환 함수에 대한 구문입니다.

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
    [ = default ] [ READONLY ] }
    [ , ...n ]
  ]
)
RETURNS TABLE
    [ WITH <function_option> [ , ...n ] ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

Transact-SQL 다중 문 테이블 반환 함수에 대한 구문입니다.

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
    [ = default ] [ READONLY ] }
    [ , ...n ]
  ]
)
RETURNS @return_variable TABLE <table_type_definition>
    [ WITH <function_option> [ , ...n ] ]
    [ AS ]
    BEGIN
        function_body
        RETURN
    END
[ ; ]

Transact-SQL 함수 절에 대한 구문입니다.

<function_option> ::=
{
    [ ENCRYPTION ]
  | [ SCHEMABINDING ]
  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
  | [ EXECUTE_AS_Clause ]
  | [ INLINE = { ON | OFF } ]
}

<table_type_definition> ::=
( { <column_definition> <column_constraint>
  | <computed_column_definition> }
    [ <table_constraint> ] [ , ...n ]
)
<column_definition> ::=
{
    { column_name data_type }
    [ [ DEFAULT constant_expression ]
      [ COLLATE collation_name ] | [ ROWGUIDCOL ]
    ]
    | [ IDENTITY [ (seed , increment ) ] ]
    [ <column_constraint> [ ...n ] ]
}

<column_constraint> ::=
{
    [ NULL | NOT NULL ]
    { PRIMARY KEY | UNIQUE }
      [ CLUSTERED | NONCLUSTERED ]
      [ WITH FILLFACTOR = fillfactor
        | WITH ( <index_option> [ , ...n ] )
      [ ON { filegroup | "default" } ] ]
  | [ CHECK ( logical_expression ) ] [ , ...n ]
}

<computed_column_definition> ::=
column_name AS computed_column_expression

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

<index_option> ::=
{
    PAD_INDEX = { ON | OFF }
  | FILLFACTOR = fillfactor
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
}

CLR 스칼라 함수의 구문입니다.

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
    [ = default ] }
    [ , ...n ]
)
RETURNS { return_data_type }
    [ WITH <clr_function_option> [ , ...n ] ]
    [ AS ] EXTERNAL NAME <method_specifier>
[ ; ]

CLR 테이블 반환 함수의 구문입니다.

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
    [ = default ] }
    [ , ...n ]
)
RETURNS TABLE <clr_table_type_definition>
    [ WITH <clr_function_option> [ , ...n ] ]
    [ ORDER ( <order_clause> ) ]
    [ AS ] EXTERNAL NAME <method_specifier>
[ ; ]

CLR 함수 절에 대한 구문입니다.

<order_clause> ::=
{
   <column_name_in_clr_table_type_definition>
   [ ASC | DESC ]
} [ , ...n ]

<method_specifier> ::=
    assembly_name.class_name.method_name

<clr_function_option> ::=
{
    [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
  | [ EXECUTE_AS_Clause ]
}

<clr_table_type_definition> ::=
( { column_name data_type } [ , ...n ] )

고유하게 컴파일된 스칼라 사용자 정의 함수에 대한 메모리 내 OLTP 구문입니다.

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
 ( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type
    [ NULL | NOT NULL ] [ = default ] [ READONLY ] }
    [ , ...n ]
  ]
)
RETURNS return_data_type
     WITH <function_option> [ , ...n ]
    [ AS ]
    BEGIN ATOMIC WITH (set_option [ , ... n ] )
        function_body
        RETURN scalar_expression
    END

<function_option> ::=
{
  |  NATIVE_COMPILATION
  |  SCHEMABINDING
  | [ EXECUTE_AS_Clause ]
  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
}

인수

OR ALTER

적용 대상: SQL Server 2016(13.x) SP 1 이상 버전 및 Azure SQL Database.

이미 있는 경우에만 함수를 조건부로 변경합니다.

선택적 OR ALTER 구문은 SQL Server 2016(13.x) SP 1 CU 1부터 CLR에 사용할 수 있습니다.

schema_name

사용자 정의 함수가 속한 스키마의 이름입니다.

function_name

사용자 정의 함수의 이름입니다. 함수 이름은 식별자에 대한 규칙을 따라야 하며 데이터베이스에서 각 스키마별로 고유해야 합니다.

매개 변수가 지정되지 않은 경우에도 함수 이름 다음에 괄호가 필요합니다.

@parameter_name

사용자 정의 함수의 매개 변수입니다. 하나 이상의 매개 변수를 선언할 수 있습니다.

하나의 함수에 최대 2,100개의 매개 변수를 지정할 수 있습니다. 매개 변수에 기본값이 정의되지 않은 경우 함수를 실행할 때 사용자가 선언된 각 매개 변수의 값을 지정해야 합니다.

at 기호(@)를 첫 번째 문자로 사용하여 매개 변수 이름을 지정합니다. 매개 변수 이름은 식별자에 대한 규칙을 따라야 합니다. 매개 변수는 함수에서 로컬로 사용되므로 다른 함수에서 동일한 매개 변수 이름을 사용할 수 있습니다. 매개 변수는 상수만 사용할 수 있습니다. 테이블 이름, 열 이름 또는 다른 데이터베이스 개체의 이름 대신 사용할 수 없습니다.

ANSI_WARNINGS 는 저장 프로시저, 사용자 정의 함수에서 매개 변수를 전달하거나 Batch 문에서 변수를 선언하고 설정할 때 적용되지 않습니다. 예를 들어 변수가 char(3) 로 정의된 경우 3자보다 큰 값으로 설정하면 해당 데이터가 정의된 크기로 잘리고 INSERT 또는 UPDATE 문은 성공합니다.

[ type_schema_name. ] parameter_data_type

매개 변수 데이터 형식 및 매개 변수가 속한 스키마(선택 사항)입니다. Transact-SQL 함수의 경우 timestamp 데이터 형식을 제외하고 CLR 사용자 정의 형식 및 사용자 정의 테이블 형식을 비롯한 모든 데이터 형식이 허용됩니다. CLR 함수의 경우 텍스트, ntext, 이미지, 사용자 정의 테이블 형식 및 타임스탬프 데이터 형식을 제외한 CLR 사용자 정의 형식을 포함한 모든 데이터 형식이 허용됩니다. 비칼라 형식, 커서 및 테이블은 Transact-SQL 또는 CLR 함수에서 매개 변수 데이터 형식으로 지정할 수 없습니다.

type_schema_name 지정되지 않은 경우 데이터베이스 엔진 다음 순서로 scalar_parameter_data_type 찾습니다.

  • SQL Server 시스템 데이터 형식의 이름이 포함된 스키마
  • 현재 데이터베이스에 있는 현재 사용자의 기본 스키마
  • 현재 데이터베이스의 dbo 스키마

[ = default ]

매개 변수의 기본값입니다. 기본 값이 정의되어 있으면 해당 매개 변수 값을 지정하지 않아도 함수를 실행할 수 있습니다.

varchar(max) 및 varbinary(max) 데이터 형식을 제외하고 CLR 함수에 대해 기본 매개 변수 값을 지정할 수 있습니다.

함수의 매개 변수에 기본값이 있는 경우 기본값을 검색하기 위해 함수를 호출할 때 키워드 DEFAULT 를 지정해야 합니다. 이 동작은 매개 변수를 생략할 경우 자동으로 기본값이 사용되는 저장 프로시저에서 기본값이 있는 매개 변수를 사용하는 것과는 다릅니다. 그러나 DEFAULT 문을 사용하여 스칼라 함수를 호출할 때는 키워드가 EXECUTE 필요하지 않습니다.

READONLY

함수 정의 내에서 매개 변수를 업데이트하거나 수정할 수 없음을 나타냅니다. READONLY 는 TVP(사용자 정의 테이블 형식 매개 변수)에 필요하며 다른 매개 변수 형식에는 사용할 수 없습니다.

return_data_type

스칼라 사용자 정의 함수의 반환 값입니다. Transact-SQL 함수의 경우 timestamp 데이터 형식을 제외하고 CLR 사용자 정의 형식을 비롯한 모든 데이터 형식이 허용됩니다. CLR 함수의 경우 text, ntext, image 및 timestamp 데이터 형식을 제외하고 CLR 사용자 정의 형식을 비롯한 모든 데이터 형식이 허용됩니다. 비칼라 형식, 커서 및 테이블은 Transact-SQL 또는 CLR 함수에서 반환 데이터 형식으로 지정할 수 없습니다.

function_body

테이블 수정과 같은 부작용을 생성하지 않는 일련의 Transact-SQL 문이 함수의 값을 정의할 수 있도록 지정합니다. function_body는 스칼라 함수와 다중 명령문 테이블 반환 함수(MSTVF)에서만 사용됩니다.

스칼라 함수에서 function_body는 함께 계산되어 스칼라 값을 반환하는 일련의 Transact-SQL 문입니다.

MSTVF에서 function_body 반환 변수를 채우는 TABLE 일련의 Transact-SQL 문입니다.

scalar_expression

스칼라 함수가 반환하는 스칼라 값을 지정합니다.

TABLE

TVF(테이블 반환 함수)의 반환 값이 테이블임을 지정합니다. 상수 및 @local_variables만 TVF에 전달할 수 있습니다.

인라인 TVF에서 TABLE 반환 값은 단일 SELECT 문을 통해 정의됩니다. 인라인 함수에는 연결된 반환 변수가 없습니다.

MSTVF에서 @return_variable 함수 값으로 반환되어야 하는 행을 저장하고 누적하는 데 사용되는 변수입니다 TABLE . @return_variable은 Transact-SQL 함수에서만 지정할 수 있으며 CLR 함수에서는 지정할 수 없습니다.

select_stmt

TVF(인라인 테이블 반환 함수)의 반환 값을 정의하는 단일 SELECT 문입니다.

ORDER (<order_clause>)

테이블 반환 함수에서 결과가 반환되는 순서를 지정합니다. 자세한 내용은 이 문서의 뒷부분에 있는 CLR 테이블 반환 함수에서 정렬 순서 사용 섹션 을 참조하세요.

EXTERNAL NAME <method_specifier>assembly_name.class_name.method_name

적용 대상: SQL Server 2008(10.0.x) SP 1 이상 버전.

만든 함수 이름에서 참조할 어셈블리 및 메서드를 지정합니다.

  • assembly_name - SELECT * FROM sys.assemblies;의 name 열에 있는 값과 일치해야 합니다.

    문에 사용된 이름입니다 CREATE ASSEMBLY .

  • class_name - SELECT * FROM sys.assembly_modules;의 assembly_name 열에 있는 값과 일치해야 합니다.

    흔히 이 값은 마침표 또는 점을 포함합니다. 이러한 경우 Transact-SQL 구문을 사용하려면 값이 대괄호 쌍() 또는 큰따옴표([]"") 쌍으로 바인딩되어야 합니다.

  • method_name - SELECT * FROM sys.assembly_modules;의 method_name 열에 있는 값과 일치해야 합니다.

    메서드는 정적이어야 합니다.

모든 형식이 네임스페이스에 있는 일반적인 예MyFood.dll에서는 값이 EXTERNAL NAME 될 수 있습니다MyFood.[MyFood.MyClass].MyStaticMethodMyFood.

기본적으로 SQL Server에서는 CLR 코드를 실행할 수 없습니다. 공용 언어 런타임 모듈을 참조하는 데이터베이스 개체를 만들고 수정하고 삭제할 수 있습니다. 그러나 clr 사용 옵션을 사용하도록 설정하기 전까지는 SQL Server에서 이러한 참조를 실행할 수 없습니다. 이 옵션을 설정하려면 sp_configure를 사용합니다. 이 옵션은 포함된 데이터베이스에서 사용할 수 없습니다.

<> table_type_definition ( { <column_definition><column_constraint | <> computed_column_definition } [ <table_constraint> ] [ , ...n ] )

Transact-SQL 함수에 대한 테이블 데이터 형식을 정의합니다. 테이블 선언에는 열 정의와 열 또는 테이블 제약 조건이 포함됩니다. 테이블은 항상 주 파일 그룹에 포함됩니다.

<> clr_table_type_definition ( { column_namedata_type } [ , ...n ] )

적용 대상: SQL Server 2008(10.0.x) SP 1 이상 버전 및 Azure SQL Database(일부 지역에서 미리 보기).

CLR 함수에 대한 테이블 데이터 형식을 정의합니다. 테이블 선언에는 열 이름과 데이터 형식만 포함됩니다. 테이블은 항상 주 파일 그룹에 포함됩니다.

NULL | NOT NULL

고유하게 컴파일된 스칼라 사용자 정의 함수에 대해서만 지원됩니다. 자세한 내용은 메모리 내 OLTP에 대한 사용자 정의 스칼라 함수를 참조하세요.

NATIVE_COMPILATION

사용자 정의 함수가 고유하게 컴파일되어 있는지 여부를 나타냅니다. 이 인수는 고유하게 컴파일된, 스칼라 사용자 정의 함수에 필요합니다.

BEGIN ATOMIC WITH

고유하게 컴파일된 스칼라 사용자 정의 함수에 대해 필수이며 지원됩니다. 자세한 내용은 네이티브 프로시저의 Atomic 블록을 참조 하세요.

SCHEMABINDING

인수 SCHEMABINDING 는 고유하게 컴파일된 스칼라 사용자 정의 함수에 필요합니다.

EXECUTE AS

EXECUTE AS 는 고유하게 컴파일된 스칼라 사용자 정의 함수에 필요합니다.

<> function_option ::= 및 <clr_function_option> ::=

함수에 다음 옵션 중 하나 이상이 있음을 지정합니다.

ENCRYPTION

적용 대상: SQL Server 2008(10.0.x) SP 1 이상 버전.

데이터베이스 엔진 문의 원래 텍스트를 CREATE FUNCTION 난독 분석된 형식으로 변환한다는 것을 나타냅니다. 난독 처리의 출력은 카탈로그 뷰에 직접 표시되지 않습니다. 시스템 테이블 또는 데이터베이스 파일에 액세스할 수 없는 사용자는 난독 제거된 텍스트를 검색할 수 없습니다. 그러나 데이터베이스 관리자에 대한 진단 연결을 통해 시스템 테이블에 액세스하거나 데이터베이스 파일에 직접 액세스할 수 있는 권한 있는 사용자가 텍스트를 사용할 수 있습니다 . 또한 디버거를 서버 프로세스에 연결할 수 있는 사용자는 런타임에 메모리에서 원래 프로시저를 검색할 수 있습니다. 시스템 메타데이터에 액세스하는 방법에 대한 자세한 내용은 메타데이터 표시 유형 구성을 참조하세요.

이 옵션을 사용하면 함수가 SQL Server 복제의 일부로 게시되는 것을 방지할 수 있습니다. CLR 함수에는 이 옵션을 지정할 수 없습니다.

SCHEMABINDING

함수를 함수가 참조하는 데이터베이스 개체에 바인딩되도록 지정합니다. 지정된 경우 SCHEMABINDING 함수 정의에 영향을 주는 방식으로 기본 개체를 수정할 수 없습니다. 함수 정의 자체를 먼저 수정하거나 삭제하여 수정할 개체에 대한 종속성을 제거해야 합니다.

다음 작업 중 하나가 발생한 경우에만 참조하는 개체에 대한 함수 바인딩이 제거됩니다.

  • 함수가 삭제된 경우
  • SCHEMABINDING 옵션을 지정하지 않은 상태에서 ALTER 문을 사용하여 함수를 수정한 경우

다음 조건을 만족하는 경우에만 함수가 스키마에 바인딩될 수 있습니다.

  • 함수가 Transact-SQL 함수입니다.
  • 사용자 정의 함수와 함수가 참조하는 뷰도 스키마에 바인딩되어야 합니다.
  • 함수가 참조하는 개체가 두 부분으로 구성된 이름을 사용하여 참조되어야 합니다.
  • 함수와 함수가 참조하는 개체가 동일한 데이터베이스에 속해야 합니다.
  • CREATE FUNCTION 문을 실행한 사용자는 해당 함수가 참조하는 데이터베이스 개체에 대한 REFERENCES 권한이 있어야 합니다.

RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT

스칼라 함수의 특성을 지정합니다 OnNULLCall . 지정 CALLED ON NULL INPUT 하지 않으면 기본적으로 암시됩니다. 즉, 함수 본문은 인수로 전달되더라도 NULL 실행됩니다.

CLR 함수에 지정된 경우 RETURNS NULL ON NULL INPUT 실제로 함수 본문을 호출하지 않고 수신NULL하는 인수가 있을 때 SQL Server가 반환 NULL 할 수 있음을 나타냅니다. 지정된 CLR 함수의 메서드에 <method_specifier> 이미 사용자 지정 특성을 나타내는 RETURNS NULL ON NULL INPUT사용자 지정 특성이 있지만 CREATE FUNCTION 문이 나타내는 CALLED ON NULL INPUTCREATE FUNCTION 경우 문이 우선합니다. OnNULLCall CLR 테이블 반환 함수에 대해 특성을 지정할 수 없습니다.

EXECUTE AS

사용자 정의 함수가 실행되는 보안 컨텍스트를 지정합니다. 따라서 SQL Server에서 함수가 참조하는 데이터베이스 개체에 대한 사용 권한을 확인하는 데 사용할 사용자 계정을 제어할 수 있습니다.

EXECUTE AS 는 인라인 테이블 반환 함수에 대해 지정할 수 없습니다.

자세한 내용은 EXECUTE AS 절(Transact-SQL)을 참조하세요.

INLINE = { ON | OFF }

적용 대상: SQL Server 2019(15.x) 이상 버전 및 Azure SQL Database.

이 스칼라 UDF를 인라인해야 하는지 여부를 지정합니다. 이 절은 스칼라 사용자 정의 함수에만 적용됩니다. INLINE 절은 필수 항목이 아닙니다. 절을 INLINE 지정하지 않으면 UDF를 인라인으로 설정할 수 있는지 여부에 따라 자동으로 설정 ON OFF 됩니다. 지정되었지만 UDF가 인라인화할 수 없는 것으로 확인되면 INLINE = ON 오류가 throw됩니다. 자세한 내용은 스칼라 UDF 인라인 처리를 참조하세요.

<> column_definition ::=

테이블 데이터 형식을 정의합니다. 테이블 선언에는 열 정의와 제약 조건이 포함됩니다. CLR 함수의 경우 column_name 및 data_type만 지정할 수 있습니다.

column_name

테이블의 열 이름입니다. 열 이름은 식별자에 대한 규칙을 따라야 하며 테이블에서 고유해야 합니다. column_name은 1~128자로 구성될 수 있습니다.

data_type

열 데이터 형식을 지정합니다. Transact-SQL 함수의 경우 timestamp를 제외하고 CLR 사용자 정의 형식을 비롯한 모든 데이터 형식이 허용됩니다. CLR 함수의 경우 텍스트, ntext, image, char, varchar, varchar(max) 및 타임스탬프를 제외한 CLR 사용자 정의 형식을 포함한 모든 데이터 형식이 허용됩니다. Transact-SQL 또는 CLR 함수에서 비칼라 형식 커서를 열 데이터 형식으로 지정할 수 없습니다.

DEFAULT constant_expression

삽입 중에 값이 명시적으로 지정되지 않은 경우에 열에 대해 제공되는 값을 지정합니다. constant_expression 상수 NULL또는 시스템 함수 값입니다. DEFAULT 정의는 속성이 있는 열을 제외한 모든 열에 IDENTITY 적용할 수 있습니다. DEFAULT 는 CLR 테이블 반환 함수에 대해 지정할 수 없습니다.

COLLATE collation_name

열에 대한 데이터 정렬을 지정합니다. 이를 지정하지 않으면 열에 데이터베이스의 기본 데이터 정렬이 할당됩니다. 데이터 정렬 이름으로는 Windows 데이터 정렬 이름 또는 SQL 데이터 정렬 이름을 사용할 수 있습니다. 데이터 정렬 목록 및 데이터 정렬에 대한 자세한 내용은 Windows 데이터 정렬 이름(Transact-SQL) 및 SQL Server 데이터 정렬 이름(Transact-SQL)을 참조하세요.

이 절은 COLLATE char, varchar, nchar 및 nvarchar 데이터 형식의 열에 대한 데이터 정렬만 변경하는 데 사용할 수 있습니다. COLLATE 는 CLR 테이블 반환 함수에 대해 지정할 수 없습니다.

ROWGUIDCOL

새 열이 행 GUID(Globally Unique Identifier) 열임을 나타냅니다. 테이블당 하나의 uniqueidentifier 열만 열로 ROWGUIDCOL 지정할 수 있습니다. 이 속성은 ROWGUIDCOL uniqueidentifier 열에만 할당할 수 있습니다.

이 속성은 ROWGUIDCOL 열에 저장된 값의 고유성을 적용하지 않습니다. 또한 테이블에 삽입된 새 행에 대한 값을 자동으로 생성하지 않습니다. 각 열에 대해 고유한 값을 생성하려면 문에 함수를 NEWID INSERT 사용합니다. 기본값을 지정할 수 있습니다. 그러나 NEWID 기본값으로 지정할 수는 없습니다.

IDENTITY

새 열이 ID 열임을 나타냅니다. 테이블에 새 행이 추가되면 SQL Server는 열에 대해 고유한 증가값을 제공합니다. ID 열은 일반적으로 제약 조건과 PRIMARY KEY 함께 테이블의 고유한 행 식별자로 사용됩니다. IDENTITY 속성은 tinyint, smallint, int, bigint, decimal(p,0) 또는 numeric(p,0) 열에 할당할 수 있습니다. ID 열은 테이블당 하나만 만들 수 있습니다. 바인딩된 기본값 및 DEFAULT 제약 조건은 ID 열과 함께 사용할 수 없습니다. 초기값과 증가값을 모두 지정하거나 모두 지정하지 않아야 합니다. 둘 다 지정하지 않은 경우에는 기본값 (1,1)이 사용됩니다.

IDENTITY 는 CLR 테이블 반환 함수에 대해 지정할 수 없습니다.

seed

테이블의 첫 번째 행에 할당할 정수 값입니다.

increment

테이블의 연속 행에 대한 시드 값에 추가할 정수 값입니다.

<> column_constraint ::= 및 <table_constraint> ::=

지정된 열 또는 테이블에 대한 제약 조건을 정의합니다. CLR 함수의 경우 허용되는 유일한 제약 조건 유형은 .입니다 NULL. 명명된 제약 조건은 허용되지 않습니다.

NULL | NOT NULL

열의 Null 값 허용 여부를 결정합니다. NULL 은 엄격하게 제약 조건이 아니지만 다음과 같이 NOT NULL지정할 수 있습니다. NOT NULL 는 CLR 테이블 반환 함수에 대해 지정할 수 없습니다.

PRIMARY KEY

고유 인덱스를 통해 지정된 열에 대한 엔터티 무결성을 적용하는 제약 조건입니다. 테이블 반환 사용자 정의 함수 PRIMARY KEY 에서는 테이블당 하나의 열에만 제약 조건을 만들 수 있습니다. PRIMARY KEY 는 CLR 테이블 반환 함수에 대해 지정할 수 없습니다.

UNIQUE

지정한 열에 대해 고유 인덱스를 통해 엔터티 무결성을 적용하는 제약 조건입니다. 테이블에는 여러 UNIQUE 제약 조건이 있을 수 있습니다. UNIQUE 는 CLR 테이블 반환 함수에 대해 지정할 수 없습니다.

CLUSTERED | NONCLUSTERED

또는 UNIQUE 제약 조건에 대해 클러스터형 또는 비클러스터형 인덱스가 PRIMARY KEY 생성되었음을 나타냅니다. PRIMARY KEY 제약 조건은 사용 CLUSTERED하며 UNIQUE 제약 조건은 .를 사용합니다 NONCLUSTERED.

CLUSTERED 는 하나의 제약 조건에 대해서만 지정할 수 있습니다. 제약 조건에 대해 UNIQUE 지정되고 PRIMARY KEY 제약 조건도 지정된 PRIMARY KEY NONCLUSTERED경우 CLUSTERED .

CLUSTERED CLR NONCLUSTERED 테이블 반환 함수에 대해 지정할 수 없습니다.

CHECK

열에 입력 가능한 값을 제한하여 도메인 무결성을 적용하는 제약 조건입니다. CHECK CLR 테이블 반환 함수에는 제약 조건을 지정할 수 없습니다.

logical_expression

반환하거나 FALSE.를 반환 TRUE 하는 논리 식입니다.

<> computed_column_definition ::=

계산 열을 지정합니다. 계산 열에 대한 자세한 내용은 CREATE TABLE(Transact-SQL)을 참조하세요.

column_name

계산 열의 이름입니다.

computed_column_expression

계산 열의 값을 정의하는 식입니다.

<index_option> ::=

또는 UNIQUE 인덱스에 대한 인덱스 PRIMARY KEY 옵션을 지정합니다. 인덱스 옵션에 대한 자세한 내용은 CREATE INDEX(Transact-SQL)를 참조하세요.

PAD_INDEX = { ON | OFF }

인덱스 패딩을 지정합니다. 기본값은 OFF입니다.

FILLFACTOR = fillfactor

데이터베이스 엔진에서 인덱스 생성 또는 변경 중에 각 인덱스 페이지의 리프 수준에 대한 채우기 정도를 나타내는 비율을 지정합니다. fillfactor는 1에서 100 사이의 정수 값이어야 하며 기본값은 0입니다.

IGNORE_DUP_KEY = { ON | OFF }

삽입 작업에서 고유 인덱스에 중복된 키 값을 삽입하려는 경우에 대한 오류 응답을 지정합니다. IGNORE_DUP_KEY 옵션은 인덱스를 만들거나 다시 빌드한 후의 삽입 작업에만 적용됩니다. 기본값은 OFF입니다.

STATISTICS_NORECOMPUTE = { ON | OFF}

배포 통계를 다시 계산할지 여부를 지정합니다. 기본값은 OFF입니다.

ALLOW_ROW_LOCKS = { ON | OFF }

행 잠금의 허용 여부를 지정합니다. 기본값은 ON입니다.

ALLOW_PAGE_LOCKS = { ON | OFF }

페이지 잠금의 허용 여부를 지정합니다. 기본값은 ON입니다.

모범 사례

절을 사용하여 사용자 정의 함수를 만들지 SCHEMABINDING 않으면 기본 개체에 대한 변경 내용이 함수의 정의에 영향을 미치고 호출될 때 예기치 않은 결과를 생성할 수 있습니다. 기본 개체에 대한 변경으로 인해 함수가 최신 상태를 유지하지 못하게 되는 일이 발생하지 않도록 다음 메서드 중 하나를 구현하는 것이 좋습니다.

  • 함수를 WITH SCHEMABINDING 만들 때 절을 지정합니다. 이 옵션은 함수도 수정하지 않는 한 함수 정의에서 참조되는 개체를 수정할 수 없도록 합니다.

  • 함수 정의에 지정된 개체를 수정한 후 sp_refreshsqlmodule 저장 프로시저를 실행합니다.

인라인 테이블 반환 함수(인라인 TVF) 및 MSTVF(다중 문 테이블 반환 함수)에 대한 자세한 내용 및 성능 고려 사항은 사용자 정의 함수 만들기(데이터베이스 엔진)를 참조하세요.

데이터 유형

CLR 함수에 매개 변수를 지정할 경우 매개 변수는 이전에 scalar_parameter_data_type에 정의된 SQL Server 형식이어야 합니다. SQL Server 시스템 데이터 형식과 CLR 통합 데이터 형식 또는 .NET Framework 공용 언어 런타임 데이터 형식을 비교하는 자세한 내용은 CLR 매개 변수 데이터 매핑을 참조 하세요.

SQL Server가 클래스에서 오버로드될 때 올바른 메서드를 참조하려면 표시된 <method_specifier> 메서드에 다음과 같은 특성이 있어야 합니다.

  • 에 지정된 것과 동일한 수의 매개 변수를 받습니다 [ , ...n ].
  • 참조가 아닌 값을 기준으로 모든 매개 변수를 받습니다.
  • SQL Server 함수에 지정된 형식과 호환되는 매개 변수 형식을 사용합니다.

CLR 함수의 반환 데이터 형식이 테이블 형식(RETURNS TABLE)을 지정하는 경우 메서드의 <method_specifier> 반환 데이터 형식은 형식 IEnumerator 이거나 IEnumerable함수 작성자가 인터페이스를 구현한다고 가정합니다. Transact-SQL 함수와 달리 CLR 함수는 제약 조건을 포함PRIMARY KEYUNIQUE할 CHECK <table_type_definition>수 없습니다. 지정된 <table_type_definition> 열의 데이터 형식은 실행 시 메서드 <method_specifier> 가 반환한 결과 집합의 해당 열 형식과 일치해야 합니다. 이 형식 검사는 함수를 만들 때 수행되지 않습니다.

CLR 함수 프로그래밍 방법은 CLR 사용자 정의 함수를 참조하세요.

설명

스칼라 함수는 계산 열 및 CHECK 제약 조건 정의를 포함하는 스칼라 식이 사용되는 위치에서 호출할 수 있습니다. EXECUTE(Transact-SQL) 문을 사용하여 스칼라 함수를 실행할 수도 있습니다. 스칼라 함수는 적어도 두 부분으로 구성된 함수 이름(<schema>.<function>)을 사용하여 호출해야 합니다. 다중 부분 이름에 대한 자세한 내용은 Transact-SQL 구문 표기 규칙(Transact-SQL)을 참조하세요. 테이블 반환 함수는 SELECT, INSERT, UPDATE 또는 DELETE 문 FROM 절의 테이블 식이 허용되는 위치에서 호출할 수 있습니다. 자세한 내용은 사용자 정의 함수 실행을 참조 하세요.

상호 운용성

함수에서 유효한 문은 다음과 같습니다.

  • 대입 문
  • TRY...CATCH 문을 제외한 흐름 제어 명령문
  • 로컬 데이터 변수 및 로컬 커서를 정의하는 DECLARE 문
  • 지역 변수에 값을 할당하는 식이 있는 SELECT 목록이 포함된 SELECT 문
  • 함수에서 커서 선언, 열기, 닫기, 할당 취소 등 로컬 커서를 참조하는 커서 작업. 절을 사용하여 INTO 지역 변수에 값을 할당하는 문만 FETCH 허용 FETCH 됩니다. 클라이언트에 데이터를 반환하는 문은 허용되지 않습니다.
  • 지역 테이블 변수를 수정하는 INSERT, UPDATE 및 DELETE 문
  • 확장 저장 프로시저를 호출하는 EXECUTE 문

자세한 내용은 사용자 정의 함수 만들기(데이터베이스 엔진)를 참조하세요.

계산 열 상호 운용성

함수는 다음 속성을 가집니다. 이러한 속성의 값은 지속 또는 인덱싱할 수 있는 계산 열에 함수를 사용할 수 있는지 여부를 결정합니다.

속성 설명 참고
IsDeterministic 함수가 결정적 또는 비결정적입니다. 결정적 함수에서는 로컬 데이터 액세스가 허용됩니다. 예를 들어 특정 입력 값 집합을 사용하여 호출될 때마다 항상 동일한 결과를 반환하는 함수와 데이터베이스의 상태가 같은 함수는 결정적 레이블이 지정됩니다.
IsPrecise 함수는 정확하거나 정확하지 않습니다. 정확하지 않은 함수에는 부동 소수점 연산과 같은 연산이 포함됩니다.
IsSystemVerified SQL Server에서 함수의 정확성 및 결정성 속성을 확인할 수 있습니다.
SystemDataAccess 함수는 SQL Server의 로컬 인스턴스에서 시스템 데이터, 시스템 카탈로그 또는 가상 시스템 테이블에 액세스합니다.
UserDataAccess 함수가 SQL Server 로컬 인스턴스의 사용자 데이터에 액세스합니다. 사용자 정의 테이블 및 임시 테이블은 포함되고 테이블 변수는 포함되지 않습니다.

Transact-SQL 함수의 정확성 및 결정성 속성은 SQL Server에서 자동으로 결정됩니다. CLR 함수의 데이터 액세스 및 결정성 속성은 사용자가 지정할 수 있습니다. 자세한 내용은 CLR 통합: CLR 루틴에 대한 사용자 지정 특성을 참조 하세요.

이러한 속성에 대한 현재 값을 표시하려면 OBJECTPROPERTYEX(Transact-SQL)를 사용합니다.

Important

함수가 결정적이려면 SCHEMABINDING으로 생성해야 합니다.

사용자 정의 함수를 호출하는 계산 열은 사용자 정의 함수가 다음 속성 값을 가질 경우 인덱스에 사용할 수 있습니다.

  • IsDeterministic가 true인 경우
  • IsSystemVerified 은 true (계산 열이 유지되지 않는 한)
  • UserDataAccess가 false인 경우
  • SystemDataAccess가 false인 경우

자세한 내용은 계산된 열의 인덱스를 참조하세요.

함수에서 확장 저장 프로시저 호출

확장 저장 프로시저는 함수 내부에서 호출할 때 결과 집합을 클라이언트로 반환할 수 없습니다. 결과 집합을 클라이언트에 반환하는 모든 ODS API는 반환합니다 FAIL. 확장 저장 프로시저는 SQL Server 인스턴스에 다시 연결할 수 있습니다. 그러나 확장 저장 프로시저를 호출한 함수와 동일한 트랜잭션을 조인해서는 안 됩니다.

일괄 처리 또는 저장 프로시저의 호출과 마찬가지로 확장 저장 프로시저는 SQL Server가 실행 중인 Windows 보안 계정의 컨텍스트에서 실행됩니다. 저장 프로시저의 소유자는 사용자에게 권한을 부여 EXECUTE 할 때 이 시나리오를 고려해야 합니다.

제한 사항

사용자 정의 함수는 데이터베이스 상태 수정 동작을 수행하는 데 사용할 수 없습니다.

사용자 정의 함수에는 테이블이 대상인 OUTPUT INTO 절을 포함할 수 없습니다.

다음 Service Broker 문은 Transact-SQL 사용자 정의 함수의 정의에 포함될 수 없습니다.

  • BEGIN DIALOG CONVERSATION
  • END CONVERSATION
  • GET CONVERSATION GROUP
  • MOVE CONVERSATION
  • RECEIVE
  • SEND

사용자 정의 함수는 중첩될 수 있습니다. 즉, 하나의 사용자 정의 함수가 다른 사용자 정의 함수를 호출할 수 있습니다. 중첩 수준은 호출된 함수의 실행이 시작되면 늘어나고 호출된 함수의 실행이 끝나면 줄어듭니다. 사용자 정의 함수는 최대 32 수준까지 중첩될 수 있습니다. 최대 중첩 수준을 초과하면 전체 함수 호출 체인이 실패합니다. Transact-SQL 사용자 정의 함수의 관리 코드 참조는 32 수준의 중첩 제한에 대해 한 수준으로 계산됩니다. 관리 코드 내에서 호출된 메서드는 이 제한에 따라 계산되지 않습니다.

CLR 테이블 반환 함수에서 정렬 순서 사용

CLR 테이블 반환 함수에서 ORDER 절을 사용할 때는 다음 지침을 따르세요.

  • 결과가 항상 지정된 순서로 정렬되도록 해야 합니다. 결과가 지정된 순서가 아니면 쿼리가 실행될 때 SQL Server에서 오류 메시지를 생성합니다.

  • ORDER 절이 지정된 경우 테이블 반환 함수의 출력은 열의 데이터 정렬(명시적 또는 암시적)에 따라 정렬되어야 합니다. 예를 들어 열 데이터 정렬이 중국어인 경우 반환된 결과는 중국어 정렬 규칙에 따라 정렬되어야 합니다. (데이터 정렬은 테이블 반환 함수의 DDL에서 지정되거나 데이터베이스 데이터 정렬에서 가져옵니다.)

  • SQL Server는 쿼리 프로세서가 ORDER 이 절을 사용하여 추가 최적화를 수행하는지 여부를 반환하는 동안 지정된 경우 항상 절을 확인합니다. 쿼리 프로세서에 ORDER 유용하다는 것을 알고 있는 경우에만 절을 사용합니다.

  • SQL Server 쿼리 프로세서는 다음과 같은 경우에 ORDER 절을 자동으로 활용합니다.

    • ORDER 절이 인덱스와 호환되는 삽입 쿼리
    • ORDER 절과 호환되는 ORDER BY 절
    • 집계, 여기서 GROUP BY는 ORDER 절과 호환됩니다.
    • 고유 열이 ORDER 절과 호환되는 DISTINCT 집계

이 절은 ORDER 쿼리에 지정되지 않는 한 ORDER BY 쿼리가 SELECT 실행될 때 순서가 지정된 결과를 보장하지 않습니다. 테이블 반환 함수의 정렬 순서에 포함된 열을 쿼리하는 방법은 sys.function_order_columns(Transact-SQL)를 참조하세요.

메타데이터

다음 표에서는 사용자 정의 함수에 대한 메타데이터를 반환하는 데 사용할 수 있는 시스템 카탈로그 뷰를 나열합니다.

시스템 뷰 Description
sys.sql_modules 예제 섹션에서 E 예제를 참조하세요.
sys.assembly_modules CLR 사용자 정의 함수에 대한 정보를 표시합니다.
sys.parameters 사용자 정의 함수에 정의된 매개 변수에 대한 정보를 표시합니다.
sys.sql_expression_dependencies 함수에서 참조하는 기본 개체를 표시합니다.

사용 권한

데이터베이스에 대한 CREATE FUNCTION 권한과 함수가 생성되는 스키마에 대한 ALTER 권한이 필요합니다. 함수가 사용자 정의 형식을 지정하면 해당 유형에 대한 EXECUTE 권한이 필요합니다.

예제

UDF에 대한 자세한 예제 및 성능 고려 사항은 사용자 정의 함수 만들기(데이터베이스 엔진)를 참조하세요.

A. ISO 주를 계산하는 스칼라 반환 사용자 정의 함수 사용

다음 예에서는 사용자 정의 함수 ISOweek를 만듭니다. 이 함수는 날짜 인수를 사용하여 ISO 주 번호를 계산합니다. 이 함수가 계산을 제대로 수행하기 위해서는 함수를 호출하기 전에 SET DATEFIRST 1을 호출해야 합니다.

또한 이 예제에서는 EXECUTE AS 절(Transact-SQL) 절을 사용하여 저장 프로시저를 실행할 수 있는 보안 컨텍스트를 지정하는 방법을 보여 줍니다. 이 예제에서 옵션은 CALLER 프로시저를 호출하는 사용자의 컨텍스트에서 프로시저가 실행되도록 지정합니다. 지정할 수 있는 다른 옵션은 SELF, OWNER 및 user_name입니다.

함수 호출은 다음과 같습니다. DATEFIRST이 1로 설정됩니다.

CREATE FUNCTION dbo.ISOweek (@DATE DATETIME)
RETURNS INT
WITH EXECUTE AS CALLER
AS
BEGIN
    DECLARE @ISOweek INT;

    SET @ISOweek = DATEPART(wk, @DATE) + 1 -
        DATEPART(wk, CAST(DATEPART(yy, @DATE) AS CHAR(4)) + '0104');

    --Special cases: Jan 1-3 may belong to the previous year
    IF (@ISOweek = 0)
        SET @ISOweek = dbo.ISOweek(CAST(DATEPART(yy, @DATE) - 1 AS CHAR(4))
           + '12' + CAST(24 + DATEPART(DAY, @DATE) AS CHAR(2))) + 1;

    --Special case: Dec 29-31 may belong to the next year
    IF ((DATEPART(mm, @DATE) = 12)
        AND ((DATEPART(dd, @DATE) - DATEPART(dw, @DATE)) >= 28))
    SET @ISOweek = 1;

    RETURN (@ISOweek);
END;
GO

SET DATEFIRST 1;

SELECT dbo.ISOweek(CONVERT(DATETIME, '12/26/2004', 101)) AS 'ISO Week';

결과 집합은 다음과 같습니다.

ISO Week
----------------
52

B. 인라인 테이블 반환 함수 만들기

다음 예제에서는 AdventureWorks2022 데이터베이스에서 인라인 테이블 반환 함수를 반환합니다. ProductID 열, Name 열, 그리고 대리점에 판매된 각 제품에 대한 대리점별 총 연간 매출의 집계를 YTD Total 열로 반환합니다.

CREATE FUNCTION Sales.ufn_SalesByStore (@storeid INT)
RETURNS TABLE
AS
RETURN (
    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'
    FROM Production.Product AS P
    INNER JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
    INNER JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
    INNER JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID
    WHERE C.StoreID = @storeid
    GROUP BY P.ProductID, P.Name
);
GO

함수를 호출하려면 이 쿼리를 실행하세요.

SELECT * FROM Sales.ufn_SalesByStore (602);

C. 다중 문 테이블 반환 함수 만들기

다음 예제에서는 데이터베이스에 테이블 반환 함수 fn_FindReports(InEmpID) 를 AdventureWorks2022 만듭니다. 이 함수에 유효한 직원 ID를 제공하면 해당 직원에게 보고하는 모든 직속 부하 직원 및 관련 부서 직원에 해당하는 테이블이 반환됩니다. 이 함수는 재귀 CTE(공통 테이블 식)를 사용하여 직원의 계층적 목록을 생성합니다. 재귀 CTE에 대한 자세한 내용은 WITH common_table_expression(Transact-SQL)을 참조하세요.

CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INT)
RETURNS @retFindReports TABLE (
    EmployeeID INT PRIMARY KEY NOT NULL,
    FirstName NVARCHAR(255) NOT NULL,
    LastName NVARCHAR(255) NOT NULL,
    JobTitle NVARCHAR(50) NOT NULL,
    RecursionLevel INT NOT NULL
    )
    --Returns a result set that lists all the employees who report to the
    --specific employee directly or indirectly.
AS
BEGIN
    WITH EMP_cte (
        EmployeeID,
        OrganizationNode,
        FirstName,
        LastName,
        JobTitle,
        RecursionLevel
        ) -- CTE name and columns
    AS (
        -- Get the initial list of Employees for Manager n
        SELECT e.BusinessEntityID,
            OrganizationNode = ISNULL(e.OrganizationNode, CAST('/' AS HIERARCHYID)),
            p.FirstName,
            p.LastName,
            e.JobTitle,
            0
        FROM HumanResources.Employee e
        INNER JOIN Person.Person p
            ON p.BusinessEntityID = e.BusinessEntityID
        WHERE e.BusinessEntityID = @InEmpID
        
        UNION ALL
        
        -- Join recursive member to anchor
        SELECT e.BusinessEntityID,
            e.OrganizationNode,
            p.FirstName,
            p.LastName,
            e.JobTitle,
            RecursionLevel + 1
        FROM HumanResources.Employee e
        INNER JOIN EMP_cte
            ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode
        INNER JOIN Person.Person p
            ON p.BusinessEntityID = e.BusinessEntityID
        )
    -- Copy the required columns to the result of the function
    INSERT @retFindReports
    SELECT EmployeeID,
        FirstName,
        LastName,
        JobTitle,
        RecursionLevel
    FROM EMP_cte

    RETURN
END;
GO

-- Example invocation
SELECT EmployeeID,
    FirstName,
    LastName,
    JobTitle,
    RecursionLevel
FROM dbo.ufn_FindReports(1);
GO

D. CLR 함수 만들기

이 예에서는 CLR 함수 len_s를 만듭니다. 함수를 만들기 전에 SurrogateStringFunction.dll 어셈블리가 로컬 데이터베이스에 등록됩니다.

적용 대상: SQL Server 2008(10.0.x) SP 1 이상 버전.

DECLARE @SamplesPath NVARCHAR(1024);

-- You may have to modify the value of this variable if you have
-- installed the sample in a location other than the default location.
SELECT @SamplesPath = REPLACE(physical_name,
    'Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\master.mdf',
    'Microsoft SQL Server\130\Samples\Engine\Programmability\CLR\'
)
FROM master.sys.database_files
WHERE name = 'master';

CREATE ASSEMBLY [SurrogateStringFunction]
FROM @SamplesPath + 'StringManipulate\CS\StringManipulate\bin\debug\SurrogateStringFunction.dll'
    WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

CREATE FUNCTION [dbo].[len_s] (@str NVARCHAR(4000))
RETURNS BIGINT
AS
EXTERNAL NAME [SurrogateStringFunction].[Microsoft.Samples.SqlServer.SurrogateStringFunction].[LenS];
GO

CLR 테이블 반환 함수를 만드는 방법에 대한 예는 CLR 테이블 반환 함수를 참조하세요.

E. 사용자 정의 함수의 정의 표시

SELECT DEFINITION,
    type
FROM sys.sql_modules AS m
INNER JOIN sys.objects AS o
    ON m.object_id = o.object_id
    AND type IN ('FN', 'IF', 'TF');
GO

옵션을 사용하여 ENCRYPTION 만든 함수의 정의는 사용하여 sys.sql_modules볼 수 없습니다. 그러나 암호화된 함수에 대한 다른 정보가 표시됩니다.

관련 콘텐츠