SQL Building Blocks and Server Settings

Aggregate Functions

Perform a calculation on a set of values and return a single summarizing value, e.g., COUNT(), SUM(), AVG()

Cursor Functions

Returns information about cursors.

Configuration Functions

Returns information about the current configuration.

Date and Time Functions

Performs an operation on a date and time input value and returns either a string, numeric or date and time value.

Mathematical Functions

Performs a calculation based on input values provided as parameters to the function, and returns a numeric value.

Metadata Functions

Returns information about the database and database objects.

Rowset Functions

Returns an object that can be used in an SQL statement like a table.

Security Functions

Returns information about users and roles.

String Functions

Performs an operation on a string (CHAR or VARCHAR) input value and returns a string or numeric value.

System Functions

Performs operations and returns information about values, objects and settings in Microsoft SQL Server.

System Statistical Functions

Returns statistical information about the system.

Text and Image Functions

Performs an operation on a text or image input values or column and returns information about the value.

AVG

Returns the average of the values in a group. Null values are ignored.

BINARY_CHECKSUM

Returns the binary checksum value computed over a row of a table or over a list of expressions. It can be used to detect changes to a row of a table.

Syntax: BINARY_CHECKSUM ( * | expression [ ,...n ] )

CHECKSUM

Returns the checksum value computed over a row of a table, or over a list of expressions. CHECKSUM is intended for use in building hash indices.

Syntax: CHECKSUM ( * | expression [ ,...n ] )

CHECKSUM_AGG

Returns the checksum of the values in a group. Null values are ignored.

Syntax: CHECKSUM_AGG ( [ ALL | DISTINCT ] expression )

COUNT

Returns the number of items in a group as an INT data type value.

Syntax: COUNT ( {[ ALL | DISTINCT ] expression ] | * })

COUNT_BIG

Returns the number of items in a group as a BIGINT data type value.

Syntax: COUNT_BIG ( {[ ALL | DISTINCT ] expression }| * )

GROUPING

Works only in SELECT statement with GROUP BY plus either ROLLUP or CUBE to determine whether a NULL in the result set was generated by ROLLUP/CUBE or comes from NULL value(s) in the underlying data.

Syntax: GROUPING ( column_name )

• returns 1 if a NULL under column_name is from ROLLUP or CUBE

• returns 0 if a NULL under column_name is from the data

See examples with ROLLUP and CUBE.

MAX

Returns the maximum value in the expression.

Syntax: MAX ( [ ALL | DISTINCT ] expression )

MIN

Returns the minimum value in the expression.

Syntax: MIN ( [ ALL | DISTINCT ] expression )

SUM

Returns the sum of the values in the expression. SUM can be used with numeric columns only. Null values are ignored.

Syntax: SUM ( [ ALL | DISTINCT ] expression )

STDEV

Returns the sample statistical standard deviation of all values in the given expression. For sample statistics the divisor is (n-1).

Syntax: STDEV ( expression ) )

STDEVP

Returns the population statistical standard deviation for all values in the given expression. For population statistics the divisor is (n).

Syntax: STDEVP ( expression ) )

VAR

Returns sample statistical variance of all values in the given expression. For sample statistics the divisor is (n-1).

Syntax: VAR ( expression )

VARP

Returns the population statistical variance for all values in the given expression. For population statistics the divisor is (n).

Syntax: VARP ( expression ) )

@@CURSOR_ROWS

Returns the number of rows in the last cursor opened on the connection.

@@FETCH_STATUS

Returns the status of the last cursor FETCH statement issued against any cursor currently opened by the connection.

Global function to all cursors in the connection, so use it immediately after the FETCH whose status you're interested in.

CURSOR_STATUS

A scalar function that allows the caller of a stored procedure to determine whether or not the procedure has returned a cursor and result set.

@@DATEFIRST

Returns the current value of the SET DATEFIRST parameter, which indicates the specified first day of each week:

• 1 for Monday, 2 for Wednesday, and so on through 7 for Sunday.

• The U.S. English default is 7, Sunday.

Syntax: -- Syntax for all functions in this table is just the function name
  @@DATEFIRST
Example:
SQL: SET DATEFIRST 1 -- Sets session value,
                          See Books Online
    SELECT @@DATEFIRST As'Beginning of Week'
Result: Beginning of Week
       -----------------
       1

@@DBTS

Returns the value of the current timestamp data type for the current database. This timestamp is guaranteed to be unique in the database.

Syntax: @@DBTS

@@LANGID

Returns local language identifier (ID) of the language currently in use.

@@LANGUAGE

Returns the name of the language currently in use.

@@LOCK_TIMEOUT

Returns the current lock time-out setting, in milliseconds, for the current session.

@@MAX_CONNECTIONS

Returns the maximum number of simultaneous user connections allowed on a Microsoft SQL Server. The number returned is not necessarily the number currently configured.

@@MAX_PRECISION

Returns the precision level used by decimal and numeric data types as currently set in the server.

@@NESTLEVEL

Returns the nesting level of the current stored procedure execution (initially 0).

@@OPTIONS

Returns information about current SET options. See description page 204.

@@REMSERVER

Returns the name of the remote Microsoft SQL Server database server as it appears in the login record. It enables a stored procedure to check the name of the database server from which the procedure is run.

@@SERVERNAME

Returns the name of the local server running Microsoft SQL Server.

@@SERVICENAME

Returns the name of the registry key under which Microsoft SQL Server is running. @@SERVICENAME returns MSSQLServer if the current instance is the default instance; this function returns the instance name if the current instance is a named instance.

@@SPID

Returns the server process identifier (ID) of the current user process.

@@TEXTSIZE

Returns the current value of the TEXTSIZE option of the SET statement, which specifies the maximum length, in bytes, of text or image data that a SELECT statement returns.

@@VERSION

Returns the date, version and processor type for the current installation of Microsoft SQL Server.

DATEADD

Returns a new datetime value after adding an interval (number argument) to the specified date argument. The interval is an integer whose date/time units are specified by the datepart argument as in DATEDIFF below.

Syntax: DATEADD ( datepart , number, date )
Example:
SQL: SELECT DATEADD(week, 1, '1 Jan, 2002' ) As '2d week in
  2002'
Result:  2d week in 2002
         --------------------------------
        2002-01-08 00:00:00.000

DATEDIFF

Returns the integer difference between two DATETIME arguments in the date or time increments specified by the datepart argument (year, quarter, ..., minute, ...).

Syntax: ( datepart , startdate , enddate )
Example:
SQL: SELECT DATEDIFF (week, '1 Jan, 2002', '19 Jan, 2002' )
  As NumWeeks
Result:  NumWeeks
         -----------
        2

DATENAME

Returns a character string representing the specified datepart of the specified date.

Syntax: DATENAME ( datepart , date )
Example:
SQL: SELECT DATENAME (month, '1 Jan, 2002' ) As '1st Month in
  2002'
Result:  1st Month in 2002
         ------------------------------
        January

DATEPART

Returns an integer representing the specified datepart of the specified date.

Syntax: DATEPART ( datepart , date )
Example:
SQL: SELECT DATEPART ( month, '1 Jan, 2002' ) As '1st Month
  in 2002'
Result:  1st Month in 2002
         -----------------
        1

DAY

Returns an integer representing the day datepart of the specified date.

Syntax: DAY ( date )
Example:
SQL: SELECT DAY( '1 Jan, 2002' ) As 'Day of Month'
Result:  Day of Month
         ------------
         1
Note: DAY( date ) is equivalent to:  DATEPART( dd, date )

MONTH

Returns an integer that represents the month part of a specified date.

Syntax: MONTH ( date )
Example:
SQL: SELECT MONTH( '1 Jan, 2002' ) As 'Month Number'
Result:  Month Number
         ------------
         1
Note: MONTH( date ) is equivalent to:  DATEPART( mm, date )

YEAR

Returns an integer that represents the year part of a specified date.

Syntax: YEAR ( date )
Example:
SQL: SELECT YEAR( '1 Jan, 2002' ) As Year
Result:  Year
         -----------
         2002
Note: YEAR( date ) is equivalent to:  DATEPART( yy, date )

GETDATE

Returns the current system date and time in the Microsoft SQL Server standard internal format for datetime values.

Syntax: GETDATE ( )
Example:
SQL: SELECT GETDATE() As Today
Result:  Today
         ------------------------------
         2002-03-27 17:26:14.723

GETUTCDATE

Returns the datetime value representing the current UTC time (Universal Time Coordinate or Greenwich Mean Time). The current UTC time is derived from the current local time and the time zone setting in the operating system of the computer on which SQL Server is running.

Syntax: GETUTCDATE()

Example:
SQL: SELECT GETDATE() As Now , GETUTCDATE() As NowUTC
-- From PST
Result:  Now                              NowUTC
         ------------------------------   -------------------------------
         2002-03-27 16:29:13.250          2002-03-27 23:29:13.250

ABS

Returns the absolute, positive value of the given numeric expression.

Syntax: ( numeric_expression )

ACOS

Returns the angle, in radians, (arccosine) whose cosine is the given float expression.

Syntax: ACOS ( float_expression )

ASIN

Returns the angle, in radians, (arcsine) whose sine is the given float expression.

Syntax: ASIN ( float_expression )

ATAN

Returns the angle in radians (arctangent) whose tangent is the given float expression.

Syntax: ATAN ( float_expression )

ATN2

Returns the angle, in radians, whose tangent is between the two given float expressions.

Syntax: ATN2 ( float_expression , float_expression )

CEILING

Returns the smallest integer greater than, or equal to, the given numeric expression.

E.g., CEILING( 2.67 ) is 3.

Syntax: CEILING ( numeric_expression )

COS

A mathematical function that returns the trigonometric cosine of the given angle (in radians) in the given expression.

Syntax: COS ( float_expression )

COT

A mathematic function that returns the trigonometric cotangent of the specified angle (in radians) in the given float expression.

Syntax: COT ( float_expression )

DEGREES

Returns the angle in degrees for an input angle in radians. E.g., DEGREES( PI()/2 ) is 90.0.

Syntax: DEGREES ( numeric_expression )

EXP

Returns the exponential value of the given float expression. That is, the natural logarithm base (approx. 2.71) raised to the exponent passed as argument. E.g., EXP(1) is 2.71.

Syntax: EXP ( float_expression )

FLOOR

Returns the largest integer less than or equal to the given numeric expression.

E.g., FLOOR( 2.67 ) is 2.

Syntax: FLOOR ( numeric_expression )

LOG

Returns the natural logarithm of the given float expression.

Syntax: LOG ( float_expression )

LOG10

Returns the base-10 logarithm of the given float expression.

Syntax: LOG10 ( float_expression )

PI

Returns the constant value of PI. I.e., PI() is 3.14159.

Syntax: PI ( )

POWER

Returns the value of the given expression to the specified power.

Syntax: POWER ( numeric_expression , y )

RADIANS

Returns radians when a numeric expression, in degrees, is entered.

Syntax: RADIANS ( numeric_expression )

RAND

Returns a random float value from 0 through 1.

Syntax: RAND ( [ seed ] )

ROUND

Returns a numeric expression, rounded to the specified length or precision.

Syntax: ROUND ( numeric_expression , length [ , function ] )

SIGN

Returns the positive (+1), zero (0), or negative (–1) sign of the given expression.

Syntax: SIGN ( numeric_expression )

SIN

Returns the trigonometric sine of the given angle (in radians) in an approximate numeric (float) expression.

Syntax: SIN ( float_expression )

SQRT

Returns the square root of the given expression.

Syntax: SQRT ( float_expression )

SQUARE

Returns the square of the given expression.

Syntax: SQUARE ( float_expression )

TAN

Returns the tangent of the input expression which is an angle in radians.

Syntax: TAN ( float_expression )

@@PROCID

Returns the current stored procedure identifier (ID).

COL_LENGTH

Returns the defined length (in bytes) of a column.

Syntax: COL_LENGTH ( 'table' , 'column' )

COL_NAME

Returns the name of a database column given the corresponding table identification number and column identification number.

Syntax: COL_NAME ( table_id , column_id )

COLUMNPROPERTY

Returns information about a column or procedure parameter.

Syntax: COLUMNPROPERTY ( id , column , property )

DATABASE-PROPERTY

Returns named database property value for the given database and property name for SQL Server 7.0 and before.

Syntax: DATABASEPROPERTY( database , property )

DATABASE-PROPERTYEX

Returns named database property value for the given database and property name for SQL Server 2K and later.

Syntax: DATABASEPROPERTYEX( database , property )

DB_ID

Returns the database identification (ID) number.

Syntax: DB_ID ( [ 'database_name' ] )

DB_NAME

Returns the database name.

Syntax: DB_NAME ( database_id )

FILE_ID

Returns the file identification (ID) number for the given logical file name in the current database.

Syntax: FILE_ID ( 'file_name' )

FILE_NAME

Returns the logical file name for the given file identification (ID) number.

Syntax: FILE_NAME ( file_id )

FILEGROUP_ID

Returns the filegroup identification (ID) number for the given filegroup name.

Syntax: FILEGROUP_ID ( 'filegroup_name' )

FILEGROUP_NAME

Returns the filegroup name for the given filegroup identification (ID) number.

Syntax: FILEGROUP_NAME ( filegroup_id )

FILEGROUPPROPERTY

Returns the specified filegroup property value when given a filegroup and property name.

Syntax: FILEGROUPPROPERTY ( filegroup_name , property )

FILEPROPERTY

Returns the specified file name property value when given a file name and property name.

Syntax: FILEPROPERTY ( file_name , property )

fn_listextended-property

Returns extended property values of database objects.

Syntax:
fn_listextendedproperty (
  {default | [ @name = ] 'property_name' | NULL }
  , {default | [ @level0type = ] 'level0_object_type' | NULL }
  , {default | [ @level0name = ] 'level0_object_name' | NULL }
  , {default | [ @level1type = ] 'level1_object_type' | NULL }
  , {default | [ @level1name = ] 'level1_object_name' | NULL }
  , {default | [ @level2type = ] 'level2_object_type' | NULL }
  , {default | [ @level2name = ] 'level2_object_name' | NULL }
  )

FULLTEXTCATALOGPROPERTY

Returns information about full-text catalog properties.

Syntax:
FULLTEXTCATALOGPROPERTY ( catalog_name , property )

FULLTEXTSERVICEPROPERTY

Returns information about full-text service-level properties.

Syntax: FULLTEXTSERVICEPROPERTY ( property )

INDEX_COL

Returns the indexed column name.

Syntax: INDEX_COL ( 'table' , index_id , key_id )

INDEXKEY_PROPERTY

Returns information about the index key.

Syntax:
INDEXKEY_PROPERTY ( tableID, indexID, keyID, property )

INDEXPROPERTY

Returns the named index property value given a table identification number, index name and property name.

Syntax: INDEXPROPERTY ( table_ID , index , property )

OBJECT_ID

Returns the database object identification number.

Syntax: OBJECT_ID ( 'object' )

OBJECT_NAME

Returns the database object name.

Syntax: OBJECT_NAME ( object_id )

OBJECT-PROPERTY

Returns information about objects in the current database.

Syntax: OBJECTPROPERTY ( id , property )

SQL_VARIANT_PROPERTY

Returns the base data type and other information about an sql_variant value.

Syntax: SQL_VARIANT_PROPERTY ( expression, property )

TYPEPROPERTY

Returns information about a data type.

Syntax: TYPEPROPERTY ( type , property )

CONTAINSTABLE

Returns a table of zero or more rows after doing a full-text type query based on precise or near (fuzzy) match criteria.

CONTAINSTABLE can be referenced in the FROM clause of a SELECT statement as if it were a regular table name.

Syntax:
CONTAINSTABLE ( table , {column | * }, ' <
    contains_search_condition > '
    [ , top_n_by_rank ] )

FREETEXTTABLE

Returns a table of zero or more rows after doing a full-text type query based on meaning of the text.

FREETEXTTABLE can be referenced in the FROM clause of a SELECT statement as if it were a regular table name.

Syntax:
FREETEXTTABLE ( table , {column | * },
    'freetext_string' [ , top_n_by_rank ] )

OPENDATASOURCE

Provides ad hoc connection information as part of a four-part object name without using a linked server name. See Books Online.

Syntax: OPENDATASOURCE ( provider_name, init_string )

OPENQUERY

Executes the specified pass-through query on the given linked server, which is an OLE DB data source.

The OPENQUERY function can be referenced in the FROM clause of a query as though it is a table name or as the target table of an INSERT, UPDATE or DELETE statement, subject to the OLE DB provider.

Syntax: OPENQUERY ( linked_server , 'query' )

OPENROWSET

This is an ad hoc method of connecting and accessing remote data using OLE DB and is an alternative to accessing tables in a linked server.

The OPENROWSET function can be referenced in the FROM clause of a query like a table or as the target table of an INSERT, UPDATE or DELETE statement, subject to the OLE DB provider.

Syntax:
OPENROWSET ( 'provider_name'
    , {'datasource' ; 'user_id' ; 'password'
        | 'provider_string' }
    , {[ catalog. ] [ schema. ] object
        | 'query' }
    )

OPENXML

Opens an XML document and returns the data as a rowset which may be used as a table in a read-only SQL statement.

Often used with sp_xml_preparedocument as in the example below.

Syntax:
OPENXML (
  idoc int [in], -- the document handle created by
    sp_xml_preparedocument
  rowpattern nvarchar[in] -- XPATH pattern to identify XML
    document nodes to be used as rows
       [, flags byte[in]] -- XML mapping:
    0 (default) attribute-centric,
    1 - attribute-centric then can use XML_ELEMENTS,
    2 - element-centric then can use XML_ATTRIBUTES,
    8 - combine XML_ELEMENTS and XML_ATTRIBUTES
)[WITH (SchemaDeclaration | TableName)] -- may specify
    nodes to display in result set

SELECT    *
               FROM OPENXML (@idoc, '/ROOT/Customer', 2)
               WITH (CustomerID  varchar(10), Name varchar(20))
               

CustomerID       Name
----------       --------------------
2                Amy Darling
36               Torie Dearest

(2 row(s) affected)

SELECT    *
               INTO table1
               FROM       OPENXML (@idoc, '/ROOT/Customer', 2)
               WITH (CustomerID  varchar(10), Name varchar(20))
               go
               
               SELECT *  FROM table1
               

CustomerID      Name
----------      --------------------
12              Amy Darling
36              Torie Dearest

(2 row(s) affected)

fn_trace_geteventinfo

Returns information about the events traced.

Syntax: fn_trace_geteventinfo ( [ @traceid = ] trace_id )

fn_trace_getfilterinfo

Returns information about the filters applied to a specified trace.

Syntax: fn_trace_getfilterinfo( [ @traceid = ] trace_id )

fn_trace_getinfo

Returns information about a specified trace or existing traces.

Syntax: fn_trace_getinfo( [ @traceid = ] trace_id )

fn_trace_gettable

Returns trace file information in a table format.

Syntax: fn_trace_gettable( [ @filename = ] filename , [ @numfiles = ] number_files )

HAS_DBACCESS

Indicates whether the user has access to the specified database.

Returns int 0 if no, 1 if yes, NULL if database name is invalid.

Syntax: HAS_DBACCESS ( 'database_name' )

IS_MEMBER

Indicates whether the current user is a member of the specified Microsoft Windows NT group or Microsoft SQL Server role.

Returns int 0 if no, 1 if yes, NULL if group or role is invalid.

Syntax: IS_MEMBER ( {'group' | 'role' })

IS_SRVROLEMEMBER

Indicates whether the current or specified user login is a member of the specified server role.

Returns int 0 if no, 1 if yes, NULL if role or login is invalid.

Syntax: IS_SRVROLEMEMBER ( 'role' [ , 'login' ] )

SUSER_SID

Returns the security identification number (SID) for the user's login name.

Syntax: SUSER_SID ( [ 'login' ] )
Example:
SQL: SELECT SUSER_SID( 'sa')
Result:  -------------
         0x01

SUSER_SNAME

Returns the login identification name for the current user or from the user's security identification number (SID) if specified.

Syntax: SUSER_SNAME ( [ server_user_sid ] )
Example:
SQL: SELECT SUSER_SNAME ( 0x1 )
Result:  -----------
         sa

USER

Allows a system-supplied value for the current user's database username to be inserted into a table when no default value is specified.

Syntax: USER
Example:
SQL: SELECT USER
Result:   ------
          dbo

USER_ID

Returns a user's database identification number.

Syntax: USER_ID ( [ 'user' ] )
Example:
SQL: SELECT USER_ID()
Result:   ------
          1

ASCII

Returns the ASCII code value of the leftmost character of a char expression.

Syntax: ASCII ( character_expression )
SQL:  PRINT ASCII('abc') -- The ASCII value of the letter
                               "a" is 97
Result:  97

CHAR

A string function that converts an int ASCII code to a character. Inverse of ASCII.

Syntax: CHAR ( integer_expression )
SQL:  PRINT CHAR(97) -- 97 is the ASCII value of the letter "a"
Result:  a

CHARINDEX

Returns the starting position of expr1 in a character string expr2. Search begins with 1st character unless start_location is given and is > 1.

Syntax: CHARINDEX ( expr1 , expr2 [ , start_location ] )

DIFFERENCE

Returns the difference between the SOUNDEX values of two character expressions as an integer. See Books Online.

Syntax: DIFFERENCE ( character_expression , character_expression )

LEFT

Returns the leftmost integer_expression characters of character_expr.

Syntax: LEFT ( character_expr , integer_expression )
SQL: PRINT LEFT('abcd' , 2)
Result:  ab

LEN

Returns the number of characters (may not be the number of bytes) of the given string expression, excluding trailing blanks.

Syntax: LEN ( string_expression )
SQL: PRINT  LEN('abc')
Result:  3

LOWER

Returns the character_expression string in all lower case.

Syntax: LOWER ( character_expression )
SQL: PRINT LOWER('AbCd')
Result:  abcd

LTRIM

Returns a character expression after removing leading blanks.

Syntax: LTRIM ( character_expression )

NCHAR

Returns the Unicode character with the given integer code.

Syntax: NCHAR ( integer_expression )

PATINDEX

Returns the starting position of the first occurrence of a pattern in the char_expr or zero if the pattern is not found (text or character data types).

Syntax:PATINDEX ( '%pattern%' , char_expr )
Example:
SQL: SELECT PATINDEX( '%cd%' , 'abcde' )
Result:  3

QUOTENAME

Returns a Unicode string with the delimiters added to make the input string a valid Microsoft SQL Server delimited identifier.

Syntax: QUOTENAME ( 'character_string' [ , 'quote_character' ] )

REPLACE

Replaces all occurrences of the second given string expression in the first string expression with a third expression.

Syntax: REPLACE ( 'string_expr1' , 'string_expr2' ,
'string_expr3' )
Example:
SQL: SELECT REPLACE ('aaaXXXbbbXXXccc' , 'XXX' , 'YYY')
Result: -----------------------
        aaaYYYbbbYYYccc

REPLICATE

Repeats a character expression for a specified number of times.

Syntax: REPLICATE ( character_expression , integer_expression )

REVERSE

Returns the reverse of a character expression.

Syntax: REVERSE ( character_expression )

RIGHT

Returns the rightmost <integer_expr> characters of <character_expr>.

Syntax: RIGHT ( character_expr , integer_expr )

RTRIM

Returns a character expression after removing trailing blanks.

Syntax: RTRIM ( character_expression )

SOUNDEX

Returns a four-character (SOUNDEX) code to evaluate the similarity of two strings.

Syntax: SOUNDEX ( character_expression )

SPACE

Returns a string of repeated spaces.

Syntax: SPACE ( integer_expression )

STR

Returns character data converted from numeric data.

Syntax: STR ( float_expression [ , length [ , decimal ] ] )

STUFF

Replaces characters in char_expr1 from start to start plus length with char_expr2.

Syntax: STUFF ( char_expr1 , start , length , char_expr2 )

SUBSTRING

Returns part of a character, binary, text, or image expression.

Syntax: SUBSTRING ( expression , start , length )

UNICODE

Returns the integer Unicode value for the first character of the expression.

Syntax: UNICODE ( 'ncharacter_expression' )

UPPER

Returns the character_expression string in all upper case.

Syntax: UPPER ( character_expression )

@@ERROR

Returns the error number for the last Transact-SQL statement executed.

Syntax: @@ERROR

@@IDENTITY

Returns the last-inserted identity value.

Syntax: @@IDENTITY

@@ROWCOUNT

Returns the number of rows affected by the last statement.

Syntax: @@ROWCOUNT

@@TRANCOUNT

Returns the number of active transactions for the current connection.

Syntax: @@TRANCOUNT

APP_NAME

Returns the application name for the current session if set.

Syntax: APP_NAME ( )

CASE expression

Evaluates a list of conditions and returns one of multiple possible result expressions. See explanation and examples, page 165.

CAST and CONVERT

Explicitly converts an expression of one data type to another. CAST and CONVERT provide similar functionality. See explanation and examples, page 167.

Syntax:
 CAST ( expression AS data_type )
 CONVERT ( data_type [ ( length ) ] , expression
    [ , style ] )

COALESCE

Returns the first nonnull expression among its arguments.

Syntax: COALESCE ( expression [ ,...n ] )

COLLATIONPROPERTY

Returns the property of a given collation.

Syntax: COLLATIONPROPERTY( collation_name, property )

CURRENT_TIMESTAMP

Returns the current date and time. Equivalent to GETDATE().

Syntax: CURRENT_TIMESTAMP

CURRENT_USER

Returns the current user. This function is equivalent to USER_NAME().

Syntax: CURRENT_USER

DATALENGTH

Returns the number of bytes used to represent an expression.

Syntax: DATALENGTH ( expression )

fn_helpcollations

Returns a list of all the collations supported by SQL Server 2K.

Syntax: fn_helpcollations ()

fn_servershareddrives

Returns the names of shared drives used by the clustered server.

Syntax: fn_servershareddrives()

fn_virtualfilestats

Returns I/O statistics for database files, including log files.

Syntax:
fn_virtualfilestats ( [@DatabaseID=] database_id ,
  [ @FileID = ] file_id )

FORMATMESSAGE

Constructs a message from an existing message in sysmessages. The functionality of FORMATMESSAGE resembles that of the RAISERROR statement; however, RAISERROR prints the message immediately. FORMATMESSAGE returns the edited message for further processing.

Syntax: FORMATMESSAGE ( msg_number , param_value
             [ ,...n ] )

GETANSINULL

Returns the effective default nullability for the database for this session.

Syntax: GETANSINULL ( [ 'database' ] )

HOST_ID

Books Online says this returns the client workstation identification number. This value appears to be the process id for each client program from the client host. Thus the value differs for each client program.

Syntax: HOST_ID ( )

HOST_NAME

Returns the client workstation name. This name is the same for each connection from the client host.

Syntax: HOST_NAME ( )

IDENT_CURRENT

Returns the last identity value generated for a specified table in any session and any scope.

Syntax: IDENT_CURRENT('table_name')

IDENT_INCR

Returns the numeric increment value specified during the creation of an identity column in a table or view that has an identity column.

Syntax: IDENT_INCR ( 'table_or_view' )

IDENT_SEED

Returns the numeric seed value specified during the creation of an identity column in a table or a view that has an identity column.

Syntax: IDENT_SEED ( 'table_or_view' )

IDENTITY (Function)

Is used only in a SELECT statement with an INTO table clause to insert an identity column into a new table. The IDENTITY function is similar to the IDENTITY property used with CREATE TABLE.

Syntax: IDENTITY ( data_type [ , seed , increment ] ) AS
column_name
Example:
SQL: SELECT IDENTITY(INT) As id , name INTO
newemp FROM emp

ISDATE

Returns 1 if the expression is a valid date, 0 if not.

Syntax: ISDATE ( expression )

ISNULL

If expr1 is NULL, it is replaced with expr2. That is, IF expr1 IS NOT NULL returns expr1 ELSE returns expr2

Syntax: ISNULL ( expr1 , expr2 )
Example:
SQL: SELECT ISNULL( 'Hello', 'Null') , ISNULL
(NULL,
  'Null word')
Result: ----------- -------------
        Hello       Null word

ISNUMERIC

Returns 1 if the expression is a valid numeric type, 0 if not.

Syntax: ISNUMERIC ( expression )

NEWID

Creates a unique value of type uniqueidentifier.

Syntax: NEWID ( )

NULLIF

Returns NULL if the two expressions are equivalent. If not equivalent, returns the first expression. See Books Online for meaningful example.

Syntax: NULLIF ( expression , expression )

PARSENAME

Returns the specified part of an object name. Parts of an object that can be retrieved are the object name, owner name, database name, and server name. Note: This function does not indicate whether or not the specified object exists. It just returns the specified piece of the given object name.

Syntax: PARSENAME ( 'object_name' , object_piece )

object_piece (integer)

Meaning

1

Object name

2

Owner name

3

Database name

4

Server name

PERMISSIONS

Returns a value containing a bitmap that indicates the statement, object or column permissions for the current user.

Syntax: PERMISSIONS ( [ objectid [ , 'column' ] ] )

ROWCOUNT_BIG

Returns the number of rows affected by the last statement executed. This function operates like @@ROWCOUNT, except that the return type of ROWCOUNT_BIG is bigint.

Syntax: ROWCOUNT_BIG ( )

SCOPE_IDENTITY

Returns the last IDENTITY value inserted into an IDENTITY column in the same scope. A scope is a module — a stored procedure, trigger, function or batch. Thus, two statements are in the same scope if they are in the same stored procedure, function or batch.

Syntax: SCOPE_IDENTITY( )

SERVERPROPERTY

Returns property information about the server instance.

Syntax: SERVERPROPERTY ( propertyname )

SESSIONPROPERTY

Returns the SET options settings of a session.

Syntax: SESSIONPROPERTY ( option )

SESSION_USER

Returns the username of the current user. May be used as a column DEFAULT in CREATE TABLE to insert the name of the user executing an INSERT statement. See example below under SYSTEM_USER.

Syntax: SESSION_USER

STATS_DATE

Returns the date and time that the statistics for the specified index were last updated. See example page 297.

Syntax: STATS_DATE ( table_id , index_id )

SYSTEM_USER

Returns the system username of the current user. May be used as a column DEFAULT in CREATE TABLE to insert the name of the user executing an INSERT statement.

Syntax: SYSTEM_USER
Example:
SQL: SELECT SESSION_USER As Sess , SYSTEM_USER
As Sys
Result:      Sess        Sys
   -----     -------
   dbo        sa

USER_NAME

Returns a user database username from a given identification number.

Syntax: USER_NAME ( [ id ] )

@@CONNECTIONS

Returns the number of connections, or attempted connections, since Microsoft SQL Server was last started.

@@CPU_BUSY

Returns the time in milliseconds (based on the resolution of the system timer) that the CPU has spent working since Microsoft SQL Server was last started.

@@IDLE

Returns the time in milliseconds (based on the resolution of the system timer) that Microsoft SQL Server has been idle since last started.

@@IO_BUSY

Returns the time in milliseconds (based on the resolution of the system timer) that Microsoft SQL Server has spent performing input and output operations since it was last started.

@@PACK_RECEIVED

Returns the number of input packets read from the network by Microsoft SQL Server since last started.

@@PACK_SENT

Returns the number of output packets written to the network by MicrosoftSQL Server since last started.

@@PACKET_ERRORS

Returns the number of network packet errors that have occurred on Microsoft SQL Server connections since SQL Server was last started.

@@TIMETICKS

Returns the number of microseconds per tick.

@@TOTAL_ERRORS

Returns the number of disk read/write errors encountered by Microsoft SQL Server since last started.

@@TOTAL_READ

Returns the number of disk reads (not cache reads) by Microsoft SQL Server since last started.

@@TOTAL_WRITE

Returns the number of disk writes by Microsoft SQL Server since last started.

fn_virtualfilestats

Returns I/O statistics for database files, including log files.

Syntax: fn_virtualfilestats ( [@DatabaseID=]database_id
        , [ @FileID = ] file_id )

DATALENGTH

Returns the number of bytes used to represent any expression.

Syntax: DATALENGTH ( expression )

PATINDEX

Returns the starting position of the first occurrence of a pattern in a specified expression or zero if the pattern is not found. All text and character data types.

Syntax: PATINDEX ( '%pattern%' , expression )

SUBSTRING

Returns part of a character, binary, text, or image expression. See Books Online.

Syntax: SUBSTRING ( expression , start , length )

TEXTPTR

Returns the text-pointer value that corresponds to a text, ntext, or image column in varbinary format. The retrieved text pointer value can be used in READTEXT, WRITETEXT, and UPDATETEXT statements.

Syntax: TEXTPTR ( column )

TEXTVALID

Returns 1 if a given text, ntext, or image pointer is valid, 0 if not.

Syntax: TEXTVALID ( 'table.column' , text_ ptr )

READTEXT

Reads text, ntext, or image values from a text, ntext, or image column, starting from a specified offset and reading the specified number of bytes.

Syntax: READTEXT {table.column text_ptr offset size }[ HOLDLOCK ]

SET TEXTSIZE

Specifies the size of text and ntext data returned with a SELECT statement.

Syntax: SET TEXTSIZE {number }

UPDATETEXT

Updates an existing text, ntext, or image field. Use UPDATETEXT to change only a portion of a text, ntext or image column in place.

WRITETEXT

Permits nonlogged, interactive updating of an existing text, ntext or image column. This statement completely overwrites any existing data in the column it affects. WRITETEXT cannot be used on text, ntext and image columns in views.

Syntax: WRITETEXT {table.column text_ptr } [ WITH LOG
] {data }

ABS

COS

EXP

NULLIF

SIN

ACOS

COT

FLOOR

PARSENAME

SQUARE

ASIN

DATALENGTH

ISNULL

PI

SQRT

ATAN

DATEADD

ISNUMERIC

POWER

TAN

ATN2

DATEDIFF

LOG

RADIANS

YEAR

CEILING

DAY

LOG10

ROUND

COALESCE

DEGREES

MONTH

SIGN

CASE expression

COALESCE

DATALENGTH

fn_helpcollations

ISNULL

ISNUMERIC

NULLIF

PARSENAME

CAST

Deterministic unless used with datetime, smalldatetime or sql_variant.

CONVERT

Deterministic unless used with datetime, smalldatetime or sql_variant. The datetime and smalldatetime data types are deterministic if style parameter is given.

CHECKSUM

Deterministic, with the exception of CHECKSUM(*).

DATEPART

Deterministic except when used as DATEPART (dw, date). The value returned by dw, weekday, depends on the value set by SET DATEFIRST.

ISDATE

Deterministic only if used with the CONVERT function, the CONVERT style parame ter is specified and style is not equal to 0, 100, 9 or 109.

RAND

RAND is deterministic only when a seed parameter is specified.

@@ERROR

fn_servershareddrives

IDENT_INCR

SESSIONPROPERTY

@@IDENTITY

fn_virtualfilestats

IDENT_SEED

STATS_DATE

@@ROWCOUNT

FORMATMESSAGE

IDENTITY

SYSTEM_USER

@@TRANCOUNT

GETANSINULL

NEWID

TEXTPTR

APP_NAME

GETDATE

PERMISSIONS

TEXTVALID

COLLATIONPROPERTY

GETUTCDATE

ROWCOUNT_BIG

USER_NAME

CURRENT_TIMESTAMP

HOST_ID

SCOPE_IDENTITY

CURRENT_USER

HOST_NAME

SERVERPROPERTY

DATENAME

IDENT_CURRENT

SESSION_USER

Buick

GM

Quattro

Au

Jeep

DC

Sebring

DC

SELECT Make, CASE Manufacturer
               WHEN 'GM'   THEN 'General Motors'
               WHEN 'Au'  THEN 'Audi'
               WHEN 'DC'  THEN 'Daimler-Chrysler'
               ELSE 'Manufacturer not found'
               END   As Manufacturer
               FROM Autos;
               

Make             Manufacturer
----------       --------------------
Buick            General Motors
Quattro          Audi
Jeep             Daimler-Chrysler
Sebring          Daimler-Chrysler

Torie

87

Counselling

James

76

Dog Husbandry

Amy

93

Tae Kwon Do

Tim

82

Jet Skiing

Ina

98

Flower Gardening

SELECT Student, CASE   WHEN Grade > 90 THEN 'A'
               WHEN Grade > 80 THEN 'B'
               WHEN Grade > 70 THEN 'C'
               WHEN Grade > 60 THEN 'D'
               ELSE 'F'
               END    As  LetterGrade
               FROM Grades
               ORDER BY Student;
               

Student           LetterGrade
----------        --------------
Amy               A
Ina               A
James             C
Tim               B
Torie             B