In this blog, we would try to understand how a user-defined function is the Transact-SQL or CLR (Common Language Runtime) routine that accepts parameters, performs an action, and return the result of that action as a value. The return value is either a single value or a table. You may create a reusable function with 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.
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 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 parameter name, don’t forget to add “@” symbol in the beginning. Each parameter should comply with rules otherwise it will be rejected. Parameters are usually local to the function and 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 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?
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 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 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 function are created using 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 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 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 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.
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 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 final output is different from your expectations. So, it is necessary to understand the functions in SQL and learn to use them effectively with basic syntax and arguments knowledge. To know more on SQL functions, join our SQL certification course at JanBask Training and start using them practically right away.
JanBask Training is a leading Global Online Training Provider through Live Sessions. The Live classes provide a blended approach of hands on experience along with theoretical knowledge which is driven by certified professionals.
Course for testing
Receive Latest Materials and Offers on SQL Server Course