List of SQL Functions on SQL Server 2016 for Transact-SQL Database Developer
SQL Server 2016 tutorial prepared for database developers provides a full list of SQL functions functions new and existing functions. While building the list of Transact-SQL functions for SQL database developers, I also added the official syntax pages from Microsoft SQL Server documentation for each of SQL functions. I also added SQL tutorials that can help SQL database programmers with sample codes related with the SQL functions listed in this tutorial.
New SQL Functions introduced with SQL Server 2016
SQL Server 2016 has introduced a number of SQL functions for Transact-SQL database developers. Especially SQL JSON functions will be very useful for database application developers.
String_Split function is also introduced with SQL Server 2016. Before SQL Server 2016, SQL developers were using custom user-defined functions to split string on SQL Server databases.
Here is the full list of new SQL functions on SQL Server 2016.
DATEDIFF_BIG
ISJSON
JSON_MODIFY
JSON_QUERY
JSON_VALUE
OPENJSON
STRING_ESCAPE
STRING_SPLIT
COMPRESS
CURRENT_TRANSACTION_ID
DECOMPRESS
HOST_NAME
SESSION_CONTEXT
SQL Server 2016 Transact-SQL Functions List
| Function Type | SQL Function Name | Official Document | SQL Tutorials |
| Aggregate Function | AVG | AVG | |
| Aggregate Function | CHECKSUM_AGG | CHECKSUM_AGG | |
| Aggregate Function | COUNT | COUNT | SQL Count Function with Partition By Clause |
| Aggregate Function | COUNT_BIG | COUNT_BIG | |
| Aggregate Function | GROUPING | GROUPING | |
| Aggregate Function | GROUPING_ID | GROUPING_ID | |
| Aggregate Function | MAX | MAX | |
| Aggregate Function | MIN | MIN | |
| Aggregate Function | STDEV | STDEV | |
| Aggregate Function | STDEVP | STDEVP | |
| Aggregate Function | SUM | SUM | |
| Aggregate Function | VAR | VAR | |
| Aggregate Function | VARP | VARP | |
| Analytic Function | CUME_DIST | CUME_DIST | |
| Analytic Function | FIRST_VALUE | FIRST_VALUE | First_Value SQL Analytic Function in SQL Server |
| Analytic Function | LAG | LAG | SQL LAG() Function in SQL Server 2012 for Calculating Previous Value |
| Analytic Function | LAST_VALUE | LAST_VALUE | Last_Value SQL Analytic Function in SQL Server |
| Analytic Function | LEAD | LEAD | SQL LEAD Function in SQL Server 2012 to Calculate Next Value |
| Analytic Function | PERCENT_RANK | PERCENT_RANK | |
| Analytic Function | PERCENTILE_CONT | PERCENTILE_CONT | |
| Analytic Function | PERCENTILE_DISC | PERCENTILE_DISC | |
| Collation Function | COLLATIONPROPERTY | COLLATIONPROPERTY | |
| Collation Function | TERTIARY_WEIGHTS | TERTIARY_WEIGHTS | |
| Configuration Function | @@DATEFIRST | @@DATEFIRST | |
| Configuration Function | @@DBTS | @@DBTS | |
| Configuration Function | @@LANGID | @@LANGID | |
| Configuration Function | @@LANGUAGE | @@LANGUAGE | |
| Configuration Function | @@LOCK_TIMEOUT | @@LOCK_TIMEOUT | |
| Configuration Function | @@MAX_CONNECTIONS | @@MAX_CONNECTIONS | |
| Configuration Function | @@MAX_PRECISION | @@MAX_PRECISION | |
| Configuration Function | @@NESTLEVEL | @@NESTLEVEL | |
| Configuration Function | @@OPTIONS | @@OPTIONS | |
| Configuration Function | @@REMSERVER | @@REMSERVER | |
| Configuration Function | @@SERVERNAME | @@SERVERNAME | |
| Configuration Function | @@SERVICENAME | @@SERVICENAME | |
| Configuration Function | @@SPID | @@SPID | |
| Configuration Function | @@TEXTSIZE | @@TEXTSIZE | |
| Configuration Function | @@VERSION | @@VERSION | |
| Conversion Function | CAST and CONVERT | CAST and CONVERT | |
| Conversion Function | PARSE | PARSE | |
| Conversion Function | TRY_CAST | TRY_CAST | |
| Conversion Function | TRY_CONVERT | TRY_CONVERT | |
| Conversion Function | TRY_PARSE | TRY_PARSE | |
| Cryptographic function | ASYMKEY_ID | ASYMKEY_ID | |
| Cryptographic function | ASYMKEYPROPERTY | ASYMKEYPROPERTY | |
| Cryptographic function | CERTENCODED | CERTENCODED | |
| Cryptographic function | CERTPRIVATEKEY | CERTPRIVATEKEY | |
| Cryptographic function | DECRYPTBYASYMKEY | DECRYPTBYASYMKEY | |
| Cryptographic function | DECRYPTBYCERT | DECRYPTBYCERT | |
| Cryptographic function | DECRYPTBYKEY | DECRYPTBYKEY | |
| Cryptographic function | DECRYPTBYKEYAUTOASYMKEY | DECRYPTBYKEYAUTOASYMKEY | |
| Cryptographic function | DecryptByKeyAutoCert | DecryptByKeyAutoCert | |
| Cryptographic function | DECRYPTBYPASSPHRASE | DECRYPTBYPASSPHRASE | |
| Cryptographic function | ENCRYPTBYASYMKEY | ENCRYPTBYASYMKEY | |
| Cryptographic function | ENCRYPTBYCERT | ENCRYPTBYCERT | |
| Cryptographic function | ENCRYPTBYKEY | ENCRYPTBYKEY | |
| Cryptographic function | ENCRYPTBYPASSPHRASE | ENCRYPTBYPASSPHRASE | |
| Cryptographic function | HASHBYTES | HASHBYTES | |
| Cryptographic function | IS_OBJECTSIGNED | IS_OBJECTSIGNED | |
| Cryptographic function | KEY_GUID | KEY_GUID | |
| Cryptographic function | KEY_ID | KEY_ID | |
| Cryptographic function | KEY_NAME | KEY_NAME | |
| Cryptographic function | SIGNBYASYMKEY | SIGNBYASYMKEY | |
| Cryptographic function | SIGNBYCERT | SIGNBYCERT | |
| Cryptographic function | SYMKEYPROPERTY | SYMKEYPROPERTY | |
| Cryptographic function | VERIFYSIGNEDBYASMKEY | VERIFYSIGNEDBYASMKEY | |
| Cryptographic function | VERIGYSIGNEDBYCERT | VERIGYSIGNEDBYCERT | |
| Cursor Function | @@CURSOR_ROWS | @@CURSOR_ROWS | |
| Cursor Function | @@FETCH_STATUS | @@FETCH_STATUS | |
| Cursor Function | CURSOR_STATUS | CURSOR_STATUS | |
| Data Type Function | DATALENGTH | DATALENGTH | |
| Data Type Function | IDENT_CURRENT | IDENT_CURRENT | |
| Data Type Function | IDENT_INCR | IDENT_INCR | |
| Data Type Function | IDENT_SEED | IDENT_SEED | |
| Data Type Function | IDENTITY (Function) | IDENTITY (Function) | |
| Data Type Function | SQL_VARIANT_PROPERTY | SQL_VARIANT_PROPERTY | |
| Date & Time Function | @@DATEFIRST | @@DATEFIRST | |
| Date & Time Function | @@LANGUAGE | @@LANGUAGE | |
| Date & Time Function | CURRENT_TIMESTAMP | CURRENT_TIMESTAMP | |
| Date & Time Function | DATEADD | DATEADD | |
| Date & Time Function | DATEDIFF | DATEDIFF | |
| Date & Time Function | DATEDIFF_BIG | DATEDIFF_BIG | |
| Date & Time Function | DATEFROMPARTS | DATEFROMPARTS | |
| Date & Time Function | DATENAME | DATENAME | |
| Date & Time Function | DATEPART | DATEPART | |
| Date & Time Function | DATETIME2FROMPARTS | DATETIME2FROMPARTS | |
| Date & Time Function | DATETIMEFROMPARTS | DATETIMEFROMPARTS | |
| Date & Time Function | DATETIMEOFFSETFROMPARTS | DATETIMEOFFSETFROMPARTS | |
| Date & Time Function | DAY | DAY | |
| Date & Time Function | EOMONTH | EOMONTH | SQL Server EOMonth End of Month Function to Calculate Last Day of Month |
| Date & Time Function | GETDATE | GETDATE | |
| Date & Time Function | GETUTCDATE | GETUTCDATE | |
| Date & Time Function | ISDATE | ISDATE | |
| Date & Time Function | MONTH | MONTH | |
| Date & Time Function | SET DATEFIRST | SET DATEFIRST | |
| Date & Time Function | SET DATEFORMAT | SET DATEFORMAT | |
| Date & Time Function | SET LANGUAGE | SET LANGUAGE | |
| Date & Time Function | SMALLDATETIMEFROMPARTS | SMALLDATETIMEFROMPARTS | |
| Date & Time Function | sp_helplanguage | sp_helplanguage | |
| Date & Time Function | SWITCHOFFSET | SWITCHOFFSET | |
| Date & Time Function | SYSDATETIME | SYSDATETIME | |
| Date & Time Function | SYSDATETIMEOFFSET | SYSDATETIMEOFFSET | |
| Date & Time Function | SYSUTCDATETIME | SYSUTCDATETIME | |
| Date & Time Function | TIMEFROMPARTS | TIMEFROMPARTS | |
| Date & Time Function | TODATETIMEOFFSET | TODATETIMEOFFSET | |
| Date & Time Function | YEAR | YEAR | |
| JSON Function | ISJSON | ISJSON | |
| JSON Function | JSON_MODIFY | JSON_MODIFY | |
| JSON Function | JSON_QUERY | JSON_QUERY | |
| JSON Function | JSON_VALUE | JSON_VALUE | |
| Logical Function | CHOOSE | CHOOSE | SQL Choose() Function in SQL Server |
| Logical Function | IIF | IIF | SQL IIF Boolean Function in Transact-SQL with SQL Server |
| Mathematical Function | ABS | ABS | |
| Mathematical Function | ACOS | ACOS | |
| Mathematical Function | ASIN | ASIN | |
| Mathematical Function | ATAN | ATAN | |
| Mathematical Function | ATN2 | ATN2 | |
| Mathematical Function | CEILING | CEILING | |
| Mathematical Function | COS | COS | |
| Mathematical Function | COT | COT | |
| Mathematical Function | DEGREES | DEGREES | |
| Mathematical Function | EXP | EXP | |
| Mathematical Function | FLOOR | FLOOR | |
| Mathematical Function | LOG | LOG | |
| Mathematical Function | LOG10 | LOG10 | |
| Mathematical Function | PI | PI | |
| Mathematical Function | POWER | POWER | |
| Mathematical Function | RADIANS | RADIANS | |
| Mathematical Function | RAND | RAND | |
| Mathematical Function | ROUND | ROUND | |
| Mathematical Function | SIGN | SIGN | |
| Mathematical Function | SIN | SIN | |
| Mathematical Function | SQRT | SQRT | |
| Mathematical Function | SQUARE | SQUARE | |
| Mathematical Function | TAN | TAN | |
| Metadata Function | @@PROCID | @@PROCID | |
| Metadata Function | APP_NAME | APP_NAME | |
| Metadata Function | APPLOCK_MODE | APPLOCK_MODE | |
| Metadata Function | APPLOCK_TEST | APPLOCK_TEST | |
| Metadata Function | ASSEMBLYPROPERTY | ASSEMBLYPROPERTY | |
| Metadata Function | COL_LENGTH | COL_LENGTH | |
| Metadata Function | COL_NAME | COL_NAME | |
| Metadata Function | COLUMNPROPERTY | COLUMNPROPERTY | |
| Metadata Function | DATABASE_PRINCIPAL_ID | DATABASE_PRINCIPAL_ID | |
| Metadata Function | DATABASEPROPERTYEX | DATABASEPROPERTYEX | |
| Metadata Function | DB_ID | DB_ID | |
| Metadata Function | DB_NAME | DB_NAME | |
| Metadata Function | FILE_ID | FILE_ID | |
| Metadata Function | FILE_IDEX | FILE_IDEX | |
| Metadata Function | FILE_NAME | FILE_NAME | |
| Metadata Function | FILEGROUP_ID | FILEGROUP_ID | |
| Metadata Function | FILEGROUP_NAME | FILEGROUP_NAME | |
| Metadata Function | FILEGROUPPROPERTY | FILEGROUPPROPERTY | |
| Metadata Function | FILEPROPERTY | FILEPROPERTY | |
| Metadata Function | FULLTEXTCATALOGPROPERTY | FULLTEXTCATALOGPROPERTY | |
| Metadata Function | FULLTEXTSERVICEPROPERTY | FULLTEXTSERVICEPROPERTY | |
| Metadata Function | INDEX_COL | INDEX_COL | |
| Metadata Function | INDEXKEY_PROPERTY | INDEXKEY_PROPERTY | |
| Metadata Function | INDEXPROPERTY | INDEXPROPERTY | |
| Metadata Function | NEXT VALUE FOR | NEXT VALUE FOR | |
| Metadata Function | OBJECT_DEFINITION | OBJECT_DEFINITION | |
| Metadata Function | OBJECT_ID | OBJECT_ID | |
| Metadata Function | OBJECT_NAME | OBJECT_NAME | |
| Metadata Function | OBJECT_SCHEMA_NAME | OBJECT_SCHEMA_NAME | |
| Metadata Function | OBJECTPROPERTY | OBJECTPROPERTY | |
| Metadata Function | OBJECTPROPERTYEX | OBJECTPROPERTYEX | |
| Metadata Function | ORIGINAL_DB_NAME | ORIGINAL_DB_NAME | |
| Metadata Function | PARSENAME | PARSENAME | Split IP Addresses Using ParseName T-SQL Function |
| Metadata Function | SCHEMA_ID | SCHEMA_ID | |
| Metadata Function | SCHEMA_NAME | SCHEMA_NAME | |
| Metadata Function | SCOPE_IDENTITY | SCOPE_IDENTITY | |
| Metadata Function | SERVERPROPERTY | SERVERPROPERTY | |
| Metadata Function | STATS_DATE | STATS_DATE | |
| Metadata Function | TYPE_ID | TYPE_ID | |
| Metadata Function | TYPE_NAME | TYPE_NAME | |
| Metadata Function | TYPEPROPERTY | TYPEPROPERTY | |
| Metadata Function | VERSION | VERSION | |
| Ranking Function | DENSE_RANK | DENSE_RANK | |
| Ranking Function | NTILE | NTILE | |
| Ranking Function | RANK | RANK | |
| Ranking Function | ROW_NUMBER | ROW_NUMBER | SQL Row_Number() Function Example Queries | SQL paging using ROW_NUMBER() SQL Server Function |
| Replication Function | PUBLISHINGSERVERNAME | PUBLISHINGSERVERNAME | |
| Rowset Function | OPENDATASOURCE | OPENDATASOURCE | |
| Rowset Function | OPENJSON | OPENJSON | Query JSON Data using OpenJSON on SQL Server |
| Rowset Function | OPENQUERY | OPENQUERY | Select from Stored Procedure using SQL Function OpenQuery |
| Rowset Function | OPENROWSET | OPENROWSET | |
| Rowset Function | OPENXML | OPENXML | Import XML into SQL Server |
| Security Function | CERTENCODED | CERTENCODED | |
| Security Function | CERTPRIVATEKEY | CERTPRIVATEKEY | |
| Security Function | CURRENT_USER | CURRENT_USER | |
| Security Function | DATABASE_PRINCIPAL_ID | DATABASE_PRINCIPAL_ID | |
| Security Function | HAS_PERMS_BY_NAME | HAS_PERMS_BY_NAME | |
| Security Function | IS_MEMBER | IS_MEMBER | |
| Security Function | IS_ROLEMEMBER | IS_ROLEMEMBER | |
| Security Function | IS_SRVROLEMEMBER | IS_SRVROLEMEMBER | |
| Security Function | LOGINPROPERTY | LOGINPROPERTY | |
| Security Function | ORIGINAL_LOGIN | ORIGINAL_LOGIN | |
| Security Function | PERMISSIONS | PERMISSIONS | |
| Security Function | PWDCOMPARE | PWDCOMPARE | |
| Security Function | PWDENCRYPT | PWDENCRYPT | |
| Security Function | SCHEMA_ID | SCHEMA_ID | |
| Security Function | SCHEMA_NAME | SCHEMA_NAME | |
| Security Function | SESSION_USER | SESSION_USER | |
| Security Function | SUSER_ID | SUSER_ID | |
| Security Function | SUSER_NAME | SUSER_NAME | |
| Security Function | SUSER_SID | SUSER_SID | |
| Security Function | SUSER_SNAME | SUSER_SNAME | |
| Security Function | sys.fn_builtin_permissions | sys.fn_builtin_permissions | |
| Security Function | sys.fn_get_audit_file | sys.fn_get_audit_file | |
| Security Function | sys.fn_my_permissions | sys.fn_my_permissions | |
| Security Function | SYSTEM_USER | SYSTEM_USER | |
| Security Function | USER_ID | USER_ID | |
| Security Function | USER_NAME | USER_NAME | |
| String Function | ASCII | ASCII | |
| String Function | CHAR | CHAR | |
| String Function | CHARINDEX | CHARINDEX | |
| String Function | CONCAT | CONCAT | SQL Concat String Function |
| String Function | DIFFERENCE | DIFFERENCE | |
| String Function | FORMAT | FORMAT | |
| String Function | LEFT | LEFT | |
| String Function | LEN | LEN | |
| String Function | LOWER | LOWER | |
| String Function | LTRIM | LTRIM | |
| String Function | NCHAR | NCHAR | |
| String Function | PATINDEX | PATINDEX | |
| String Function | QUOTENAME | QUOTENAME | |
| String Function | REPLACE | REPLACE | |
| String Function | REPLICATE | REPLICATE | SQL Pad Leading Zeros |
| String Function | REVERSE | REVERSE | |
| String Function | RIGHT | RIGHT | |
| String Function | RTRIM | RTRIM | |
| String Function | SOUNDEX | SOUNDEX | |
| String Function | SPACE | SPACE | |
| String Function | STR | STR | |
| String Function | STRING_ESCAPE | STRING_ESCAPE | |
| String Function | STRING_SPLIT | STRING_SPLIT | |
| String Function | STUFF | STUFF | |
| String Function | SUBSTRING | SUBSTRING | |
| String Function | UNICODE | UNICODE | |
| String Function | UPPER | UPPER | |
| System Function | $PARTITION | $PARTITION | |
| System Function | @@ERROR | @@ERROR | |
| System Function | @@IDENTITY | @@IDENTITY | |
| System Function | @@PACK_RECEIVED | @@PACK_RECEIVED | |
| System Function | @@ROWCOUNT | @@ROWCOUNT | |
| System Function | @@TRANCOUNT | @@TRANCOUNT | |
| System Function | BINARY_CHECKSUM | BINARY_CHECKSUM | |
| System Function | CHECKSUM | CHECKSUM | |
| System Function | COMPRESS | COMPRESS | |
| System Function | CONNECTIONPROPERTY | CONNECTIONPROPERTY | |
| System Function | CONTEXT_INFO | CONTEXT_INFO | |
| System Function | CURRENT_REQUEST_ID | CURRENT_REQUEST_ID | |
| System Function | CURRENT_TRANSACTION_ID | CURRENT_TRANSACTION_ID | |
| System Function | DECOMPRESS | DECOMPRESS | |
| System Function | ERROR_LINE | ERROR_LINE | |
| System Function | ERROR_MESSAGE | ERROR_MESSAGE | |
| System Function | ERROR_NUMBER | ERROR_NUMBER | |
| System Function | ERROR_PROCEDURE | ERROR_PROCEDURE | |
| System Function | ERROR_SEVERITY | ERROR_SEVERITY | |
| System Function | ERROR_STATE | ERROR_STATE | |
| System Function | FORMATMESSAGE | FORMATMESSAGE | |
| System Function | GET_FILESTREAM_TRANSACTION_CONTEXT | GET_FILESTREAM_TRANSACTION_CONTEXT | |
| System Function | GETANSINULL | GETANSINULL | |
| System Function | HOST_ID | HOST_ID | |
| System Function | HOST_NAME | HOST_NAME | |
| System Function | ISNULL | ISNULL | |
| System Function | ISNUMERIC | ISNUMERIC | |
| System Function | MIN_ACTIVE_ROWVERSION | MIN_ACTIVE_ROWVERSION | |
| System Function | NEWID | NEWID | Use SQL NEWID in SQL Functions as SQL Random Generator |
| System Function | NEWSEQUENTIALID | NEWSEQUENTIALID | |
| System Function | ROWCOUNT_BIG | ROWCOUNT_BIG | |
| System Function | SESSION_CONTEXT | SESSION_CONTEXT | |
| System Function | SESSION_ID | SESSION_ID | |
| System Function | XACT_STATE | XACT_STATE | |
| System Statistical Function | @@CONNECTIONS | @@CONNECTIONS | |
| System Statistical Function | @@CPU_BUSY | @@CPU_BUSY | |
| System Statistical Function | @@IDLE | @@IDLE | |
| System Statistical Function | @@IO_BUSY | @@IO_BUSY | |
| System Statistical Function | @@PACK_RECEIVED | @@PACK_RECEIVED | |
| System Statistical Function | @@PACK_SENT | @@PACK_SENT | |
| System Statistical Function | @@PACKET_ERRORS | @@PACKET_ERRORS | |
| System Statistical Function | @@TIMETICKS | @@TIMETICKS | |
| System Statistical Function | @@TOTAL_ERRORS | @@TOTAL_ERRORS | |
| System Statistical Function | @@TOTAL_READ | @@TOTAL_READ | |
| System Statistical Function | @@TOTAL_WRITE | @@TOTAL_WRITE | |
| System Statistical Function | fn_virtualfilestats | fn_virtualfilestats | |
| Text and Image Function | TEXTPTR | TEXTPTR | |
| Text and Image Function | TEXTVALID | TEXTVALID | |
| Trigger Function | COLUMNS_UPDATED | COLUMNS_UPDATED | |
| Trigger Function | EVENTDATA | EVENTDATA | Prevent DROP Table using SQL Server DDL Trigger | Log Who Drops Table in SQL Server Database with DDL Trigger |
| Trigger Function | TRIGGER_NESTLEVEL | TRIGGER_NESTLEVEL | |
| Trigger Function | UPDATE() | UPDATE() |