SALES: 1-800-867-1380
11 out of 16 rated this helpful - Rate this topic

Functions (Azure SQL Database)

Updated: February 17, 2014

This topic is OBSOLETE. You can find the most current version in the SQL 14 Transact-SQL Reference.

Microsoft Microsoft Azure SQL Database provides support for many SQL Server built-in functions. This topic describes the supported and unsupported built-in functions.   

The following table lists the function categories and also describes the support for built-in functions:

 

Function Category Description Microsoft Azure SQL Database Support

Aggregate Functions

Operate on a collection of values but return a single, summarizing value.

Yes

Ranking Functions

Return a ranking value for each row in a partition.

Yes

Rowset Functions

Return an object that can be used like table references in an SQL statement.

No

Scalar Functions

Operate on a single value and return a single value. Scalar functions can be used wherever an expression is valid.

Yes but not all the scalar functions are supported by Microsoft Azure SQL Database. For more information, see Scalar Functions.

ODBC Scalar Functions

Functions specified by ODBC. They can be used in stored procedures.

Yes

ImportantImportant
For more information about Transact-SQL grammar, function usage, and examples, see Functions (Transact-SQL) in SQL Server Books Online.

Aggregate Functions

Aggregate functions perform a calculation on a set of values and return a single value. The following table lists the aggregate functions that Microsoft Azure SQL Database supports.

 

AVG

COUNT_BIG

SUM

BINARY_CHECKSUM

GROUPING

STDEV

CHECKSUM

GROUPING_ID

STDEVP

CHECKSUM_AGG

MAX

VAR

COUNT

MIN

VARP

Ranking Functions

Ranking functions return a ranking value for each row in a partition. The following table lists the ranking functions that Microsoft Azure SQL Database supports.

 

DENSE_RANK

RANK

NTILE

ROW_NUMBER

Rowset Functions

The following rowset functions return an object that can be used in place of a table reference in a Transact-SQL statement. The following table lists the rowset functions that Microsoft Azure SQL Database does not support.

 

CONTAINSTABLE

OPENDATASOURCE

OPENROWSET

FREETEXTTABLE

OPENQUERY

OPENXML

Scalar Functions

The following table lists the categories of scalar functions and also describes the support provided by Microsoft Azure SQL Database.

 

Function category Description Microsoft Azure SQL Database Support

Configuration Functions

Return information about the current configuration.

Partial support is available.

Conversion Functions

Support data type casting and converting.

Yes

Cursor Functions

Return information about cursors.

Yes

Date and Time Functions

Perform operations on a date and time input values and return string, numeric, or date and time values.

Partial support is available.

Logical Functions

Perform logical operations.

Yes

Mathematical Functions

Perform calculations based on input values provided as parameters to the functions, and return numeric values.

Yes

Metadata Functions

Return information about the database and database objects.

Partial support is available.

Security Functions

Return information about users and roles.

Partial support is available.

String Functions

Perform operations on a string (char or varchar) input value and return a string or numeric value.

Yes

System Functions

Perform operations and return information about values, objects, and settings in a database.

Partial support is available.

System Statistical Functions

Return statistical information about the system.

No

Text and Image Functions

Perform operations on text or image input values or columns, and return information about the value.

Partial support is available.

Configuration Functions

The following table lists the configuration functions that Microsoft Azure SQL Database supports.

 

@@DATEFIRST

@@MAX_PRECISION

@@TEXTSIZE

@@DBTS

@@NESTLEVEL

@@VERSION (Azure SQL Database)

@@LANGID

@@OPTIONS

FEDERATION_FILTERING_VALUE (Azure SQL Database)

@@LANGUAGE

@@SERVERNAME

@@LOCK_TIMEOUT

@@SPID

Microsoft Azure SQL Database does not support the following configuration functions: CONNECTIONPROPERTY, @@MAX_CONNECTIONS, @@REMSERVER, @@SERVICENAME.

Conversion Functions

The following table lists the conversion functions that Microsoft Azure SQL Database supports. These conversion functions support data type casting and converting.

 

PARSE

TRY_CAST

TRY_PARSE

Cursor Functions

The following table lists the cursor functions that Microsoft Azure SQL Database supports. These scalar functions return information about cursors.

 

@@CURSOR_ROWS

@@FETCH_STATUS

CURSOR_STATUS

Date and Time Functions

The following table lists the date and time functions that Microsoft Azure SQL Database supports:

 

@@DATEFIRST

DATETIMEFROMPARTS

SWITCHOFFSET

@@LANGUAGE

DATETIMEOFFSETFROMPARTS

SYSDATETIME

CURRENT_TIMESTAMP

DAY

SYSDATETIMEOFFSET

DATEADD

EOMONTH

SYSUTCDATETIME

DATEDIFF

GETDATE

TIMEFROMPARTS

DATEFROMPARTS

GETUTCDATE

TODATETIMEOFFSET

DATENAME

ISDATE

YEAR

DATEPART

MONTH

DATETIME2FROMPARTS

SMALLDATETIMEFROMPARTS

Logical Functions

The following table lists the logical functions that Microsoft Azure SQL Database supports. These scalar functions perform logical operations.

 

IIF

CHOOSE

Mathematical Functions

The following scalar functions perform a calculation, usually based on input values that are provided as arguments, and return a numeric value. The following table lists the mathematical functions that Microsoft Azure SQL Database supports:

 

ABS

DEGREES

RAND

ACOS

EXP

ROUND

ASIN

FLOOR

SIGN

ATAN

LOG

SIN

ATN2

LOG10

SQRT

CEILING

PI

SQUARE

COS

POWER

TAN

COT

RADIANS

Metadata Functions

The following scalar functions return information about the database and database objects. The following table lists the metadata functions that Microsoft Azure SQL Database supports:

 

@@PROCID

INDEX_COL

SCHEMA_ID

COL_LENGTH

INDEXPROPERTY

SCHEMA_NAME

COL_NAME

OBJECT_DEFINITION

SQL_VARIANT_PROPERTY

COLUMNPROPERTY

OBJECT_ID

TYPE_ID

DATABASE_PRINCIPAL_ID

OBJECT_NAME

TYPE_NAME

DATABASEPROPERTYEX (Azure SQL Database)

OBJECT_SCHEMA_NAME

TYPEPROPERTY

DB_ID

OBJECTPROPERTY

DB_NAME

OBJECTPROPERTYEX

The following table lists the metadata functions that Microsoft Azure SQL Database does not support:

 

ASSEMBLYPROPERTY

FILE_NAME

FULLTEXTSERVICEPROPERTY

ASYMKEY_ID

FILEGROUP_ID

INDEXKEY_PROPERTY

ASYMKEYPROPERTY

FILEGROUP_NAME

Key_GUID

Cert_ID

FILEGROUPPROPERTY

Key_ID

DATABASEPROPERTY

FILEPROPERTY

KEY_NAME

FILE_ID

fn_listextendedproperty

SYMKEYPROPERTY

FILE_IDEX

FULLTEXTCATALOGPROPERTY

Security Functions

The following functions return information that is useful in managing security. The following table lists the security functions that Microsoft Azure SQL Database supports:

 

CURRENT_USER

SESSION_USER

USER_NAME

HAS_PERMS_BY_NAME (Azure SQL Database)

SUSER_SID (Azure SQL Database)

SUSER_SNAME (Azure SQL Database)

IS_MEMBER (Azure SQL Database)

USER

IS_SRVROLEMEMBER (Azure SQL Database)

USER_ID

The following table lists the security functions that Microsoft Azure SQL Database does not support:

 

fn_Trace_Geteventinfo

fn_my_permissions

SUSER_NAME

fn_Trace_Getfilterinfo

PERMISSIONS

SYSTEM_USER

fn_Trace_Getinfo

SETUSER

sys.fn_builtin_permissions

fn_Trace_Gettable

SUSER_ID

String Functions

The following scalar functions perform an operation on a string input value and return a string or numeric value. The following table lists the string functions that Microsoft Azure SQL Database supports:

 

ASCII

LTRIM

SOUNDEX

CHAR

NCHAR

SPACE

CHARINDEX

PATINDEX

STR

COMCAT

QUOTENAME

STUFF

DIFFERENCE

REPLACE

SUBSTRING

FORMAT

REPLICATE

UNICODE

LEFT

REVERSE

UPPER

LEN

RIGHT

LOWER

RTRIM

System Functions

The following table lists the system functions that Microsoft Azure SQL Database supports:

 

APP_NAME

ERROR_PROCEDURE

NULLIF

CASE

ERROR_SEVERITY

PARSENAME

CAST AND CONVERT

ERROR_STATE

@@ROWCOUNT

COALESCE

FORMATMESSAGE

ROWCOUNT_BIG

COLLATIONPROPERTY

GETANSINULL

SCOPE_IDENTITY

COLUMNS_UPDATED

Fn_helpcollations

SERVERPROPERTY (Azure SQL Database)

CONVERT

IDENT_CURRENT

SESSIONPROPERTY

CURRENT_TIMESTAMP

IDENT_INCR

SESSION_USER

CURRENT_USER

IDENT_SEED

STATS_DATE

DATALENGTH

@@IDENTITY

@@TRANCOUNT

@@ERROR

ISDATE

UPDATE()

ERROR_LINE

ISNULL

USER_NAME

ERROR_MESSAGE

ISNUMERIC

XACT_STATE

ERROR_NUMBER

NEWID

Microsoft Azure SQL Database does not support the following system functions:

 

fn_servershareddrives

HOST_ID

SYSTEM_USER

fn_virtualfilestats

HOST_NAME

sys.dm_db_index_physical_stats

fn_virtualservernodes

IDENTITY

fn_indexinfo

ORIGINAL_LOGIN

System Statistical Functions

System statistical functions return statistical information about the system. Microsoft Azure SQL Database does not support the following system functions:

 

@@CONNECTIONS

@@PACKET_ERRORS

@@TOTAL_ERRORS

@@CPU_BUSY

@@PACK_RECEIVED

@@TOTAL_READ

@@IDLE

@@PACK_SENT

@@TOTAL_WRITE

@@IO_BUSY

@@TIMETICKS

fn_virtualfilestats

Text and Image Functions

The text and image functions perform an operation on a text or image input value or column and return information about the value. In this category, Microsoft Azure SQL Database supports only the PATINDEX function.

Note that Microsoft Azure SQL Database does not support the following text and image functions: TEXTPTR, TEXTVALID.

ODBC Scalar Functions

The following table lists the categories of ODBC scalar functions and also describes the support provided by Microsoft Azure SQL Database.

 

ODBC Scalar Function Category Microsoft Azure SQL Database Support

ODBC String Functions

Yes

ODBC Numeric Function

Yes

ODBC Time, Date, and Interval Functions

Yes

ImportantImportant
For more information about ODBC scalar function usage and examples, see ODBC Scalar Functions (Transact-SQL) in SQL Server Books Online.

ODBC String Functions

 

BIT_LENGTH( string_exp ) (ODBC 3.0)

CONCAT( string_exp1,string_exp2) (ODBC 1.0)

OCTET_LENGTH( string_exp ) (ODBC 3.0)

ODBC Numeric Function

 

TRUNCATE( numeric_exp, integer_exp) (ODBC 2.0)

ODBC Time, Date, and Interval Functions

 

CURRENT_DATE( ) (ODBC 3.0)

CURRENT_TIME[( time-precision )] (ODBC 3.0)

DAYNAME( date_exp ) (ODBC 2.0)

DAYOFMONTH( date_exp ) (ODBC 1.0)

DAYOFWEEK( date_exp ) (ODBC 1.0)

HOUR( time_exp ) (ODBC 1.0)

MINUTE( time_exp ) (ODBC 1.0)

MONTHNAME( date_exp ) (ODBC 2.0)

QUARTER( date_exp ) (ODBC 1.0)

WEEK( date_exp ) (ODBC 1.0)

See Also

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.