SQL 資料庫函式是什麼?What are the SQL database functions?

適用於: 是SQL Server (從 2008 開始) 是Azure SQL Database 是Azure SQL 資料倉儲 是平行處理資料倉儲 APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

深入了解您可以與 SQL 資料庫搭配使用之內建函式的類別。Learn about the categories of built-in functions you can use with SQL databases. 您可以使用內建函式,或建立您自己的使用者定義函式。You can use the built-in functions or create your own user-defined functions.

彙總函式Aggregate functions

彙總函式會根據一組值來執行計算,再傳回單一值。Aggregate functions perform a calculation on a set of values and return a single value. 它們可在選取清單中或在 SELECT 陳述式的 HAVING 子句中使用。They are allowed in the select list or the HAVING clause of a SELECT statement. 您可以與 GROUP BY 子句搭配使用彙總來計算資料列類別的彙總。You can use an aggregation in combination with the GROUP BY clause to calculate the aggregation on categories of rows. 使用 OVER 子句來計算特定範圍值的彙總。Use the OVER clause to calculate the aggregation on a specific range of value. OVER 子句無法接續在 GROUPING 或 GROUPING_ID 彙總之後。The OVER clause cannot follow the GROUPING or GROUPING_ID aggregations.

所有彙總函式都具有確定性,即在相同的輸入值上執行時,它們一律會傳回相同的值。All aggregate functions are deterministic, which means they always return the same value when they run on the same input values. 如需詳細資訊,請參閱確定性與非確定性函式。|For more information, see Deterministic and Nondeterministic Functions.|

分析函數Analytic functions

分析函數會根據資料列群組計算出彙總值。Analytic functions compute an aggregate value based on a group of rows. 但不同於彙總函式,分析函式可以傳回每個群組的多個資料列。However, unlike aggregate functions, analytic functions can return multiple rows for each group. 您可以使用分析函式計算群組中的移動平均、最新總數、百分比或前 N 個結果。You can use analytic functions to compute moving averages, running totals, percentages, or top-N results within a group.

排名函數Ranking functions

排名函數會傳回資料分割中每個資料列的次序值。Ranking functions return a ranking value for each row in a partition. 根據所用的函數而定,有些資料列可能會收到與其他資料列相同的值。Depending on the function that is used, some rows might receive the same value as other rows. 排名函數不具決定性。Ranking functions are nondeterministic.

資料列集函式Rowset functions

資料列集函式會傳回可在 SQL 陳述式中,依照資料表參考的方式來使用的物件。Rowset functions Return an object that can be used like table references in an SQL statement.

純量函式Scalar functions

處理單一值,再傳回單一值。Operate on a single value and then return a single value. 凡是運算式有效之處,都能夠使用純量函數。Scalar functions can be used wherever an expression is valid.

純量函式類別Categories of scalar functions

函數類別目錄Function category DescriptionDescription
組態函式Configuration Functions 傳回目前組態的詳細資訊。Return information about the current configuration.
轉換函式Conversion Functions 支援資料類型轉型及轉換。Support data type casting and converting.
資料指標函式Cursor Functions 傳回資料指標的詳細資訊。Return information about cursors.
日期與時間資料類型與函式Date and Time Data Types and Functions 執行作業來處理日期和時間輸入值,以及傳回字串、數值,或日期和時間值。Perform operations on a date and time input values and return string, numeric, or date and time values.
JSON 函式JSON Functions 驗證、查詢,或變更 JSON 資料。Validate, query, or change JSON data.
邏輯函式Logical Functions 執行邏輯運算。Perform logical operations.
數學函式Mathematical Functions 根據函數參數所提供的輸入值來執行計算,以及傳回數值。Perform calculations based on input values provided as parameters to the functions, and return numeric values.
中繼資料函式Metadata Functions 傳回資料庫和資料庫物件的相關資訊。Return information about the database and database objects.
安全性函式Security Functions 傳回使用者和角色的相關資訊。Return information about users and roles.
字串函式String Functions 執行作業來處理字串 (charvarchar) 輸入值,並傳回字串或數值。Perform operations on a string (char or varchar) input value and return a string or numeric value.
系統函式System Functions 執行作業和傳回 SQL ServerSQL Server 執行個體中的值、物件和設定的相關資訊。Perform operations and return information about values, objects, and settings in an instance of SQL ServerSQL Server.
系統統計函式System Statistical Functions 傳回系統的統計資訊。Return statistical information about the system.
文字和影像函式Text and Image Functions 執行作業來處理文字或影像輸入值或資料行,以及傳回值的相關資訊。Perform operations on text or image input values or columns, and return information about the value.

函數決定性Function Determinism

SQL ServerSQL Server 內建函數可能具決定性或不具決定性。built-in functions are either deterministic or nondeterministic. 如果每當利用一組特定輸入值來呼叫函數時,函數都會傳回相同的值,這些函數便是具決定性。Functions are deterministic when they always return the same result any time they are called by using a specific set of input values. 如果每次呼叫時都可能傳回不同結果,即便使用同一組特定的輸入值也是如此,這些函數便是不具決定性。Functions are nondeterministic when they could return different results every time they are called, even with the same specific set of input values. 如需詳細資訊,請參閱確定性與非確定性函式For more information, see Deterministic and Nondeterministic Functions

函數定序Function Collation

採取字元字串輸入、傳回字元字串輸出的函數,輸出會使用輸入字串的定序。Functions that take a character string input and return a character string output use the collation of the input string for the output.

使用非字元輸入並傳回字元字串的函數,輸出會使用目前資料庫的預設定序。Functions that take non-character inputs and return a character string use the default collation of the current database for the output.

採取多重字元字串輸入、傳回單一字元字串的函數,會利用定序優先順序的規則來設定輸出字串的定序。Functions that take multiple character string inputs and return a character string use the rules of collation precedence to set the collation of the output string. 如需詳細資訊,請參閱定序優先順序 (Transact-SQL)For more information, see Collation Precedence (Transact-SQL).

另請參閱See Also

CREATE FUNCTION (Transact-SQL) CREATE FUNCTION (Transact-SQL)
確定性與非確定性函式 Deterministic and Nondeterministic Functions
使用預存程序 (MDX)Using Stored Procedures (MDX)