Using Functions

Microsoft® SQL Server™ 2000 has built-in functions to perform certain operations. The function categories are:

Aggregate functions.

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

Configuration functions.

Are scalar functions that return information about configuration settings.

Cursor functions.

Return information about the status of a cursor.

Date and time functions.

Manipulate datetime and smalldatetime values.

Mathematical functions.

Perform trigonometric, geometric, and other numeric operations.

Meta data functions.

Return information on the attributes of databases and database objects.

Rowset functions.

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

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

System functions.

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

System statistical functions.

Return information regarding the performance of SQL Server.

Text and image functions.

Manipulate text and image values.

Uses of Functions

Functions can be used or included in:

• The select list of a query using a SELECT statement to return a value.

  SELECT DB_NAME()
  

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

  SELECT *
  FROM [Order Details]
  WHERE Quantity =
      (SELECT MAX(Quantity) FROM [Order Details])
  

• The search condition (WHERE clause) of a view to make the view dynamically conform to the user or environment at run time.

  CREATE VIEW ShowMyEmploymentInfo AS
  SELECT * FROM Employees
  WHERE EmployeeID = SUSER_SID()
  GO
  

• Any expression.
  
  
• A CHECK constraint or trigger to check 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)
  

• A DEFAULT constraint or trigger to supply a value in case one is not specified on 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 always used with parentheses, even when there is no parameter. An exception to this are the niladic functions (functions that take no parameters) used with the DEFAULT keyword. For more information about the DEFAULT keyword, see ALTER TABLE and CREATE TABLE, or Defaults.

The parameters to specify a database, computer, login, or database user are sometimes optional. If they are not specified, they default to the current database, host computer, login, or database user.

Functions can be nested (one function used inside another function).

Using Deterministic and Nondeterministic Functions

A function is either deterministic or nondeterministic. When it always returns the same result any time it is called with a specific set of input values, the function is called deterministic. When it returns different results each time it is called with a specific set of input values, it is nondeterministic.

A function's determinism can limit where it can be used. Only deterministic functions can be invoked in views and computed columns indexed.

For more information see Deterministic and Nondeterministic Functions.