Self - Learning: Functions (SQL Azure Database)

http://msdn.microsoft.com/en-us/library/windowsazure/ee336248.aspx

Microsoft SQL Azure 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 CategoryDescriptionSQL Azure 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 SQL Azure Database. For more information, see Scalar Functions.

ODBC Scalar Functions

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

Yes

ImportantFor 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 SQL Azure 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 SQL Azure 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 SQL Azure 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 SQL Azure Database.

 

Function categoryDescriptionSQL Azure Database Support

Configuration Functions

Return information about the current configuration.

Partial support is available.

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.

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 SQL Azure Database supports.

 

@@DATEFIRST

@@MAX_PRECISION

@@TEXTSIZE

@@DBTS

@@NESTLEVEL

@@VERSION (SQL Azure Database)

@@LANGID

@@OPTIONS

FEDERATION_FILTERING_VALUE (SQL Azure Database)

@@LANGUAGE

@@SERVERNAME

@@LOCK_TIMEOUT

@@SPID

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

Cursor Functions

The following table lists the cursor functions that SQL Azure 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 SQL Azure Database supports:

 

@@DATEFIRST

DATEPART

SWITCHOFFSET

@@LANGUAGE

DAY

SYSDATETIME

CURRENT_TIMESTAMP

GETDATE

SYSDATETIMEOFFSET

DATEADD

GETUTCDATE

SYSUTCDATETIME

DATEDIFF

ISDATE

TODATETIMEOFFSET

DATENAME

MONTH

YEAR

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 SQL Azure 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 SQL Azure 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 (SQL Azure Database)

OBJECT_SCHEMA_NAME

TYPEPROPERTY

DB_ID

OBJECTPROPERTY

DB_NAME

OBJECTPROPERTYEX

The following table lists the metadata functions that SQL Azure 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 SQL Azure Database supports:

 

CURRENT_USER

SESSION_USER

USER_NAME

HAS_PERMS_BY_NAME (SQL Azure Database)

SUSER_SID (SQL Azure Database)

SUSER_SNAME (SQL Azure Database)

IS_MEMBER (SQL Azure Database)

USER

IS_SRVROLEMEMBER (SQL Azure Database)

USER_ID

The following table lists the security functions that SQL Azure 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 SQL Azure Database supports:

 

ASCII

NCHAR

SOUNDEX

CHAR

PATINDEX

SPACE

CHARINDEX

QUOTENAME

STR

DIFFERENCE

REPLACE

STUFF

LEFT

REPLICATE

SUBSTRING

LEN

REVERSE

UNICODE

LOWER

RIGHT

UPPER

LTRIM

RTRIM

System Functions

The following table lists the system functions that SQL Azure 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 (SQL Azure 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

SQL Azure 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. SQL Azure 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, SQL Azure Database supports only the PATINDEX function.

Note that SQL Azure 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 SQL Azure Database.

 

ODBC Scalar Function CategorySQL Azure Database Support

ODBC String Functions

Yes

ODBC Numeric Function

Yes

ODBC Time, Date, and Interval Functions

Yes

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