System Functions

Functions that are defined by the system are known as system functions. In other words, all the built-in functions supported by the server are referred to as System functions. The built-in functions save us time while performing the specific task. These types of functions usually work with the SQL SELECT statement to calculate values and manipulate data.

These functions are defined by SQL Server for a different purpose. We have two types of system defined function in SQL Server

  1. Scalar Function

    Scalar functions operate on a single value and return a single value. Below is the list of some useful SQL Server Scalar functions.

    System Scalar Function
    Scalar Function
    Description
    abs(-10.67)
    This returns an absolute number of the given number means 10.67.
    rand(10)
    This will generate a random number of 10 characters.
    round(17.56719,3)
    This will round off the given number to 3 places of decimal means 17.567
    upper('dotnet')
    This will returns the upper case of given string means 'DOTNET'
    lower('DOTNET')
    This will returns the lower case of given string means 'dotnet'
    ltrim(' dotnet')
    This will remove the spaces from the left-hand side of 'dotnet' string.
    convert(int, 15.56)
    This will convert the given float value to integer means 15.

  2. Aggregate Function

    Aggregate functions operate on a collection of values and return a single value. Below is the list of some useful SQL Server Aggregate functions.

    System Aggregate Function
    Aggregate Function
    Description
    max()
    This returns maximum value from a collection of values.
    min()
    This returns the minimum value from a collection of values.
    avg()
    This returns an average of all values in a collection.


Here is the list of some system functions used in the SQL Server:

  • String Functions (LEN, SUBSTRING, REPLACE, CONCAT, TRIM)

  • Date and Time Functions (datetime, datetime2, smalldatetime)
  • Aggregate Functions (COUNT, MAX, MIN, SUM, AVG)
  • Mathematical Functions (ABS, POWER, PI, EXP, LOG)
  • Ranking Functions (RANK, DENSE_RANK, ROW_NUMBER, NTILE)
  • Conversion Functions (CAST, Convert)
  • Configuration Functions

  • Advanced Functions