Home > Articles > Data > SQL Server

  • Print
  • + Share This
This chapter is from the book

2.6 TRANSACT-SQL FUNCTIONS

Microsoft SQL Server 2000 has a large number of built-in functions available for SQL programming, database administration and other purposes. The major categories are listed in Table 2-51.

The following sections contain detailed explanations for each of the categories from Table 2-51, categorizing the functions and describing their general purpose. Some of the commonly used functions are covered in greater detail, with examples. All functions can be found in Books Online.

Table 2-51. Built-in Functions

Function Category

Description

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.

See also Deterministic and Nondeterministic Functions, page 162.

2.6.1 Aggregate Functions

Aggregate functions perform a calculation on a set of values and return a single summarizing value. Table 2-52 lists the function names and their details.COUNT(), AVG(), MIN(), MAX() and SUM() are ANSI SQL-92 and 99 standard. All are deterministic (see page 162).

Aggregate functions are only allowed as expressions in the following cases.

  • The select list of a SELECT statement (either a subquery or an outer query).

  • A COMPUTE or COMPUTE BY clause.

  • A HAVING clause.

Table 2-52. Aggregate Functions

Aggregate Function Name

Description and Syntax

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 ) )

2.6.2 Cursor Functions

Cursor Functions, listed in Table 2-53, return cursor status information. All are nondeterministic (see page 162).

Table 2-53. Cursor Functions

Function Name

Description and Syntax

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

These functions are discussed in more detail in Cursors, page 638.

2.6.3 Configuration Functions

Configuration functions, listed in Table 2-54, return information about the current server and database configuration settings. All are nondeterministic (see page 162).

Table 2-54. Configuration Functions

Configuration Function Name

Description (Syntax is just the Function Name since all are read-only and none take parameters.)

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

Example:

SQL

SELECT @@SERVERNAME As Server , @@SERVICENAME Service
                  
 

Result

Server               Service
----------------     -----------------------
AMY                  MSSQLServer

2.6.4 Date and Time Functions

Date and time functions perform an operation on a date and time input value and return either a string, numeric or date and time value. See Table 2-55.

These functions are deterministic and nondeterministic. See details page 163. DATENAME, GETDATE and GETUTCDATE are nondeterministic. DATEPART is deterministic unless used with dw datepart. The rest are deterministic.

Table 2-55. Date and Time Functions

Date Function Name

Description and Syntax

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.

SyntaxDATEADD ( 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.

SyntaxDATENAME ( 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.

SyntaxDATEPART ( 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.

SyntaxDAY ( 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.

SyntaxMONTH ( 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.

SyntaxYEAR ( 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.

SyntaxGETDATE ( )
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.

SyntaxGETUTCDATE()

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

2.6.5 Mathematical Functions

A mathematical function performs a calculation based on input values provided as parameters to the function and returns a numeric value (Table 2-56). All are deterministic except RAND. See listing page 162.

Table 2-56. Mathematical Functions

Math Function Name

Description and Syntax

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 )

Example:

SQL

SELECT  CEILING(2.13) Ceil , LOG(10) Log , LOG10
                     graphics/ccc.gif(10) Log10 ,
                  PI() Pi , SIN(1) Sine
                  
 

Result


Ceil      Log                               Log10             Pi                              Sine
-----     ------------------------------    --------------    -------------------------    -----------------------------
3         2.3025850929940459                1.0               3.1415926535897931           0.8414709848078965

2.6.6 Metadata Functions

A metadata function returns information about the database and database objects (Table 2-57). All are nondeterministic (see page 163).

Table 2-57. Metadata Functions

Function

Description and Syntax

@@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 )

2.6.7 Rowset Functions

A rowset is a set of rows that contain columns of data and can be used like a table in SQL. Rowsets are central objects that enable all OLE DB data providers to expose result set data in tabular form. Rowset Functions return rowsets (Table 2-58). All are nondeterministic (see page 163).

A rowset is a set of rows that contain columns of data. Rowsets are central objects that enable all OLE DB data providers to expose result set data in tabular form.

Table 2-58. Rowset Functions

Rowset Function Name

Description and Syntax

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

Example: Use OPENQUERY — requires a link be made to remote server. See page 66.

SQL

SELECT  TOP 2   *
                  FROM  OPENQUERY( CAT2_Link , 'SELECT *   FROM pubs
                     graphics/ccc.gif.dbo.authors')
                  
 

Result

au_id           au_lname        au_fname        phone                address                    city               state
-----------     -------------   -------------   ------------------   ------------------------   ----------------   ------
172-32-1176     White           Johnson         408 496-7223         10932 Bigge Rd.            Menlo Park         CA
213-46-8915     Green           Marjorie        415 986-7020         309 63rd St. #411          Oakland            CA

This example uses the link CAT2_Link created on page 47. That version is repeated below as it seems more direct than the OPENQUERY method above.

SQL

SELECT  TOP 2  *   FROM  CAT2_Link.pubs.dbo.authors
                        
 

Result

Same result as previous.

SQL

SELECT  TOP 2  *   FROM  CAT2_Link.pubs.dbo.authors
                  
 

Result

Same result as previous.

Example: Use OPENROWSET — Does not require that a link be created first.

SQL

SELECT a.*
                  FROM    OPENROWSET('SQLOLEDB','cat\cat2';'sa';'',
                  

SQL

'SELECT  TOP 2 * FROM pubs.dbo.authors ') AS a
                  
 

Result

Same result as previous.

Example: Use OPENXML

Create an xml document with sp_xml_preparedocument, then read it with OPENXML.

Create an internal representation of an XML data document and assign the document handle value to the idoc handle variable so it can be passed in to OPENXML to read.

DECLARE @idoc int -- Declare an int variable for the xml document handle

          EXEC sp_xml_preparedocument @idoc OUTPUT,
          '<ROOT>
          <Customer >
              <CustomerID>12</CustomerID>
              <Name>Amy Darling</Name>
              <Telephone>111-2222</Telephone>
          </Customer>
          <Customer >
              <CustomerID>36</CustomerID>
              <Name> Torie Dearest </Name>
              <Telephone>333-4444</Telephone>
          </Customer>
          </ROOT>'

Do a query using OPENXML to read the desired parts of the XML data document.

SQL

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

Result

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

(2 row(s) affected)

With the XML data thus available at a virtual table in SQL, it may be inserted into a database table as in this example run in the same batch as the previous statements.

SQL

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

Result

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

(2 row(s) affected)

2.6.8 Security Functions

A security function returns information about users and roles. All are nondeterministic (see Table 2-59 and page 162).

Table 2-59. Security Functions

Security Function Name

Description and Syntax

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.

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

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

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

USER_ID

Returns a user's database identification number.

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

2.6.9 String Functions — for CHAR or VARCHAR expressions

A string function for CHAR or VARCHAR expressions performs an operation on a string input value and returns a string or numeric value (Table 2-60). All are deterministic except CHARINDEX and PATINDEX (see page 162).

Table 2-60. String Functions

String Fcn

Description and Syntax

ASCII

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

SyntaxASCII ( 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.

SyntaxCHAR ( 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.

SyntaxLEFT ( 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.

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

LOWER

Returns the character_expression string in all lower case.

SyntaxLOWER ( 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.

SyntaxREPLACE ( '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 )

2.6.10 System Functions

System functions, listed in Table 2-61, perform operations and return information about values, objects and settings in Microsoft SQL Server. Some are deterministic and some are not. See list page 162.

Table 2-61. System Functions

System Function Name

Description and Syntax

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

SyntaxFORMATMESSAGE ( 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.

SyntaxIDENTITY ( 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

SyntaxISNULL ( 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.

SyntaxSYSTEM_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 ] )

2.6.11 System Statistical Functions

A system statistical function, returns statistical information about the system. See Table 2-62 for details. All are nondeterministic (see page 162).

Table 2-62. System Statistical Functions

Function Name

Description and Syntax

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

Syntaxfn_virtualfilestats ( [@DatabaseID=]database_id
        , [ @FileID = ] file_id )

2.6.12 Text and Image Functions and Statements

Text and image functions, listed in Table 2-63, perform an operation on a text or image input value or column and return information about the value. All are nondeterministic (see page 162).

Table 2-63. Text and Image Functions

Function Name

Description and Syntax

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 )

See Text examples on page 109.

Text and image statements are summarized in Table 2-64.

Table 2-64. Text and Image Statements

Statement Name

Description and Syntax

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.

SyntaxWRITETEXT {table.column text_ptr } [ WITH LOG
] {data }

2.6.13 Deterministic and Nondeterministic Functions

All functions are either deterministic or nondeterministic. Deterministic functions always return the same result any time they are called with the same input values. For example, ABS(-2) always returns 2. Nondeterministic functions may return different results each time they are called even though input values are the same. For example, GETDATE() returns a different result each time it's called.

Indexed views or indexes on computed columns cannot include nondeterministic functions. An index cannot be created on a view which references any nondeterministic functions. An index cannot be created on a computed column if the computed_column_expression references any nondeterministic functions.

2.6.13.1 Listing of Deterministic and Nondeterministic Functions

Aggregate built-in functions (page 139) are all deterministic. String built-in functions (page 154) are all deterministic except CHARINDEX and PATINDEX. Tables 2-65 thorugh 2-68 identify characteristics of many functions.

Always Deterministic

The functions in Table 2-65 are always deterministic.

Table 2-65. Deterministic Functions

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

The System Functions listed in Table 2-66 are deterministic.

Table 2-66. Deterministic System Functions

CASE expression

COALESCE

DATALENGTH

fn_helpcollations

ISNULL

ISNUMERIC

NULLIF

PARSENAME

   

Sometimes Deterministic

These functions, listed in Table 2-67, are not always deterministic but can be used in indexed views or indexes on computed columns when they are specified in a deterministic manner.

Table 2-67. Sometimes Deterministic Functions

Function

Comments

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.

Never Deterministic

The System and Built-in Functions in Table 2-68 are always nondeterministic.

Table 2-68. Nondeterministic Functions

@@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

 

As discussed earlier, all configuration, cursor, meta data, rowset, security, and system statistical functions are nondeterministic. Functions that call extended stored procedures are nondeterministic because the extended stored procedures can cause side effects on the database.

2.6.14 CASE Expression

CASE can be considered an expression or a function because it evaluates to a single scalar value of the same data type as the input expression. CASE has two formats: simple CASE and searched CASE.

Simple CASE compares the input expression to a series of simple expressions.

CASE input-expression WHEN match-expression THEN result
   [ WHEN match-expression THEN result ]
   ...
   [ELSE result]
   END
   

Searched CASE evaluates a series of Boolean expressions to determine the result.

CASE               WHEN Boolean-condition THEN result
   [ WHEN Boolean-condition THEN result ]
   ...
   [ELSE result2]
   END
   

2.6.14.1 Example of Simple CASE

Consider Table 2-69, which has a column containing a car manufacturer abbreviation.

Table 2-69. Autos

Make

Manufacturer

...

...

Buick

GM

   

Quattro

Au

 

Jeep

DC

   

Sebring

DC

   

The following query uses CASE to convert the manufacturer abbreviation to the full name.

SQL

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;
               
 

Result

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

2.6.14.2 Example of Searched CASE

This form of CASE can be used for inequalities, as in the example, as well as equalities. Consider Table 2-70, for which we want to do a query that assigns letter grades.

Table 2-70. Grades

Student

Grade

Major

...

Torie

87

Counselling

 

James

76

Dog Husbandry

 

Amy

93

Tae Kwon Do

Tim

82

Jet Skiing

 

Ina

98

Flower Gardening

 

SQL

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;
               
 

Result

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

2.6.15 CAST and CONVERT

Both CAST and CONVERT functions are used to explicitly convert a value from one data type to another data type. CAST and CONVERT provide similar functionality but only CAST complies with ANSI SQL-92 and -99. CAST and CONVERT may be used anywhere a scalar valued expression may occur in an SQL statement.

CAST Syntax

CAST ( expression AS datatype )

CONVERT Syntax

CONVERT (data_type[(length)], expression [, style])

Example: Error

SQL

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;
                  
 

Result

Server: Msg 241, Level 16, State 1, Line 2

Syntax error converting datetime from character string.

 

SQL

SELECT  'Today is ' + GETDATE()   -- Error,
                     graphics/ccc.gif incompatible data types
                  

CAST Example: CAST is ANSI standard which makes it more portable than CONVERT.

SQL

SELECT  'Today is ' + CAST( GETDATE() AS CHAR ) --
                     graphics/ccc.gif Okay
                  
 

Result

--------------------------------------------------
Today is Sep  2 2001  2:14PM.

CONVERT Example:

SQL

SELECT  'Today is ' + CONVERT( CHAR , GETDATE() ) 
                     graphics/ccc.gif -- Okay
                  
 

Result

--------------------------------------------------
Today is Sep  2 2001  2:14PM.

  • + Share This
  • 🔖 Save To Your Account