Today's Offer - SQL Server Certification Training - Enroll at Flat 10% Off.

- SQL Server Blogs -

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

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.

  • 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 purpose.

Here is given the basic syntax of how user-defined functions are created in T-SQL. Function in SQL Server 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: Difference Between Stored Procedure and Function in SQL Server

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. SQL Server Curriculum @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:

  • 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: Top 100 SQL Interview Questions and Answers

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

Best Practices for user-defined functions

  • Function names should always comply with the rules of identifiers.
  • Use 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 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 CREATE FUNCTION permission and ALTER permission on the schema in which the function is created. SQL Server quiz 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?

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.

  • To rename an object, click on 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, and 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 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: SSIS Interview Questions & Answers for Fresher, Experienced

How to drop a function in T-SQL?

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. How to drop a function in T-SQL? 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.

How to ALTER a function in T-SQL?

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. How to ALTER a function in T-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 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. free SQL Server demo 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. How to use Aggregate Function in SQL?

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.

Read: SQL Server Tutorial for Beginners

SQL Tutorial Overview


    Janbask Training

    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.


Trending Courses

AWS

  • AWS & Fundamentals of Linux
  • Amazon Simple Storage Service
  • Elastic Compute Cloud
  • Databases Overview & Amazon Route 53

Upcoming Class

4 days 24 Nov 2019

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing

Upcoming Class

5 days 25 Nov 2019

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning

Upcoming Class

5 days 25 Nov 2019

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation

Upcoming Class

6 days 26 Nov 2019

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

14 days 04 Dec 2019

Course for testing

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

34 days 24 Dec 2019

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing

Upcoming Class

13 days 03 Dec 2019

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum

Upcoming Class

5 days 25 Nov 2019

SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design

Upcoming Class

8 days 28 Nov 2019

Comments

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews