Functions (Database Engine)

SQL Server 2005 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.

Deterministic and Nondeterministic Functions

In SQL Server 2005, 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.

See Also

Concepts

Conversion Functions
Date Functions

Other Resources

Functions (Transact-SQL)
Using Mathematical Functions

Help and Information

Getting SQL Server 2005 Assistance