SQL Server Built-in Functions

In SQL, a built-in function is a programming object that takes zero or more inputs and returns a value. A feature that is built into an application and can be used by end users. Built-in functions in SQL SELECT expressions help in calculating numbers, casting or converting data, changing the format of fields, and analyzing data.

The following is a list of Microsoft SQL Server built-in functions, some of which have been covered in previous posts and are linked to those posts, while the others will be covered in depth in future topics.

Please note that certain functions are repeatative, which means there are several examples.

Aggregate Functions:

• APPROX_COUNT_DISTINCT
• AVG
• CHECKSUM_AGG
• COUNT
• COUNT_BIG
• GROUPING
• GROUPING_ID
• MAX
• MIN
• STDEV
• STDEV_POP
• STRING_AGG
• SUM
• VAR
• VARP

Analytical Functions:

• CUME_DIST
• FIRST_VALUE
• LAG
• LAST_VALUE
• LEAD
• LEAD
• PERCENT_RANK
• PERCENTILE_CONT
• PERCENTILE_DISC

Configuration Functions:

• @@DATEFIRST
• @@DBTS
• @@LANGID
• @@LANGUAGE
• @@LOCK_TIMEOUT
• @@MAX_CONNECTIONS
• @@MAX_PRECISION
• @@NESTLEVEL
• @@OPTIONS
• @@REMSERVER
• @@SERVERNAME
• @@SERVICENAME
• @@SPID
• @@TEXTSIZE
• @@VERSION

Conversion Functions:

• CAST and CONVERT
• CONVERT
• PARSE
• TRY_CAST
• TRY_CONVERT
• TRY_PARSE
• PARSENAME

Data Type Functions:

• DATALENGTH
• IDENT_SEED
• IDENT_CURRENT
• IDENTITY (Function)
• IDENTITY (INSERT)
• IDENT_INCR
• SQL_VARIANT_PROPERTY

Date Time Functions:

• SYSDATETIME
• SYSDATETIMEOFFSET
• SYSUTCDATETIME
• CURRENT_TIMESTAMP
• GETDATE
• GETDATE
• GETUTCDATE
• DATENAME
• DATEPART
• DAY
• MONTH
• YEAR
• DATEFROMPARTS
• DATETIME2FROMPARTS
• DATETIMEFROMPARTS
• DATETIMEOFFSETFROMPARTS
• SMALLDATETIMEFROMPARTS
• TIMEFROMPARTS
• DATEDIFF
• DATEDIFF_BIG
• DATEADD
• EOMONTH
• SWITCHOFFSET
• TODATETIMEOFFSET
• ISDATE

JSON Functions:

• ISJSON
• JSON_VALUE
• JSON_QUERY
• JSON_MODIFY
• OPENJSON

Mathematical Functions: 

• ABS
• ACOS
• ASIN
• ATAN
• ATN2
• CEILING
• COS
• COT
• DEGREES
• EXP
• FLOOR
• LOG
• LOG10
• PI
• POWER
• RADIANS
• RAND
• ROUND
• SIGN
• SIN
• SQRT
• SQUARE
• TAN

Logical Functions:

• CHOOSE
• GREATEST
• IIF
• LEAST
• CASE

Ranking Functions:

• RANK
• NTILE
• DENSE_RANK
• ROW_NUMBER

Security Functions:

• CERTENCODED
• PWDCOMPARE
• CERTPRIVATEKEY
• PWDENCRYPT
• CURRENT_USER
• SCHEMA_ID
• DATABASE_PRINCIPAL_ID
• SCHEMA_NAME
• sys.fn_builtin_permissions
• SESSION_USER
• sys.fn_get_audit_file
• SUSER_ID
• sys.fn_my_permissions
• SUSER_SID
• HAS_PERMS_BY_NAME
• SUSER_SNAME
• IS_MEMBER
• SYSTEM_USER
• IS_ROLEMEMBER
• SUSER_NAME
• IS_SRVROLEMEMBER
• USER_ID
• LOGINPROPERTY
• USER_NAME
• ORIGINAL_LOGIN
• PERMISSIONS

String Functions:

• ASCII
• CHAR
• CHARINDEX
• CONCAT
• CONCAT_WS
• DIFFERENCE
• FORMAT
• LEFT
• LEN
• LOWER
• LTRIM
• NCHAR
• PATINDEX
• QUOTENAME
• REPLACE
• REPLICATE
• REVERSE
• RIGHT
• RTRIM
• SOUNDEX
• SPACE
• STR
• STRING_AGG
• STRING_ESCAPE
• STRING_SPLIT
• STRING_SPLIT
• STUFF
• SUBSTRING
• TRANSLATE
• TRIM
• UNICODE
• UPPER

System Functions:

• $PARTITION
• ERROR_PROCEDURE
• @@ERROR
• ERROR_SEVERITY
• @@IDENTITY
• ERROR_STATE
• @@PACK_RECEIVED
• FORMATMESSAGE
• @@ROWCOUNT
• GET_FILESTREAM_TRANSACTION_CONTEXT
• @@TRANCOUNT
• GETANSINULL
• BINARY_CHECKSUM
• HOST_ID
• CHECKSUM
• HOST_NAME
• COMPRESS
• ISNULL
• CONNECTIONPROPERTY
• ISNUMERIC
• CONTEXT_INFO
• MIN_ACTIVE_ROWVERSION
• CURRENT_REQUEST_ID
• NEWID
• CURRENT_TRANSACTION_ID
• NEWSEQUENTIALID
• DECOMPRESS
• ROWCOUNT_BIG
• ERROR_LINE
• SESSION_CONTEXT
• ERROR_MESSAGE
• SESSION_ID
• ERROR_NUMBER
• XACT_STATE

Window Functions:

• AGGREGATE FUNCTIONS IN ANALYTICAL CONTEXT
• MIN and MAX functions in Analytical Context