Types of Built-in Functions
SQL Server supports numerous built-in functions. Some of these are specific to a particular set of functionality. For instance, rowset functions can be used only with full-text search, XML strings, or linked servers. Similarly, math functions (not surprisingly) are helpful mainly in applications that have to perform mathematical calculations.
Other functions can be used in a variety of situations. Following is a list of function types that should contain almost every supported built-in function:
Aggregate (SUM, AVG, MIN, MAX, and so on)
System (CURRENT_USER, HOST_ID, ISNULL, NULLIF, and so on)
System Statistical (@@CONNECTIONS, @@CPU_BUSY, and so on)
Metadata (COL_NAME, DB_ID, DB_NAME, and so on)
Configuration (@@DATEFIRST, @@LANGUAGE, and so on)
Date (GETDATE, DATEADD, and so on)
Math (COS, TAN, SQUARE, and so on)
Text (PATINDEX, TEXTPTR, and so on)
String (SUBSTRING, LEFT, CHARINDEX, and so on)
Rowset (OPENQUERY, OPENXML, CONTAINS, and so on)
Cursor (@@CURSOR_ROWS, @@FETCH_STATUS, and so on)
Security (HAS_DBACCESS, SUSER_NAME, and so on)