Share via

  • Article

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

  • Any expression.

  • 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.

Function Categories

The following table lists the categories for the SQL Server functions.

Function category

Description

Aggregate Functions (Transact-SQL)

Perform operations that combine multiple values into one. Examples are COUNT, SUM, MIN, and MAX.

Configuration functions

Scalar functions that return information about configuration settings.

Cryptographic Functions (Transact-SQL)

Support encryption, decryption, digital signing, and the validation of digital signatures.

Cursor functions

Return information about the status of a cursor.

Date and Time functions

Change date and time values.

Mathematical functions

Perform trigonometric, geometric, and other numeric operations.

Metadata functions

Return information about the attributes of databases and database objects.

Ranking functions

Nondeterministic functions that return a ranking value for each row in a partition.

Rowset Functions (Transact-SQL)

Return the rowsets that can be used in the place of a table reference in a Transact-SQL statement.

Security functions

Return information about users and roles.

String functions

Change char, varchar, nchar, nvarchar, binary, and varbinary values.

System functions

Operate on or report on various system level options and objects.

System Statistical Functions (Transact-SQL)

Return information about the performance of SQL Server.

Text and image functions

Change text and image values.

Deterministic and Nondeterministic Functions

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.