SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


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.

SQL Server 2016 SQL functions list

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 TypeSQL Function NameOfficial DocumentSQL Tutorials
Aggregate FunctionAVGAVG
Aggregate FunctionCHECKSUM_AGGCHECKSUM_AGG
Aggregate FunctionCOUNTCOUNTSQL Count Function with Partition By Clause
Aggregate FunctionCOUNT_BIGCOUNT_BIG
Aggregate FunctionGROUPINGGROUPING
Aggregate FunctionGROUPING_IDGROUPING_ID
Aggregate FunctionMAXMAX
Aggregate FunctionMINMIN
Aggregate FunctionSTDEVSTDEV
Aggregate FunctionSTDEVPSTDEVP
Aggregate FunctionSUMSUM
Aggregate FunctionVARVAR
Aggregate FunctionVARPVARP
Analytic FunctionCUME_DISTCUME_DIST
Analytic FunctionFIRST_VALUEFIRST_VALUEFirst_Value SQL Analytic Function in SQL Server
Analytic FunctionLAGLAGSQL LAG() Function in SQL Server 2012 for Calculating Previous Value
Analytic FunctionLAST_VALUELAST_VALUELast_Value SQL Analytic Function in SQL Server
Analytic FunctionLEADLEADSQL LEAD Function in SQL Server 2012 to Calculate Next Value
Analytic FunctionPERCENT_RANKPERCENT_RANK
Analytic FunctionPERCENTILE_CONTPERCENTILE_CONT
Analytic FunctionPERCENTILE_DISCPERCENTILE_DISC
Collation FunctionCOLLATIONPROPERTYCOLLATIONPROPERTY
Collation FunctionTERTIARY_WEIGHTSTERTIARY_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 FunctionCAST and CONVERTCAST and CONVERT
Conversion FunctionPARSEPARSE
Conversion FunctionTRY_CASTTRY_CAST
Conversion FunctionTRY_CONVERTTRY_CONVERT
Conversion FunctionTRY_PARSETRY_PARSE
Cryptographic functionASYMKEY_IDASYMKEY_ID
Cryptographic functionASYMKEYPROPERTYASYMKEYPROPERTY
Cryptographic functionCERTENCODEDCERTENCODED
Cryptographic functionCERTPRIVATEKEYCERTPRIVATEKEY
Cryptographic functionDECRYPTBYASYMKEYDECRYPTBYASYMKEY
Cryptographic functionDECRYPTBYCERTDECRYPTBYCERT
Cryptographic functionDECRYPTBYKEYDECRYPTBYKEY
Cryptographic functionDECRYPTBYKEYAUTOASYMKEYDECRYPTBYKEYAUTOASYMKEY
Cryptographic functionDecryptByKeyAutoCertDecryptByKeyAutoCert
Cryptographic functionDECRYPTBYPASSPHRASEDECRYPTBYPASSPHRASE
Cryptographic functionENCRYPTBYASYMKEYENCRYPTBYASYMKEY
Cryptographic functionENCRYPTBYCERTENCRYPTBYCERT
Cryptographic functionENCRYPTBYKEYENCRYPTBYKEY
Cryptographic functionENCRYPTBYPASSPHRASEENCRYPTBYPASSPHRASE
Cryptographic functionHASHBYTESHASHBYTES
Cryptographic functionIS_OBJECTSIGNEDIS_OBJECTSIGNED
Cryptographic functionKEY_GUIDKEY_GUID
Cryptographic functionKEY_IDKEY_ID
Cryptographic functionKEY_NAMEKEY_NAME
Cryptographic functionSIGNBYASYMKEYSIGNBYASYMKEY
Cryptographic functionSIGNBYCERTSIGNBYCERT
Cryptographic functionSYMKEYPROPERTYSYMKEYPROPERTY
Cryptographic functionVERIFYSIGNEDBYASMKEYVERIFYSIGNEDBYASMKEY
Cryptographic functionVERIGYSIGNEDBYCERTVERIGYSIGNEDBYCERT
Cursor Function@@CURSOR_ROWS@@CURSOR_ROWS
Cursor Function@@FETCH_STATUS@@FETCH_STATUS
Cursor FunctionCURSOR_STATUSCURSOR_STATUS
Data Type FunctionDATALENGTHDATALENGTH
Data Type FunctionIDENT_CURRENTIDENT_CURRENT
Data Type FunctionIDENT_INCRIDENT_INCR
Data Type FunctionIDENT_SEEDIDENT_SEED
Data Type FunctionIDENTITY (Function)IDENTITY (Function)
Data Type FunctionSQL_VARIANT_PROPERTYSQL_VARIANT_PROPERTY
Date & Time Function@@DATEFIRST@@DATEFIRST
Date & Time Function@@LANGUAGE@@LANGUAGE
Date & Time FunctionCURRENT_TIMESTAMPCURRENT_TIMESTAMP
Date & Time FunctionDATEADDDATEADD
Date & Time FunctionDATEDIFFDATEDIFF
Date & Time FunctionDATEDIFF_BIGDATEDIFF_BIG
Date & Time FunctionDATEFROMPARTSDATEFROMPARTS
Date & Time FunctionDATENAMEDATENAME
Date & Time FunctionDATEPARTDATEPART
Date & Time FunctionDATETIME2FROMPARTSDATETIME2FROMPARTS
Date & Time FunctionDATETIMEFROMPARTSDATETIMEFROMPARTS
Date & Time FunctionDATETIMEOFFSETFROMPARTSDATETIMEOFFSETFROMPARTS
Date & Time FunctionDAYDAY
Date & Time FunctionEOMONTHEOMONTHSQL Server EOMonth End of Month Function to Calculate Last Day of Month
Date & Time FunctionGETDATEGETDATE
Date & Time FunctionGETUTCDATEGETUTCDATE
Date & Time FunctionISDATEISDATE
Date & Time FunctionMONTHMONTH
Date & Time FunctionSET DATEFIRSTSET DATEFIRST
Date & Time FunctionSET DATEFORMATSET DATEFORMAT
Date & Time FunctionSET LANGUAGESET LANGUAGE
Date & Time FunctionSMALLDATETIMEFROMPARTSSMALLDATETIMEFROMPARTS
Date & Time Functionsp_helplanguagesp_helplanguage
Date & Time FunctionSWITCHOFFSETSWITCHOFFSET
Date & Time FunctionSYSDATETIMESYSDATETIME
Date & Time FunctionSYSDATETIMEOFFSETSYSDATETIMEOFFSET
Date & Time FunctionSYSUTCDATETIMESYSUTCDATETIME
Date & Time FunctionTIMEFROMPARTSTIMEFROMPARTS
Date & Time FunctionTODATETIMEOFFSETTODATETIMEOFFSET
Date & Time FunctionYEARYEAR
JSON FunctionISJSONISJSON
JSON FunctionJSON_MODIFYJSON_MODIFY
JSON FunctionJSON_QUERYJSON_QUERY
JSON FunctionJSON_VALUEJSON_VALUE
Logical FunctionCHOOSECHOOSESQL Choose() Function in SQL Server
Logical FunctionIIFIIFSQL IIF Boolean Function in Transact-SQL with SQL Server
Mathematical FunctionABSABS
Mathematical FunctionACOSACOS
Mathematical FunctionASINASIN
Mathematical FunctionATANATAN
Mathematical FunctionATN2ATN2
Mathematical FunctionCEILINGCEILING
Mathematical FunctionCOSCOS
Mathematical FunctionCOTCOT
Mathematical FunctionDEGREESDEGREES
Mathematical FunctionEXPEXP
Mathematical FunctionFLOORFLOOR
Mathematical FunctionLOGLOG
Mathematical FunctionLOG10LOG10
Mathematical FunctionPIPI
Mathematical FunctionPOWERPOWER
Mathematical FunctionRADIANSRADIANS
Mathematical FunctionRANDRAND
Mathematical FunctionROUNDROUND
Mathematical FunctionSIGNSIGN
Mathematical FunctionSINSIN
Mathematical FunctionSQRTSQRT
Mathematical FunctionSQUARESQUARE
Mathematical FunctionTANTAN
Metadata Function@@PROCID@@PROCID
Metadata FunctionAPP_NAMEAPP_NAME
Metadata FunctionAPPLOCK_MODEAPPLOCK_MODE
Metadata FunctionAPPLOCK_TESTAPPLOCK_TEST
Metadata FunctionASSEMBLYPROPERTYASSEMBLYPROPERTY
Metadata FunctionCOL_LENGTHCOL_LENGTH
Metadata FunctionCOL_NAMECOL_NAME
Metadata FunctionCOLUMNPROPERTYCOLUMNPROPERTY
Metadata FunctionDATABASE_PRINCIPAL_IDDATABASE_PRINCIPAL_ID
Metadata FunctionDATABASEPROPERTYEXDATABASEPROPERTYEX
Metadata FunctionDB_IDDB_ID
Metadata FunctionDB_NAMEDB_NAME
Metadata FunctionFILE_IDFILE_ID
Metadata FunctionFILE_IDEXFILE_IDEX
Metadata FunctionFILE_NAMEFILE_NAME
Metadata FunctionFILEGROUP_IDFILEGROUP_ID
Metadata FunctionFILEGROUP_NAMEFILEGROUP_NAME
Metadata FunctionFILEGROUPPROPERTYFILEGROUPPROPERTY
Metadata FunctionFILEPROPERTYFILEPROPERTY
Metadata FunctionFULLTEXTCATALOGPROPERTYFULLTEXTCATALOGPROPERTY
Metadata FunctionFULLTEXTSERVICEPROPERTYFULLTEXTSERVICEPROPERTY
Metadata FunctionINDEX_COLINDEX_COL
Metadata FunctionINDEXKEY_PROPERTYINDEXKEY_PROPERTY
Metadata FunctionINDEXPROPERTYINDEXPROPERTY
Metadata FunctionNEXT VALUE FORNEXT VALUE FOR
Metadata FunctionOBJECT_DEFINITIONOBJECT_DEFINITION
Metadata FunctionOBJECT_IDOBJECT_ID
Metadata FunctionOBJECT_NAMEOBJECT_NAME
Metadata FunctionOBJECT_SCHEMA_NAMEOBJECT_SCHEMA_NAME
Metadata FunctionOBJECTPROPERTYOBJECTPROPERTY
Metadata FunctionOBJECTPROPERTYEXOBJECTPROPERTYEX
Metadata FunctionORIGINAL_DB_NAMEORIGINAL_DB_NAME
Metadata FunctionPARSENAMEPARSENAMESplit IP Addresses Using ParseName T-SQL Function
Metadata FunctionSCHEMA_IDSCHEMA_ID
Metadata FunctionSCHEMA_NAMESCHEMA_NAME
Metadata FunctionSCOPE_IDENTITYSCOPE_IDENTITY
Metadata FunctionSERVERPROPERTYSERVERPROPERTY
Metadata FunctionSTATS_DATESTATS_DATE
Metadata FunctionTYPE_IDTYPE_ID
Metadata FunctionTYPE_NAMETYPE_NAME
Metadata FunctionTYPEPROPERTYTYPEPROPERTY
Metadata FunctionVERSIONVERSION
Ranking FunctionDENSE_RANKDENSE_RANK
Ranking FunctionNTILENTILE
Ranking FunctionRANKRANK
Ranking FunctionROW_NUMBERROW_NUMBERSQL Row_Number() Function Example Queries | SQL paging using ROW_NUMBER() SQL Server Function
Replication FunctionPUBLISHINGSERVERNAMEPUBLISHINGSERVERNAME
Rowset FunctionOPENDATASOURCEOPENDATASOURCE
Rowset FunctionOPENJSONOPENJSONQuery JSON Data using OpenJSON on SQL Server
Rowset FunctionOPENQUERYOPENQUERYSelect from Stored Procedure using SQL Function OpenQuery
Rowset FunctionOPENROWSETOPENROWSET
Rowset FunctionOPENXMLOPENXMLImport XML into SQL Server
Security FunctionCERTENCODEDCERTENCODED
Security FunctionCERTPRIVATEKEYCERTPRIVATEKEY
Security FunctionCURRENT_USERCURRENT_USER
Security FunctionDATABASE_PRINCIPAL_IDDATABASE_PRINCIPAL_ID
Security FunctionHAS_PERMS_BY_NAMEHAS_PERMS_BY_NAME
Security FunctionIS_MEMBERIS_MEMBER
Security FunctionIS_ROLEMEMBERIS_ROLEMEMBER
Security FunctionIS_SRVROLEMEMBERIS_SRVROLEMEMBER
Security FunctionLOGINPROPERTYLOGINPROPERTY
Security FunctionORIGINAL_LOGINORIGINAL_LOGIN
Security FunctionPERMISSIONSPERMISSIONS
Security FunctionPWDCOMPAREPWDCOMPARE
Security FunctionPWDENCRYPTPWDENCRYPT
Security FunctionSCHEMA_IDSCHEMA_ID
Security FunctionSCHEMA_NAMESCHEMA_NAME
Security FunctionSESSION_USERSESSION_USER
Security FunctionSUSER_IDSUSER_ID
Security FunctionSUSER_NAMESUSER_NAME
Security FunctionSUSER_SIDSUSER_SID
Security FunctionSUSER_SNAMESUSER_SNAME
Security Functionsys.fn_builtin_permissionssys.fn_builtin_permissions
Security Functionsys.fn_get_audit_filesys.fn_get_audit_file
Security Functionsys.fn_my_permissionssys.fn_my_permissions
Security FunctionSYSTEM_USERSYSTEM_USER
Security FunctionUSER_IDUSER_ID
Security FunctionUSER_NAMEUSER_NAME
String FunctionASCIIASCII
String FunctionCHARCHAR
String FunctionCHARINDEXCHARINDEX
String FunctionCONCATCONCATSQL Concat String Function
String FunctionDIFFERENCEDIFFERENCE
String FunctionFORMATFORMAT
String FunctionLEFTLEFT
String FunctionLENLEN
String FunctionLOWERLOWER
String FunctionLTRIMLTRIM
String FunctionNCHARNCHAR
String FunctionPATINDEXPATINDEX
String FunctionQUOTENAMEQUOTENAME
String FunctionREPLACEREPLACE
String FunctionREPLICATEREPLICATESQL Pad Leading Zeros
String FunctionREVERSEREVERSE
String FunctionRIGHTRIGHT
String FunctionRTRIMRTRIM
String FunctionSOUNDEXSOUNDEX
String FunctionSPACESPACE
String FunctionSTRSTR
String FunctionSTRING_ESCAPESTRING_ESCAPE
String FunctionSTRING_SPLITSTRING_SPLIT
String FunctionSTUFFSTUFF
String FunctionSUBSTRINGSUBSTRING
String FunctionUNICODEUNICODE
String FunctionUPPERUPPER
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 FunctionBINARY_CHECKSUMBINARY_CHECKSUM
System FunctionCHECKSUMCHECKSUM
System FunctionCOMPRESSCOMPRESS
System FunctionCONNECTIONPROPERTYCONNECTIONPROPERTY
System FunctionCONTEXT_INFOCONTEXT_INFO
System FunctionCURRENT_REQUEST_IDCURRENT_REQUEST_ID
System FunctionCURRENT_TRANSACTION_IDCURRENT_TRANSACTION_ID
System FunctionDECOMPRESSDECOMPRESS
System FunctionERROR_LINEERROR_LINE
System FunctionERROR_MESSAGEERROR_MESSAGE
System FunctionERROR_NUMBERERROR_NUMBER
System FunctionERROR_PROCEDUREERROR_PROCEDURE
System FunctionERROR_SEVERITYERROR_SEVERITY
System FunctionERROR_STATEERROR_STATE
System FunctionFORMATMESSAGEFORMATMESSAGE
System FunctionGET_FILESTREAM_TRANSACTION_CONTEXTGET_FILESTREAM_TRANSACTION_CONTEXT
System FunctionGETANSINULLGETANSINULL
System FunctionHOST_IDHOST_ID
System FunctionHOST_NAMEHOST_NAME
System FunctionISNULLISNULL
System FunctionISNUMERICISNUMERIC
System FunctionMIN_ACTIVE_ROWVERSIONMIN_ACTIVE_ROWVERSION
System FunctionNEWIDNEWIDUse SQL NEWID in SQL Functions as SQL Random Generator
System FunctionNEWSEQUENTIALIDNEWSEQUENTIALID
System FunctionROWCOUNT_BIGROWCOUNT_BIG
System FunctionSESSION_CONTEXTSESSION_CONTEXT
System FunctionSESSION_IDSESSION_ID
System FunctionXACT_STATEXACT_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 Functionfn_virtualfilestatsfn_virtualfilestats
Text and Image FunctionTEXTPTRTEXTPTR
Text and Image FunctionTEXTVALIDTEXTVALID
Trigger FunctionCOLUMNS_UPDATEDCOLUMNS_UPDATED
Trigger FunctionEVENTDATAEVENTDATAPrevent DROP Table using SQL Server DDL Trigger | Log Who Drops Table in SQL Server Database with DDL Trigger
Trigger FunctionTRIGGER_NESTLEVELTRIGGER_NESTLEVEL
Trigger FunctionUPDATE()UPDATE()


SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.