In SQL Server, there are various functions that can be used to manipulate data and perform operations on it. In this blog we discuss about SQL Server functions and operations. These functions can be divided into several categories:
Scalar functions:
These functions return a single value based on the input provided. Examples of scalar functions include DATEPART(), UPPER(), and CONCAT().
- LOWER(), UPPER()
- SUBSTRING(), REPLACE()
- LEN(), DATALENGTH()
- CAST(), CONVERT()
- COALESCE(), NULLIF()
Aggregate functions:
These functions are used to perform calculations on a set of values and return a single value. Examples of aggregate functions include SUM(), AVG(), and COUNT().
- SUM(), AVG()
- COUNT(), MAX(), MIN()
- GROUP BY
Ranking functions:
These functions are used to rank rows within a result set. Examples of ranking functions include ROW_NUMBER(), RANK(), and DENSE_RANK().
- ROW_NUMBER()
- RANK(), DENSE_RANK()
- NTILE()
String functions:
These functions are used to manipulate string values. Examples of string functions include LEN(), LEFT(), and REPLACE().
- CHARINDEX(), PATINDEX()
- LEFT(), RIGHT(), LTRIM(), RTRIM()
- CONCAT(), STUFF(), QUOTENAME()
Date and time functions:
These functions are used to work with date and time values. Examples of date and time functions include GETDATE(), DATEADD(), and DATEDIFF().
- GETDATE(), CURRENT_TIMESTAMP()
- DATEPART(), DATEADD(), DATEDIFF()
- YEAR(), MONTH(), DAY(), HOUR(), MINUTE(), SECOND()
- DATENAME()
Mathematical functions:
These functions are used to perform mathematical operations. Examples of mathematical functions include ABS(), CEILING(), and FLOOR().
- ABS(), SIGN()
- CEILING(), FLOOR(), ROUND()
- EXP(), LOG(), LOG10(), POWER(), SQRT()
Logical functions:
These functions are used to evaluate logical expressions and return a Boolean value. Examples of logical functions include AND, OR, and NOT.
- AND, OR, NOT
- CASE WHEN
- IIF()
