System Scalar Functions

SQL Server Documentation - Reference

System Scalar Functions

System scalar functions return a single value based on input values or system information. These functions are categorized and widely used in SQL Server for various data manipulation and information retrieval tasks.

Categorization

System scalar functions can be broadly categorized:

  • Aggregate Functions: (Note: While often discussed with scalar functions, these return a single value from a set of rows. For this context, we focus on functions returning a value *per row* or based on system state.)
  • Date and Time Functions: Manipulate and retrieve date and time values.
  • Mathematical Functions: Perform mathematical operations.
  • String Functions: Manipulate and retrieve string data.
  • System Information Functions: Return information about the SQL Server environment or session.
  • URL Functions: (Used in specific contexts like Service Broker).
  • XML Functions: Operate on XML data.
  • ROWGUID Functions: Generate or retrieve ROWGUID values.

Common System Scalar Functions (Examples)

Date and Time Functions

These functions are used to get the current date, time, or to manipulate existing date and time values.

Function Description Example
GETDATE() Returns the current database system timestamp as a datetime2 value. SELECT GETDATE();
SYSDATETIME() Returns the SQL Server system timestamp as a datetime2(7) value. SELECT SYSDATETIME();
DATEPART(interval, date) Returns an integer representing the specified date part of the specified date. SELECT DATEPART(year, '2023-10-27'); -- Returns 2023
DATEDIFF(datepart, startdate, enddate) Returns the difference between two specified dates in the specified date part. SELECT DATEDIFF(day, '2023-10-01', '2023-10-27'); -- Returns 26

Mathematical Functions

These functions perform arithmetic operations.

Function Description Example
ABS(numeric_expression) Returns the absolute value of a numeric expression. SELECT ABS(-10); -- Returns 10
ROUND(numeric_expression, length [, function]) Rounds a numeric expression to a specified length or precision. SELECT ROUND(123.456, 2); -- Returns 123.46
SQRT(float_expression) Returns the square root of a specified floating-point expression. SELECT SQRT(16); -- Returns 4.0

String Functions

These functions are used to manipulate character strings.

Function Description Example
LEN(string_expression) Returns the number of characters in a string expression, excluding trailing blanks. SELECT LEN('Hello World '); -- Returns 11
LEFT(character_expression, integer_expression) Returns the left part of a character string with the specified number of characters. SELECT LEFT('SQL Server', 3); -- Returns 'SQL'
REPLACE(string_expression, string_pattern, string_replacement) Returns the string with all occurrences of a given string pattern replaced with another string. SELECT REPLACE('This is a test', 'is', 'XX'); -- Returns 'ThXX XX a test'
SUBSTRING(expression, start, length) Returns part of a character string, bit string, or binary string. SELECT SUBSTRING('SQL Server', 5, 6); -- Returns 'Server'

System Information Functions

These functions provide information about the current SQL Server session or instance.

Function Description Example
USER_NAME() Returns the user name for the specified user ID. If no ID is specified, returns the user name of the current context. SELECT USER_NAME();
DB_NAME(database_id) Returns the database name. If no ID is specified, returns the name of the current database. SELECT DB_NAME();
@@ROWCOUNT Returns the number of rows affected by the last statement. UPDATE MyTable SET Column1 = 'Value'; SELECT @@ROWCOUNT;

Using System Scalar Functions

System scalar functions can be used in various parts of a SQL statement, including:

  • The SELECT list.
  • The WHERE clause for filtering data.
  • The ORDER BY clause for sorting results.
  • The GROUP BY clause for aggregation.
  • Default constraints for columns.
  • CHECK constraints.

Example: Combining Functions

You can combine multiple scalar functions in a single query.

SELECT
    ProductName,
    UPPER(LEFT(ProductName, 3)) AS ProductCode,
    GETDATE() AS CurrentDateTime
FROM
    Products
WHERE
    LEN(ProductName) > 10;

Important Notes

  • Always refer to the official Microsoft SQL Server documentation for the most up-to-date and comprehensive information on all available system scalar functions, their parameters, return values, and specific usage guidelines.
  • The behavior of some functions might depend on the SQL Server version and compatibility level.
  • Be mindful of data types when using functions, as implicit or explicit conversion might be necessary.