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 Category | Description | SQL Azure Database Support |
---|---|---|
Operate on a collection of values but return a single, summarizing value. | Yes | |
Return a ranking value for each row in a partition. | Yes | |
Return an object that can be used like table references in an SQL statement. | No | |
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. | |
Functions specified by ODBC. They can be used in stored procedures. | Yes |
![]() |
---|
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 SQL Azure Database supports.
AVG | COUNT_BIG | SUM |
BINARY_CHECKSUM | GROUPING | STDEV |
CHECKSUM | GROUPING_ID | STDEVP |
CHECKSUM_AGG | VAR | |
COUNT | 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 category | Description | SQL Azure Database Support |
---|---|---|
Return information about the current configuration. | Partial support is available. | |
Return information about cursors. | Yes | |
Perform operations on a date and time input values and return string, numeric, or date and time values. | Partial support is available. | |
Perform calculations based on input values provided as parameters to the functions, and return numeric values. | Yes | |
Return information about the database and database objects. | Partial support is available. | |
Return information about users and roles. | Partial support is available. | |
Perform operations on a string (char or varchar) input value and return a string or numeric value. | Yes | |
Perform operations and return information about values, objects, and settings in a database. | Partial support is available. | |
Return statistical information about the system. | No | |
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 | |
@@LANGID | @@OPTIONS | |
@@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 | 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 |
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 |
USER | ||
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 | |
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 Category | SQL Azure Database Support |
---|---|
Yes | |
Yes | |
Yes |
![]() |
---|
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) |