Functions in SQL Server are the database objects that contains a set of SQL statements to perform a specific task. A function accepts input parameters, perform actions, and then return the result. We should note that functions always return either a single value or a table. The main purpose of functions is to replicate the common task easily. We can build functions one time and can use them in multiple locations based on our needs. SQL Server does not allow to use of the functions for inserting, deleting, or updating records in the database tables.

The following are the rules for creating SQL Server functions:

  • A function must have a name, and the name cannot begin with a special character such as @, $, #, or other similar characters.
  • SELECT statements are the only ones that operate with functions.
  • We can use a function anywhere such as AVG, COUNT, SUM, MIN, DATE, and other functions with the SELECT query in SQL.
  • Whenever a function is called, it compiles.
  • Functions must return a value or result.
  • Functions use only input parameters.
  • We cannot use TRY and CATCH statements in functions.

Types of Functions

SQL Server categorizes the functions into two types:

  • System Functions
  • User-Defined Functions