Our Support: During the COVID-19 outbreak, we request learners to CALL US for Special Discounts!

- SQL Server Blogs -

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: SQL Server Analysis Services – All You Need to Know

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.

Learn SQL Server in the Easiest Way

  • Learn from the videos
  • Learn anytime anywhere
  • Pocket-friendly mode of learning
  • Complimentary eBook available

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: SQL Server Analysis Services – All You Need to Know

  • 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?

SQL Server Training & Certification

  • No cost for a Demo Class
  • Industry Expert as your Trainer
  • Available as per your schedule
  • Customer Support Available

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.

How to drop a function in T-SQL?

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: How to Restore a Database Backup from SQL Server Management Studio

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.

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.

SQL Server Training & Certification

  • Personalized Free Consultation
  • Access to Our Learning Management System
  • Access to Our Course Curriculum
  • Be a Part of Our Free Demo Class

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.

How to use Aggregate Function in SQL?

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: Different Types of SQL Keys: Example and Uses

SQL Tutorial Overview


    Janbask Training

    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.


Comments

Trending Courses

AWS

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

Upcoming Class

0 day 05 Aug 2020

DevOps

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

Upcoming Class

5 days 10 Aug 2020

Data Science

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

Upcoming Class

12 days 17 Aug 2020

Hadoop

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

Upcoming Class

2 days 07 Aug 2020

Salesforce

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

Upcoming Class

-1 day 04 Aug 2020

QA

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

Upcoming Class

2 days 07 Aug 2020

Business Analyst

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

Upcoming Class

0 day 05 Aug 2020

MS SQL Server

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

Upcoming Class

9 days 14 Aug 2020

Python

  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation

Upcoming Class

2 days 07 Aug 2020

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks

Upcoming Class

2 days 07 Aug 2020

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning

Upcoming Class

11 days 16 Aug 2020

Tableau

  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop

Upcoming Class

2 days 07 Aug 2020

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews