SQL Server 2017 SQL Functions List for Transact-SQL Developer
SQL Server 2017 tutorial contains SQL Server SQL functions full list for Transact-SQL database programmers. SQL developers will also find official Microsoft SQL Server documentation on each SQL function. I tried to link to SQL tutorials and to sample codes for all T-SQL functions hoping to be useful for database developers.
New SQL Functions in SQL Server 2017
Following SQL functions are introduced for SQL database developer with SQL Server 2017
CONCAT_WS
STRING_AGG
TRANSLATE
TRIM
SQL Functions List on SQL Server 2017 for Database Developer
| 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 | STRING_AGG | STRING_AGG | String Concatenation in SQL Server with String_Agg Function |
| 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 | CONCAT_WS | CONCAT_WS | |
| 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_AGG | STRING_AGG | |
| String Function | STRING_ESCAPE | STRING_ESCAPE | |
| String Function | STRING_SPLIT | STRING_SPLIT | |
| String Function | STUFF | STUFF | |
| String Function | SUBSTRING | SUBSTRING | |
| String Function | TRANSLATE | TRANSLATE | |
| String Function | TRIM | TRIM | |
| 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() |