Microsoft® SQL Server™ 2000 has built-in functions to perform certain operations. The function categories are:
Perform operations that combine multiple values into one. Examples are COUNT, SUM, MIN, and MAX.
Are scalar functions that return information about configuration settings.
Return information about the status of a cursor.
Date and time functions.
Manipulate datetime and smalldatetime values.
Perform trigonometric, geometric, and other numeric operations.
Meta data functions.
Return information on the attributes of databases and database objects.
Return rowsets that can be used in the place of a table reference in a Transact-SQL statement.
Return information about users and roles.
Manipulate char, varchar, nchar, nvarchar, binary, and varbinary values.
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.
- 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.