Functions (Database Engine)
SQL Server provides built-in functions that can be used to perform certain operations.
Functions can be used or included in the following:
The select list of a query that uses a SELECT statement to return a value.
SELECT DB_NAME(); GO
A WHERE clause search condition of a SELECT or data modification (SELECT, INSERT, DELETE, or UPDATE) statement to limit the rows that qualify for the query.
USE AdventureWorks; GO SELECT SalesOrderID, ProductID, OrderQty FROM Sales.SalesOrderDetail WHERE OrderQty = (SELECT MAX(OrderQty) FROM Sales.SalesOrderDetail); GO
The search condition, WHERE clause, of a view to make the view dynamically comply with the user or environment at run time.
CREATE VIEW ShowMyEmploymentInfo AS SELECT FirstName, LastName FROM Person.Contact WHERE ContactID = SUSER_SID(); GO
A CHECK constraint or trigger to look for specified values when data is inserted.
CREATE TABLE SalesContacts (SalesRepID INT PRIMARY KEY CHECK (SalesRepID = SUSER_SID() ), ContactName VARCHAR(50) NULL, ContactPhone VARCHAR(13) NULL); GO
A DEFAULT constraint or trigger to supply a value in case one is not specified by an INSERT.
CREATE TABLE SalesContacts ( SalesRepID INT PRIMARY KEY CHECK (SalesRepID = SUSER_SID() ), ContactName VARCHAR(50) NULL, ContactPhone VARCHAR(13) NULL, WhenCreated DATETIME DEFAULT GETDATE(), Creator INT DEFAULT SUSER_SID() ); GO
Functions are specified with parentheses even when there is no parameter. Exceptions to this are the niladic functions that are used with the DEFAULT keyword. Niladic functions do not take parameters. For more information about the DEFAULT keyword, see ALTER TABLE (Transact-SQL) and CREATE TABLE (Transact-SQL).
The parameters to specify a database, computer, login, or database user are sometimes optional. If they are not specified, the default is the current database, host computer, login, or database user.
Functions can be nested.
The following table lists the categories for the SQL Server functions.
Perform operations that combine multiple values into one. Examples are COUNT, SUM, MIN, and MAX.
Scalar functions that return information about configuration settings.
Support encryption, decryption, digital signing, and the validation of digital signatures.
Return information about the status of a cursor.
Change date and time values.
Perform trigonometric, geometric, and other numeric operations.
Return information about the attributes of databases and database objects.
Nondeterministic functions that return a ranking value for each row in a partition.
Return the rowsets that can be used in the place of a table reference in a Transact-SQL statement.
Return information about users and roles.
Change char, varchar, nchar, nvarchar, binary, and varbinary values.
Operate on or report on various system level options and objects.
Return information about the performance of SQL Server.
Change text and image values.
In SQL Server, functions are classified as strictly deterministic, deterministic, or nondeterministic.
A function is strictly deterministic if, for a specific set of input values, the function always returns the same results.
For user-defined functions, a less rigid notion of determinism is applied. A user-defined function is deterministic if, for a specific set of input values and database state, the function always returns the same results. If the function is not strictly deterministic, it can be deterministic in this sense if it is data-accessing.
A nondeterministic function may return different results when it is called repeatedly with the same set of input values. For example, the function GETDATE() is nondeterministic. SQL Server puts restrictions on various classes of nondeterminism. Therefore, nondeterministic functions should be used carefully.
For built-in functions, determinism and strict determinism are the same. For Transact-SQL user-defined functions, the system verifies the definition and prevents the definition of nondeterministic functions. However, a data-accessing or nonschema-bound function is considered not strictly deterministic. For common language runtime (CLR) functions, function definitions specify the deterministic, data access, and system data access properties of the function, but because these properties are not system-verified, the functions are always considered not strictly deterministic.
The lack of determinism of a function can limit where it can be used. Only deterministic functions can be invoked in indexed views, indexed computed columns, persisted computed columns, or definitions of Transact-SQL user-defined functions.
The lack of strict determinism of a function can block valuable performance optimizations. Certain plan reordering steps are skipped to conservatively preserve correctness. Additionally, the number, order, and timing of calls to user-defined functions is implementation-dependent. Do not rely on these invocation semantics. Aside from runtime constant nondeterministic built-ins RAND, and the GETDATE family, the number, order, and timing of calls will depend on the plan chosen.
Best Practice Recommendations
We recommend that you following the following guidelines whenever possible:
1. Write strictly deterministic functions when you have a choice. In particular, make your Transact-SQL functions schema-bound.
2. Limit uses of nondeterministic functions to outermost select lists.
3. Do not use nondeterministic functions in performance-critical queries.
4. Do not rely on the number, order, or timing of calls, because these can be implementation-dependent.
For more information, see Deterministic and Nondeterministic Functions.