What is system-defined function? Explain its types
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
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.
Scalar FunctionDescriptionabs(-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.567upper('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.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.
Aggregate FunctionDescriptionmax()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
Post a Comment
0 Comments