Using Functions

Microsoft® SQL Server™ has built-in functions to perform certain operations quickly and easily. 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 on 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.

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

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.
• In a CHECK constraint or trigger to checking 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)

    

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