System Functions

Explore built-in functions for SQL Server

System functions are built-in functions that perform calculations, manipulate data, or return information about the database system. They are part of the Transact-SQL (T-SQL) language and can be used in various SQL statements.

Categories of System Functions

SQL Server system functions can be broadly categorized based on their purpose:

1. Aggregate Functions

These functions operate on a set of rows and return a single value. Examples include COUNT, SUM, AVG, MIN, and MAX. While commonly used, these are often discussed separately from other system functions.

2. Scalar Functions

Scalar functions return a single value based on the input arguments. They can be used in expressions and clauses within your SQL statements.

3. String Functions

These functions manipulate character strings. Some common examples:

  • LEN(string_expression): Returns the length of a string.
  • LEFT(string_expression, integer_expression): Returns the left part of a string.
  • RIGHT(string_expression, integer_expression): Returns the right part of a string.
  • SUBSTRING(string_expression, start, length): Extracts a substring.
  • UPPER(character_expression): Converts a string to uppercase.
  • LOWER(character_expression): Converts a string to lowercase.
  • REPLACE(string_expression, string_pattern, string_replacement): Replaces occurrences of a pattern.
  • CONCAT(string1, string2, ...): Concatenates strings.

4. Date and Time Functions

These functions work with date and time values. Key functions include:

  • GETDATE(): Returns the current database system date and time.
  • DATEPART(datepart, date): Returns an integer representing the specified part of a date.
  • DATENAME(datepart, date): Returns a character string representing the specified part of a date.
  • DATEDIFF(datepart, startdate, enddate): Returns the difference between two dates.
  • DATEADD(datepart, number, date): Adds a specified interval to a date.
  • DAY(date): Returns the day of the month.
  • MONTH(date): Returns the month.
  • YEAR(date): Returns the year.

5. Numeric Functions

Functions that perform mathematical operations:

  • ABS(numeric_expression): Returns the absolute value.
  • CEILING(numeric_expression): Returns the smallest integer greater than or equal to a value.
  • FLOOR(numeric_expression): Returns the largest integer less than or equal to a value.
  • ROUND(numeric_expression, length [, function]): Rounds a number.
  • RAND([seed]): Returns a pseudo-random float number.
  • SIGN(numeric_expression): Returns the sign of a number (-1, 0, or 1).

6. System Information Functions

Functions that return information about the SQL Server environment:

  • @@VERSION: Returns information about the SQL Server version and operating system.
  • DB_NAME([database_id]): Returns the name of a database.
  • USER_NAME([user_id]): Returns the name of a user.
  • HOST_NAME(): Returns the workstation name.
  • SUSER_SNAME([server_user_id]): Returns the login name for a given server login ID.

7. Mathematical Functions

Additional mathematical functions beyond basic numeric operations:

  • POWER(float_expression, y): Raises an expression to the power of another expression.
  • SQRT(float_expression): Returns the square root.
  • EXP(numeric_expression): Returns the exponent of a given number.
  • LOG(numeric_expression [, base]): Returns the logarithm.
  • PI(): Returns the value of PI.

Common Examples

Example 1: Getting current date and time

SELECT GETDATE();

Example 2: Extracting year from a date

SELECT YEAR('2023-10-27');

Example 3: Concatenating strings

SELECT CONCAT('Microsoft', ' ', 'SQL Server');

Example 4: Calculating the number of days between two dates

SELECT DATEDIFF(day, '2023-10-01', '2023-10-27');
Note: The exact set of available system functions and their syntax might vary slightly between different versions of SQL Server. Always refer to the documentation specific to your version for the most accurate information.
Tip: System functions are powerful tools for data manipulation and retrieval. Understanding their capabilities can significantly enhance your T-SQL queries.

For a comprehensive list and detailed descriptions, please consult the official Microsoft SQL Server documentation.