---
title: "Define the functions in SQL with examples."  
description: "in sql server, functions are reusable code blocks that perform a specific task and return a value."  
author: "Ashutosh Patel"  
published: 2024-07-12  
updated: 2024-07-12  
canonical: https://www.mindstick.com/blog/304485/define-the-functions-in-sql-with-examples  
category: "mssql server"  
tags: ["mssql server", "sql server", "sql"]  
reading_time: 3 minutes  

---

# Define the functions in SQL with examples.

### SQL Server Functions

In SQL, functions are reusable blocks of code that perform a specific operation and return a value. They can be divided into two main types: [Scalar Functions](https://www.mindstick.com/interview/23537/what-are-aggregate-and-scalar-functions) and [Aggregate Functions](https://www.mindstick.com/forum/158949/what-are-the-conditional-aggregate-functions-in-sql-such-as-sum-case-and-how-are-they-used).

Here each method is shared with examples.

#### Scalar Functions

The [scalar function](https://www.mindstick.com/forum/34598/how-to-create-scalar-function-in-sql) returns a value based on the input value. It can be built-in or user-defined.

## Common Built-in Scalar Functions

**UPPER()-** It converts a string to uppercase.

```plaintext
SELECT UPPER('hello') AS UppercaseString;  -- Output: 'HELLO'
```

**LOWER()-** It converts a string to lowercase.

```plaintext
SELECT LOWER('WORLD') AS LowercaseString;  -- Output: 'world'
```

**LEN()-** It returns the [length of a string](https://answers.mindstick.com/qa/104780/how-to-find-the-length-of-a-string).

```plaintext
SELECT LEN('SQL Server') AS StringLength;  -- Output: 11
```

**GETDATE()-** It returns the [current date](https://www.mindstick.com/forum/323/find-the-day-name-and-month-name-from-current-date) and time.

```plaintext
SELECT GETDATE() AS CurrentDateTime;  -- Output: Current date and time
```

**ROUND()-** Rounds a numeric value to a specified number of [decimal places](https://www.mindstick.com/forum/33881/round-double-type-variable-to-two-decimal-places-in-c-sharp).

```plaintext
SELECT ROUND(123.4567, 2) AS RoundedValue;  -- Output: 123.46
```

## User-Defined Scalar Functions

You can create your own scalar functions. Here is an example that calculates the square of a number.

```plaintext
CREATE FUNCTION dbo.Square(@Number INT)
RETURNS INT
AS
BEGIN
   RETURN @Number * @Number;
END;
```

## Execute-

```plaintext
SELECT [dbo].[Square](5) AS SquaredValue;
 -- Output: 25
```

#### Aggregate Functions

The [aggregation](https://www.mindstick.com/forum/217/what-is-aggregation-and-how-it-maps-into-a-java-class) function works on a range of values ​​and returns a single summary value. It is often used with the `GROUP BY` clause.

## Common Aggregate Functions

**COUNT()-** it returns the number of rows in a group or table.

```plaintext
SELECT COUNT(*) AS TotalEmployees FROM Employees;
-- Count of all employees
```

**SUM()-** It returns the sum of a numeric column.

```plaintext
SELECT SUM(Salary) AS TotalSalary FROM Employees;
-- Sum of all salaries
```

**AVG()-** It returns the average of a numeric column

```plaintext
SELECT AVG(Salary) AS AverageSalary FROM Employees;
-- Average salary
```

**MIN()-** It returns the minimum value in a set.

```plaintext
SELECT MIN(Salary) AS LowestSalary FROM Employees;
-- Lowest salary
```

**MAX()-** It returns the [maximum value](https://www.mindstick.com/forum/33667/select-the-maximum-value-of-a-colum-in-mysql) in a set.

```plaintext
SELECT MAX(Salary) AS HighestSalary FROM Employees;
-- Highest salary
```

#### Table-Valued Functions

Table-valued functions return a table as a result. These can be used in queries such as regular tables.

**Example**\
Here is an example that returns employees from a specific department.

```plaintext
CREATE FUNCTION dbo.GetEmployeesByDeptID(@DepartmentID INT)
RETURNS TABLE
AS
RETURN (
   SELECT * FROM Employees WHERE DepartmentID = @DepartmentID
);
```

## Execute-

```plaintext
SELECT * FROM [dbo].[GetEmployeesByDeptID](1);
```

## Summary of Function Types

| **Type** | **Description** | **Example** |
| --- | --- | --- |
| Scalar Functions | Return a single value | UPPER('text') |
| Aggregate Functions | Return a summary value | SUM(Salary) |
| Table-Valued Functions | Return a table | GetEmployeesByDepartment(1) |

SQL processing is necessary to perform various tasks efficiently. They help preserve logic and encourage code reuse, making [database operations](https://www.mindstick.com/forum/160213/explain-the-use-of-asynchronous-database-operations-in-dot-net-core-apis) more efficient and consistent.

**Also, Read:** [Explain the SQL Stored Procedures](https://www.mindstick.com/blog/304484/explain-the-sql-stored-procedures)

---

Original Source: https://www.mindstick.com/blog/304485/define-the-functions-in-sql-with-examples

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.