In this blog, we would try to understand function in SQL Server and how a user-defined function is the Transact-SQL or CLR (Common Language Runtime) routine that accepts parameters, performs an action, and returns the result of that action as a value. We will elaborate on function in SQL Server example also. With our function in SQL server example you will get a better understanding of the same. The return value is either a single value or a table. You may create a reusable function with a CREATE statement in SQL that can be used in multiple scenarios as given below.
Here is given the basic syntax of how user-defined functions are created in T-SQL.
Let us discuss the different arguments that are used in the syntax given above.
[OR ALTER] – It is applied to both SQL server database 2016 and Azure databases. It alters the function conditionally only if it already exists.
schema_name – Here, you have to give the name of the schema to which user-defined function belongs.
Learn SQL Server in the Easiest Way
Function_name – This is the name for a user-defined function that must comply with certain rules and must be unique within the database. Keep in mind that parentheses are always required after the function name, even if parameters are not defined well.
@parameter_name – This is the parameter given within the user-defined function. It is possible to declare multiple parameters together. A function may have 2100 parameters in total. The user supplies a value for each declared parameter when the function is executed. When you give a parameter name, don’t forget to add the “@” symbol in the beginning. Each parameter should comply with rules otherwise it will be rejected. Parameters are usually local to the function and the same parameter name can be used with other functions too. Parameters can be used in place of constants only but they cannot be used in place of table names, column names, or the name of other database objects. [type_schema_name.] parameter_data_type – Here, you can define data types for parameters and schema to which it belongs. For T-SQL functions, you may use all data types either it is CLR user-defined types or user-defined table types except the timestamp data types. The non-scalar data types cannot be defined for T-SQL or CLR functions. If type_schema_name is not given, the Database Engine looks for the scalar_parameter_data_type in the following order:
[ =default ] – This is the default value for parameter. If the default value is given for a parameter, it can be executed without specifying a value for that particular parameter. When a parameter of the function has a default value, you should insert the DEFAULT keyword in that case to retrieve the default value.
READONLY – It indicates that the parameter cannot be modified within the definition of a function. If the parameter is user-defined table type, using READONLY specification is necessary.
Return_data_type – It gives the scalar value for a user-defined function. For non-scalar types, you cannot specify a return data type in T-SQL or CLR function.
Function_body – Here, you specify a series of T-SQL statements that together evaluate some value.
You cannot alter, drop, rename, or aggregate a function if it is not created. To drop a function in SQL you either need to ALTER permission on the schema to which the function belongs or CONTROL permission on the function. To re-create a function needs to CREATE FUNCTION permission and ALTER permission on the schema in which the function is created. With this discussion, you know how to create a function in T-SQL. Now let us discuss how to use alter, drop, rename, aggregate function in SQL?
SQL Server Training & Certification
To rename a function SQL Server 2017, you can either SQL server management studio or Transact-SQL. When you rename a user-defined function, it will not change the corresponding object name in the definition of a function. So, you should drop or recreate a function in that case instead of renaming it. Also, changing the name of the user-defined function can affect the performance of the attached objects too. The dependent objects may stop working when the function is loaded again. Let us see how to rename a user-defined function in SQL SERVER MANAGEMENT STUDIO.
This method cannot be used for the T-SQL database. To rename a T-SQL function, you first have to delete it and recreate it with the new name. Make sure that app applications and programs that were using the older function, now they are connected with the new one.
As we discussed that functions are created using the CREATE function and they are modified using ALTER function. Obviously, the DROP command is valid for existing functions only. The basic syntax for drop command is given below.
Here, you can add schema name to which the function belongs to and the name of a user-defined function to be removed. Adding the schema name is optional but the server name or database name cannot be specified here. DROP function does not work if it is bind with Schema binding clause. It will not work if computed columns that reference this function are indexed. To execute the DROP function, you should add ALTER permission on the schema to which it belongs, or CONTROL permission on the function.
Here, we will learn how to ALTER a function that was created using the CREATE statement with changing its permission and affecting the dependent functions, stored procedures, or triggers, etc. Here is the basic syntax for ALTER command in SQL.
ALTER function cannot be used to change a scalar-valued function to a table-valued function. Also, ALTER function cannot be used to change INLINE function with multiple statements. ALTER command cannot be used to change a T-SQL function to CLR function or vice versa.
SQL Server Training & Certification
An aggregate function performs the calculation on a set of values and returns a single value. An aggregate function is generally used with the GROUP BY clause of the SELECT statement. It ignores null values too. They are deterministic and return the same value every time they are called for a specific set of inputs. There are certain cases where the aggregate function should be used as an expression. There are multiple aggregate functions in SQL and you may use any one of them based on requirements. A complete range of aggregate functions in SQL is given as below.
Each of them has different syntax and they are generally picked based on the calculation you want to perform with a function. Similarly, SQL offers a complete range of in-built functions to perform operations on data. They are useful for mathematical calculations, string concatenation, sub-strings, etc. If they are not used correctly then the final output is different from your expectations. So, it is necessary to understand the function in SQL Server and learn to use them effectively with basic syntax and argument knowledge. You should also read a little more on function in sql server example to understand the whole concept in a better manner. To know more about SQL functions, join our SQL certification course at JanBask Training and start using them practically right away.
A dynamic, highly professional, and a global online training course provider committed to propelling the next generation of technology learners with a whole new way of training experience.
MS SQL Server
Receive Latest Materials and Offers on SQL Server Course