ALTER FUNCTION (Transact-SQL)ALTER FUNCTION (Transact-SQL)

適用於: 是SQL Server 是Azure SQL Database 否Azure Synapse Analytics (SQL DW) 是平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

改變先前執行 CREATE FUNCTION 陳述式所建立的現有 Transact-SQLTransact-SQL 或 CLR 函數,而不變更權限及不影響任何相依的函數、預存程序或觸發程序。Alters an existing Transact-SQLTransact-SQL or CLR function that was previously created by executing the CREATE FUNCTION statement, without changing permissions and without affecting any dependent functions, stored procedures, or triggers.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

-- Transact-SQL Scalar Function Syntax    
ALTER FUNCTION [ schema_name. ] function_name   
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type   
    [ = default ] }   
    [ ,...n ]  
  ]  
)  
RETURNS return_data_type  
    [ WITH <function_option> [ ,...n ] ]  
    [ AS ]  
    BEGIN   
        function_body   
        RETURN scalar_expression  
    END  
[ ; ]
-- Transact-SQL Inline Table-Valued Function Syntax
ALTER FUNCTION [ schema_name. ] function_name   
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type   
    [ = default ] }   
    [ ,...n ]  
  ]  
)  
RETURNS TABLE  
    [ WITH <function_option> [ ,...n ] ]  
    [ AS ]  
    RETURN [ ( ] select_stmt [ ) ]  
[ ; ]  
-- Transact-SQL Multistatement Table-valued Function Syntax
ALTER FUNCTION [ schema_name. ] function_name   
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type   
    [ = default ] }   
    [ ,...n ]  
  ]  
)  
RETURNS @return_variable TABLE <table_type_definition>  
    [ WITH <function_option> [ ,...n ] ]  
    [ AS ]  
    BEGIN   
        function_body   
        RETURN  
    END  
[ ; ]  
-- Transact-SQL Function Clauses   
<function_option>::=   
{  
    [ ENCRYPTION ]  
  | [ SCHEMABINDING ]  
  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]  
  | [ EXECUTE_AS_Clause ]  
} 

<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 Scalar and Table-Valued Function Syntax
ALTER FUNCTION [ schema_name. ] function_name   
( { @parameter_name [AS] [ type_schema_name. ] parameter_data_type   
    [ = default ] }   
    [ ,...n ]  
)  
RETURNS { return_data_type | TABLE <clr_table_type_definition> }  
    [ WITH <clr_function_option> [ ,...n ] ]  
    [ AS ] EXTERNAL NAME <method_specifier>  
[ ; ]  
-- CLR Function Clauses
<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 ] )  
  
-- Syntax for In-Memory OLTP: Natively compiled, scalar user-defined function  
ALTER FUNCTION [ schema_name. ] function_name    
 ( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type   
    [ NULL | NOT NULL ] [ = default ] }   
    [ ,...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 ]   
}  

引數Arguments

schema_nameschema_name
這是使用者定義函數所屬的結構描述名稱。Is the name of the schema to which the user-defined function belongs.

function_namefunction_name
這是要變更的使用者定義函數。Is the user-defined function to be changed.

注意

即使沒有指定參數,函數名稱後面仍需要括號。Parentheses are required after the function name even if a parameter is not specified.

@ parameter_name@ parameter_name
這是使用者定義函數中的參數。Is a parameter in the user-defined function. 您可以宣告一個或多個參數。One or more parameters can be declared.

函數最多可以有 2,100 個參數。A function can have a maximum of 2,100 parameters. 除非定義了參數的預設值,否則在執行函數時,使用者必須提供每個已宣告之參數的值。The value of each declared parameter must be supplied by the user when the function is executed, unless a default for the parameter is defined.

使用 "at" 記號 ( @ ) 當作第一個字元來指定參數名稱。Specify a parameter name by using an at sign (@) as the first character. 參數名稱必須符合識別碼的規則。The parameter name must comply with the rules for identifiers. 對函數而言,參數必須是本機參數;相同的參數名稱可以用在其他函數中。Parameters are local to the function; the same parameter names can be used in other functions. 參數只能取代常數,不能用來取代資料表名稱、資料行名稱或其他資料庫物件的名稱。Parameters can take the place only of constants; they cannot be used instead of table names, column names, or the names of other database objects.

注意

當在預存程序或使用者自訂函數中傳遞參數時,或在批次陳述式中宣告和設定變數時,會忽略 ANSI_WARNINGS。ANSI_WARNINGS is not honored when passing parameters in a stored procedure, user-defined function, or when declaring and setting variables in a batch statement. 例如,如果將變數定義為 char(3) ,然後設定為大於三個字元的值,資料就會被截斷成定義的大小,而 INSERT 或 UPDATE 陳述式會執行成功。For example, if a variable is defined as char(3), and then set to a value larger than three characters, the data is truncated to the defined size and the INSERT or UPDATE statement succeeds.

[ type_schema_name.[ type_schema_name. ] parameter_data_type] parameter_data_type
這是參數資料類型,對於其所屬的結構描述而言為選擇性。Is the parameter data type and optionally, the schema to which it belongs. Transact-SQLTransact-SQL 函式而言,所有資料類型 (包括 CLR 使用者定義型別) 都是允許的資料類型,但 timestamp 資料類型除外。For Transact-SQLTransact-SQL functions, all data types, including CLR user-defined types, are allowed except the timestamp data type. 就 CLR 函式而言,所有資料類型 (包括 CLR 使用者定義型別) 都是允許的資料類型,但 textntextimagetimestamp 資料類型除外。For CLR functions, all data types, including CLR user-defined types, are allowed except text, ntext, image, and timestamp data types. 非純量類型 cursortable 不能指定為 Transact-SQLTransact-SQL 或 CLR 函式中的參數資料類型。The nonscalar types cursor and table cannot be specified as a parameter data type in either Transact-SQLTransact-SQL or CLR functions.

如果未指定 type_schema_nameSQL Server 2005 Database EngineSQL Server 2005 Database Engine 就會依照下列順序尋找 scalar_parameter_data_typeIf type_schema_name is not specified, the SQL Server 2005 Database EngineSQL Server 2005 Database Engine looks for the parameter_data_type in the following order:

  • 內含 SQL Server 系統資料類型的結構描述。The schema that contains the names of SQL Server system data types.

  • 目前資料庫中之目前使用者的預設結構描述。The default schema of the current user in the current database.

  • 目前資料庫中的 dbo 結構描述。The dbo schema in the current database.

[ = default ][ =default ]
這是參數的預設值。Is a default value for the parameter. 如果已定義 default 值,則不需為該參數指定值,即可執行函式。If a default value is defined, the function can be executed without specifying a value for that parameter.

注意

您可以針對 CLR 函式指定預設參數值,但 varchar(max)varbinary(max) 資料類型除外。Default parameter values can be specified for CLR functions except for varchar(max) and varbinary(max) data types.

如果函數的參數有預設值,則必須在呼叫函數來擷取該預設值時指定關鍵字 DEFAULT。When a parameter of the function has a default value, the keyword DEFAULT must be specified when calling the function to retrieve the default value. 這個行為與使用預存程序中具有預設值的參數不一樣,因為在預存程序中,省略參數也意味著使用預設值。This behavior is different from using parameters with default values in stored procedures in which omitting the parameter also implies the default value.

return_data_typereturn_data_type
這是純量使用者定義函數的傳回值。Is the return value of a scalar user-defined function. Transact-SQLTransact-SQL 函式而言,所有資料類型 (包括 CLR 使用者定義型別) 都是允許的資料類型,但 timestamp 資料類型除外。For Transact-SQLTransact-SQL functions, all data types, including CLR user-defined types, are allowed except the timestamp data type. 就 CLR 函式而言,所有資料類型 (包括 CLR 使用者定義型別) 都是允許的資料類型,但 textntextimagetimestamp 資料類型除外。For CLR functions, all data types, including CLR user-defined types, are allowed except text, ntext, image, and timestamp data types. 非純量類型 cursortable 不能指定為 Transact-SQLTransact-SQL 或 CLR 函式中的傳回資料類型。The nonscalar types cursor and table cannot be specified as a return data type in either Transact-SQLTransact-SQL or CLR functions.

function_bodyfunction_body
指定一系列 Transact-SQLTransact-SQL 陳述式 (並用這些陳述式並不會造成任何副作用,例如修改資料表等) 定義函數的值。Specifies that a series of Transact-SQLTransact-SQL statements, which together do not produce a side effect such as modifying a table, define the value of the function. function_body 僅用於純量函式和多重陳述式資料表值函式中。function_body is used only in scalar functions and multistatement table-valued functions.

在純量函式中,function_body 是一系列的 Transact-SQLTransact-SQL 陳述式,這些陳述式會一起評估為純量值。In scalar functions, function_body is a series of Transact-SQLTransact-SQL statements that together evaluate to a scalar value.

在多重陳述式資料表值函式中,function_body 是一系列的 Transact-SQLTransact-SQL 陳述式,這些陳述式會填入 TABLE 傳回變數。In multistatement table-valued functions, function_body is a series of Transact-SQLTransact-SQL statements that populate a TABLE return variable.

scalar_expressionscalar_expression
指定純量函數傳回純量值。Specifies that the scalar function returns a scalar value.

TABLETABLE
指定資料表值函式的傳回值是資料表。Specifies that the return value of the table-valued function is a table. 只有常數和 @ local_variables 才能傳遞給資料表值函式。Only constants and @local_variables can be passed to table-valued functions.

在內嵌資料表值函式中,TABLE 傳回值是利用單一 SELECT 陳述式所定義。In inline table-valued functions, the TABLE return value is defined through a single SELECT statement. 內嵌函數沒有相關聯的傳回變數。Inline functions do not have associated return variables.

在多重陳述式資料表值函式中, @ return_variable 是一個 TABLE 變數,可用來儲存及累積應作為函式值來傳回的資料列。In multistatement table-valued functions, @return_variable is a TABLE variable used to store and accumulate the rows that should be returned as the value of the function. @ return_variable 只能指定給 Transact-SQLTransact-SQL 函式,但不能指定給 CLR 函式。@return_variable can be specified only for Transact-SQLTransact-SQL functions and not for CLR functions.

select-stmtselect-stmt
這是單一 SELECT 陳述式,可定義嵌入資料表值函式的傳回值。Is the single SELECT statement that defines the return value of an inline table-valued function.

EXTERNAL NAME <method_specifier>assembly_name.class_name.method_nameEXTERNAL NAME <method_specifier>assembly_name.class_name.method_name
適用對象SQL Server 2008SQL Server 2008 及更新版本。Applies to: SQL Server 2008SQL Server 2008 and later.

指定繫結函數之組件的方法。Specifies the method of an assembly to bind with the function. assembly_name 必須符合目前所顯示之資料庫的 SQL ServerSQL Server 的現有組件。assembly_name must match an existing assembly in SQL ServerSQL Server in the current database with visibility on. class_name 必須是有效的 SQL ServerSQL Server 識別碼,且必須是組件中的類別。class_name must be a valid SQL ServerSQL Server identifier and must exist as a class in the assembly. 如果該類別具有命名空間限定的名稱,且該名稱使用句號 ( . ) 來分隔命名空間的各個部分,您就必須使用方括弧 ( [] ) 或引號 ( "" ) 來分隔類別名稱。If the class has a namespace-qualified name that uses a period (.) to separate namespace parts, the class name must be delimited by using brackets ([]) or quotation marks (""). method_name 必須是有效的 SQL ServerSQL Server 識別碼,且必須是指定類別中的靜態方法。method_name must be a valid SQL ServerSQL Server identifier and must exist as a static method in the specified class.

注意

依預設,SQL ServerSQL Server 不能執行 CLR 程式碼。By default, SQL ServerSQL Server cannot execute CLR code. 您可以建立、修改和卸除參考通用語言執行平台模組的資料庫物件;不過,必須等到您啟用 clr enabled 選項之後,才能在 SQL ServerSQL Server 中執行這些參考。You can create, modify, and drop database objects that reference common language runtime modules; however, you cannot execute these references in SQL ServerSQL Server until you enable the clr enabled option. 若要啟用這個選項,請使用 sp_configureTo enable the option, use sp_configure.

注意

自主資料庫無法使用這個選項。This option is not available in a contained database.

<table_type_definition> ( { <column_definition> <column_constraint> | <computed_column_definition> } [ <table_constraint> ] [ , ...n ] )<table_type_definition>( { <column_definition> <column_constraint> | <computed_column_definition> } [ <table_constraint> ] [ ,...n ])
定義 Transact-SQLTransact-SQL 函數的資料表資料類型。Defines the table data type for a Transact-SQLTransact-SQL function. 資料表宣告包括資料行定義和資料行或資料表條件約束。The table declaration includes column definitions and column or table constraints.

< clr_table_type_definition > ( { column_name**data_type } [ , ...n ] ) 適用於SQL Server 2008SQL Server 2008 及更新版本、SQL DatabaseSQL Database (在某些區域中為預覽版本)。< clr_table_type_definition > ( { column_name**data_type } [ ,...n ] ) Applies to: SQL Server 2008SQL Server 2008 and later, SQL DatabaseSQL Database (Preview in some regions).

定義 CLR 函數的資料表資料類型。Defines the table data types for a CLR function. 資料表宣告只包含資料行名稱和資料類型。The table declaration includes only column names and data types.

NULL|NOT NULLNULL|NOT NULL
只有針對原生編譯的純量使用者定義函式才支援。Supported only for natively compiled, scalar user-defined functions. 如需詳細資訊,請參閱記憶體內部 OLTP 的純量使用者定義函數For more information, see Scalar User-Defined Functions for In-Memory OLTP.

NATIVE_COMPILATIONNATIVE_COMPILATION
指出使用者定義函式是否為原生編譯函式。Indicates whether a user-defined function is natively compiled. 此引數是原生編譯之純量使用者定義函式的必要引數。This argument is required for natively compiled, scalar user-defined functions.

NATIVE_COMPILATION 引數在您 ALTER 函式時為必要項目,且只能在函式是透過 NATIVE_COMPILATION 引數建立時才能使用。The NATIVE_COMPILATION argument is required when you ALTER the function, and can only be used, if the function was created with the NATIVE_COMPILATION argument.

BEGIN ATOMIC WITHBEGIN ATOMIC WITH
只有針對原生編譯的純量使用者定義函式才支援,並且為必要項目。Supported only for natively compiled, scalar user-defined functions, and is required. 如需詳細資訊,請參閱 Atomic BlocksFor more information, see Atomic Blocks.

SCHEMABINDINGSCHEMABINDING
SCHEMABINDING 是原生編譯之純量使用者定義函式的必要引數。The SCHEMABINDING argument is required for natively compiled, scalar user-defined functions.

<function_option>::= and <clr_function_option>::=<function_option>::= and <clr_function_option>::=

指定函數必須有下列其中一個或多個選項。Specifies the function will have one or more of the following options.

ENCRYPTIONENCRYPTION
適用對象SQL Server 2008SQL Server 2008 及更新版本。Applies to: SQL Server 2008SQL Server 2008 and later.

指出 Database EngineDatabase Engine 會對包含 ALTER FUNCTION 陳述式文字的目錄檢視表資料行進行加密。Indicates that the Database EngineDatabase Engine encrypts the catalog view columns that contains the text of the ALTER FUNCTION statement. 使用 ENCRYPTION 可防止在 SQL ServerSQL Server 複寫中發行這個函數。Using ENCRYPTION prevents the function from being published as part of SQL ServerSQL Server replication. 無法為 CLR 函數指定 ENCRYPTION。ENCRYPTION cannot be specified for CLR functions.

SCHEMABINDINGSCHEMABINDING
指定函數必須繫結到它所參考的資料庫物件。Specifies that the function is bound to the database objects that it references. 如果其他結構描述繫結的物件正在參考函數,這個條件可防止對函數進行變更。This condition will prevent changes to the function if other schema bound objects are referencing it.

只有在下發生下列其中一個動作時,才會移除函數與其參考的物件之間的繫結:The binding of the function to the objects it references is removed only when one of the following actions occurs:

  • 已卸除這個函數。The function is dropped.

  • 您可以利用未指定 SCHEMABINDING 選項的 ALTER 陳述式來修改函數。The function is modified by using the ALTER statement with the SCHEMABINDING option not specified.

如需函式可以繫結結構描述前所必須符合的條件清單,請參閱 CREATE FUNCTION (Transact-SQL)For a list of conditions that must be met before a function can be schema bound, see CREATE FUNCTION (Transact-SQL).

RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUTRETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT
指定純量值函式的 OnNULLCall 屬性。Specifies the OnNULLCall attribute of a scalar-valued function. 若未指定,預設情況下意味著 CALLED ON NULL INPUT。If not specified, CALLED ON NULL INPUT is implied by default. 這表示,即使傳遞 NULL 做為引數,函數主體仍會執行。This means that the function body executes even if NULL is passed as an argument.

如果在 CLR 函數中指定 RETURNS NULL ON NULL INPUT,它會指出 SQL ServerSQL Server 可以在它接收的任何引數是 NULL 時傳回 NULL,而不必實際叫用函數主體。If RETURNS NULL ON NULL INPUT is specified in a CLR function, it indicates that SQL ServerSQL Server can return NULL when any of the arguments it receives is NULL, without actually invoking the body of the function. 如果 <method_specifier> 中指定的方法已經有一個指出 RETURNS NULL ON NULL INPUT 的自訂屬性,但 ALTER FUNCTION 陳述式卻指出 CALLED ON NULL INPUT,則優先使用 ALTER FUNCTION 陳述式。If the method specified in <method_specifier> already has a custom attribute that indicates RETURNS NULL ON NULL INPUT, but the ALTER FUNCTION statement indicates CALLED ON NULL INPUT, the ALTER FUNCTION statement takes precedence. 無法為 CLR 資料表值函式指定 OnNULLCall 屬性。The OnNULLCall attribute cannot be specified for CLR table-valued functions.

EXECUTE AS 子句EXECUTE AS Clause
指定執行使用者定義函數時所在的安全性內容。Specifies the security context under which the user-defined function is executed. 因此,您可以控制 SQL ServerSQL Server 要利用哪個使用者帳戶來驗證在函數參考的任何資料庫物件上的權限。Therefore, you can control which user account SQL ServerSQL Server uses to validate permissions on any database objects referenced by the function.

注意

無法為內嵌使用者定義函數指定 EXECUTE AS。EXECUTE AS cannot be specified for inline user-defined functions.

如需詳細資訊,請參閱 EXECUTE AS 子句 (Transact-SQL)For more information, see EXECUTE AS Clause (Transact-SQL).

< column_definition >::=< column_definition >::=

定義資料表資料類型。Defines the table data type. 資料表宣告包括資料行定義和條件約束。The table declaration includes column definitions and constraints. 針對 CLR 函式,只能指定 column_namedata_typeFor CLR functions, only column_name and data_type can be specified.

column_namecolumn_name
這是資料表中的資料行名稱。Is the name of a column in the table. 資料行名稱必須符合識別碼規則,在資料表中也必須是唯一的。Column names must comply with the rules for identifiers and must be unique in the table. column_name 可由 1 到 128 個字元組成。column_name can consist of 1 through 128 characters.

data_typedata_type
指定資料行資料類型。Specifies the column data type. Transact-SQLTransact-SQL 函式而言,允許所有資料類型 (包括 CLR 使用者定義型別),但 timestamp除外。For Transact-SQLTransact-SQL functions, all data types, including CLR user-defined types, are allowed except timestamp. 就 CLR 函式而言,允許所有資料類型 (包括 CLR 使用者定義型別),但 textntextimagecharvarcharvarchar(max)timestamp 除外。無法指定非純量類型 cursor 作為 Transact-SQLTransact-SQL 或 CLR 函式中的資料行資料類型。For CLR functions, all data types, including CLR user-defined types, are allowed except text, ntext, image, char, varchar, varchar(max), and timestamp.The nonscalar type cursor cannot be specified as a column data type in either Transact-SQLTransact-SQL or CLR functions.

DEFAULT constant_expressionDEFAULT constant_expression
指定在插入期間未明確提供值時,提供給資料行的值。Specifies the value provided for the column when a value is not explicitly supplied during an insert. constant_expression 是常數、NULL 或系統函式值。constant_expression is a constant, NULL, or a system function value. 除了含有 IDENTITY 屬性的資料行之外,任何資料行都可以套用 DEFAULT 定義。DEFAULT definitions can be applied to any column except those that have the IDENTITY property. 無法為 CLR 資料表值函式指定 DEFAULT。DEFAULT cannot be specified for CLR table-valued functions.

COLLATE collation_nameCOLLATE collation_name
指定資料行的定序。Specifies the collation for the column. 若未指定,就會將資料庫的預設定序指派給資料行。If not specified, the column is assigned the default collation of the database. 定序名稱可以是 Windows 定序名稱或 SQL 定序名稱。Collation name can be either a Windows collation name or a SQL collation name. 如需清單和詳細資訊,請參閱 Windows 定序名稱 (Transact-SQL)SQL Server 定序名稱 (Transact-SQL)For a list of and more information, see Windows Collation Name (Transact-SQL) and SQL Server Collation Name (Transact-SQL).

COLLATE 子句只能用來變更 charvarcharncharnvarchar 資料類型之資料行的定序。The COLLATE clause can be used to change the collations only of columns of the char, varchar, nchar, and nvarchar data types.

無法為 CLR 資料表值函式指定 COLLATE。COLLATE cannot be specified for CLR table-valued functions.

ROWGUIDCOLROWGUIDCOL
指出新資料行是一個資料列全域唯一識別碼資料行。Indicates that the new column is a row global unique identifier column. 每個資料表只能有一個 uniqueidentifier 資料行指定為 ROWGUIDCOL 資料行。Only one uniqueidentifier column per table can be designated as the ROWGUIDCOL column. ROWGUIDCOL 屬性只能指派給 uniqueidentifier 資料行。The ROWGUIDCOL property can be assigned only to a uniqueidentifier column.

ROWGUIDCOL 屬性不會強制執行資料行中所儲存之值的唯一性。The ROWGUIDCOL property does not enforce uniqueness of the values stored in the column. 它也不會自動為插入資料表中的新資料列產生值。It also does not automatically generate values for new rows inserted into the table. 若要為每個資料行產生唯一值,請在 INSERT 陳述式上使用 NEWID 函數。To generate unique values for each column, use the NEWID function on INSERT statements. 可以指定預設值;不過,NEWID 不能指定為預設值。A default value can be specified; however, NEWID cannot be specified as the default.

IDENTITYIDENTITY
指出新資料行是識別欄位。Indicates that the new column is an identity column. 新資料列加入至資料表時,SQL ServerSQL Server 會提供資料行的唯一累加值。When a new row is added to the table, SQL ServerSQL Server provides a unique, incremental value for the column. 識別欄位通常用來搭配 PRIMARY KEY 條件約束一起使用,當做資料表的唯一資料列識別碼。Identity columns are typically used together with PRIMARY KEY constraints to serve as the unique row identifier for the table. 可以將 IDENTITY 屬性指派給 tinyintsmallintintbigintdecimal(p,0)numeric(p,0) 資料行。The IDENTITY property can be assigned to tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0) columns. 每份資料表都只能建立一個識別欄位。Only one identity column can be created per table. 繫結的預設值和 DEFAULT 條件約束無法搭配識別欄位使用。Bound defaults and DEFAULT constraints cannot be used with an identity column. 您必須同時指定 seedincrement,或兩者都不指定。You must specify both the seed and increment or neither. 如果同時不指定這兩者,預設值便是 (1,1)。If neither is specified, the default is (1,1).

無法為 CLR 資料表值函式指定 IDENTITY。IDENTITY cannot be specified for CLR table-valued functions.

seedseed
這是要指派給資料表中第一個資料列的整數值。Is the integer value to be assigned to the first row in the table.

incrementincrement
這是要新增至資料表中後續資料列之 seed 值的整數值。Is the integer value to add to the seed value for successive rows in the table.

< column_constraint >::= 和 < table_constraint>::=< column_constraint >::= and < table_constraint>::=

定義指定之資料行或資料表的條件約束。Defines the constraint for a specified column or table. 就 CLR 函數而言,唯一允許的條件約束類型是 NULL。For CLR functions, the only constraint type allowed is NULL. 不允許具名條件約束。Named constraints are not allowed.

NULL | NOT NULLNULL | NOT NULL
判斷資料行中是否允許 Null 值。Determines whether null values are allowed in the column. 嚴格來說,NULL 並不算是條件約束,但是您可以如同指定 NOT NULL 一樣加以指定。NULL is not strictly a constraint but can be specified just like NOT NULL. 無法為 CLR 資料表值函式指定 NOT NULL。NOT NULL cannot be specified for CLR table-valued functions.

PRIMARY KEYPRIMARY KEY
這是一項條件約束,它利用唯一索引強制執行指定之資料行的實體完整性。Is a constraint that enforces entity integrity for a specified column through a unique index. 在資料表值使用者定義函數中,PRIMARY KEY 條件約束只能建立在每份資料表的一個資料行上。In table-valued user-defined functions, the PRIMARY KEY constraint can be created on only one column per table. 無法為 CLR 資料表值函式指定 PRIMARY KEY。PRIMARY KEY cannot be specified for CLR table-valued functions.

UNIQUEUNIQUE
這是一項條件約束,它透過唯一索引為指定的一個或多個資料行提供實體完整性。Is a constraint that provides entity integrity for a specified column or columns through a unique index. 一份資料表可以有多個 UNIQUE 條件約束。A table can have multiple UNIQUE constraints. 無法為 CLR 資料表值函式指定 UNIQUE。UNIQUE cannot be specified for CLR table-valued functions.

CLUSTERED | NONCLUSTEREDCLUSTERED | NONCLUSTERED
指出針對 PRIMARY KEY 或 UNIQUE 條件約束建立叢集或非叢集索引。Indicate that a clustered or a nonclustered index is created for the PRIMARY KEY or UNIQUE constraint. PRIMARY KEY 條件約束使用 CLUSTERED,UNIQUE 條件約束則使用 NONCLUSTERED。PRIMARY KEY constraints use CLUSTERED, and UNIQUE constraints use NONCLUSTERED.

只能為一個條件約束指定 CLUSTERED。CLUSTERED can be specified for only one constraint. 如果針對 UNIQUE 條件約束指定 CLUSTERED,且指定了 PRIMARY KEY 條件約束,則 PRIMARY KEY 會使用 NONCLUSTERED。If CLUSTERED is specified for a UNIQUE constraint and a PRIMARY KEY constraint is also specified, the PRIMARY KEY uses NONCLUSTERED.

無法為 CLR 資料表值函式指定 CLUSTERED 和 NONCLUSTERED。CLUSTERED and NONCLUSTERED cannot be specified for CLR table-valued functions.

CHECKCHECK
這是一個條件約束,藉由限制可能輸入一個或多個資料行的值,強制執行範圍完整性。Is a constraint that enforces domain integrity by limiting the possible values that can be entered into a column or columns. 無法為 CLR 資料表值函式指定 CHECK 條件約束。CHECK constraints cannot be specified for CLR table-valued functions.

logical_expressionlogical_expression
這是一個傳回 TRUE 或 FALSE 的邏輯運算式。Is a logical expression that returns TRUE or FALSE.

<computed_column_definition>::=<computed_column_definition>::=

指定計算資料行。Specifies a computed column. 如需有關計算資料行的詳細資訊,請參閱 CREATE TABLE (Transact-SQL)For more information about computed columns, see CREATE TABLE (Transact-SQL).

column_namecolumn_name
這是計算資料行的名稱。Is the name of the computed column.

computed_column_expressioncomputed_column_expression
這是定義計算資料行值的運算式。Is an expression that defines the value of a computed column.

<index_option>::=<index_option>::=

指定 PRIMARY KEY 或 UNIQUE 索引的索引選項。Specifies the index options for the PRIMARY KEY or UNIQUE index. 如需索引選項的詳細資訊,請參閱 CREATE INDEX (Transact-SQL)For more information about index options, see CREATE INDEX (Transact-SQL).

PAD_INDEX = { ON | OFF }PAD_INDEX = { ON | OFF }
指定索引填補。Specifies index padding. 預設值為 OFF。The default is OFF.

FILLFACTOR = fillfactorFILLFACTOR = fillfactor
指定指出在建立或變更索引期間,Database EngineDatabase Engine 各索引頁面之分葉層級填滿程度的百分比。Specifies a percentage that indicates how full the Database EngineDatabase Engine should make the leaf level of each index page during index creation or change. fillfactor 必須是 1 到 100 之間的整數值。fillfactor must be an integer value from 1 to 100. 預設值是 0。The default is 0.

IGNORE_DUP_KEY = { ON | OFF }IGNORE_DUP_KEY = { ON | OFF }
指定當插入作業嘗試將重複的索引鍵值插入唯一索引時所產生的錯誤回應。Specifies the error response when an insert operation attempts to insert duplicate key values into a unique index. IGNORE_DUP_KEY 選項只適用於在建立或重建索引之後所發生的插入作業。The IGNORE_DUP_KEY option applies only to insert operations after the index is created or rebuilt. 預設值為 OFF。The default is OFF.

STATISTICS_NORECOMPUTE = { ON | OFF }STATISTICS_NORECOMPUTE = { ON | OFF }
指定是否要重新計算散發統計資料。Specifies whether distribution statistics are recomputed. 預設值為 OFF。The default is OFF.

ALLOW_ROW_LOCKS = { ON | OFF }ALLOW_ROW_LOCKS = { ON | OFF }
指定是否允許資料列鎖定。Specifies whether row locks are allowed. 預設值是 ON。The default is ON.

ALLOW_PAGE_LOCKS = { ON | OFF }ALLOW_PAGE_LOCKS = { ON | OFF }
指定是否允許頁面鎖定。Specifies whether page locks are allowed. 預設值是 ON。The default is ON.

RemarksRemarks

ALTER FUNCTION 不能用來將純量值函式變更為資料表值函式,反之亦然。ALTER FUNCTION cannot be used to change a scalar-valued function to a table-valued function, or vice versa. 另外,ALTER FUNCTION 也不能用來將內嵌函數變更為多重陳述式函數,反之亦然。Also, ALTER FUNCTION cannot be used to change an inline function to a multistatement function, or vice versa. ALTER FUNCTION 不能用來將 Transact-SQLTransact-SQL 函數變更為 CLR 函數,反之亦然。ALTER FUNCTION cannot be used to change a Transact-SQLTransact-SQL function to a CLR function or vice-versa.

下列 Service Broker 陳述式不能包含在 Transact-SQLTransact-SQL 使用者定義函式的定義中:The following Service Broker statements cannot be included in the definition of a Transact-SQLTransact-SQL user-defined function:

  • BEGIN DIALOG CONVERSATIONBEGIN DIALOG CONVERSATION
  • END CONVERSATIONEND CONVERSATION
  • GET CONVERSATION GROUPGET CONVERSATION GROUP
  • MOVE CONVERSATIONMOVE CONVERSATION
  • RECEIVERECEIVE
  • SENDSEND

權限Permissions

需要函數或結構描述的 ALTER 權限。Requires ALTER permission on the function or on the schema. 如果此函數指定使用者定義型別,則需要該型別的 EXECUTE 權限。If the function specifies a user-defined type, requires EXECUTE permission on the type.

另請參閱See Also

CREATE FUNCTION (Transact-SQL) CREATE FUNCTION (Transact-SQL)
DROP FUNCTION (Transact-SQL) DROP FUNCTION (Transact-SQL)
對發行集資料庫進行結構描述變更 Make Schema Changes on Publication Databases
EVENTDATA (Transact-SQL)EVENTDATA (Transact-SQL)