How to Use Alter, Drop, Rename, Aggregate Function in SQL Server?

Introduction

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.

• To create a function in T-SQL, like SELECT statement
• To call a function in applications
• To define a function within another user-defined function
• To define a column in a table
• To parametrize or improve the functionality of an indexed view.
• To define the check constraint on a column
• To replace some stored procedure
• To set the filter predicate using the INLINE function for security purposes.

Read: Introduction to SQL Injection and Attacks

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.

Read: All you need to know about SQL Cheat Sheet

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:

Read: Introduction to SQL Injection and Attacks

• The schema that contains the name of SQL Server data types.
• The default schema of the current user in the current database.
• The dbo schema in the current database.

[ =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.

Read: Most Popular SQL Server Performance Tuning Tips

Function_body – Here, you specify a series of T-SQL statements that together evaluate some value.

Best Practices for user-defined function in SQL Server

• Function names should always comply with the rules of identifiers.
• Use a schema-binding clause within your function to keep the data secure.
• The name of the function should be clear to the user and developer both.
• You can define up to 2100 parameters for a particular function.
• Don’t forget to add data types based on the nature of the value, you want to store.
• The scope of functions is generally defined locally so functions with the same name can be used other programs or applications too. The same case is true for parameters defined within functions.

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?

How to rename user-defined functions in SQL Server ?

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.

• To rename an object, click on the plus sign next to the database that contains the function and rename it.
• Now click on the plus sign next to the Programmability Folder.
• Click on the plus sign next to function, you wish to rename. It could be a table-valued function, scalar-valued function, an aggregate function, etc.
• Right-click on function now and Rename it.
• Give a new name to the function and you are done.

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.

Read: SQL Server Developer & Database Administrator Salary Structure

How to drop a function in T-SQL?

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.

Read: 241 Excel Shortcut Keys that You Should Know in 2022-23

How to ALTER a function in T-SQL?

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.

How to use an Aggregate Function in SQL?

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.

Read: How Long Does It Take to Learn SQL? Get to Know Tips to Learn Fast

FaceBook Twitter Google+ LinkedIn Pinterest Email