SQL Server User-Defined Functions
Let us discuss about the creation, modification, and usage of UDFs in SQL Server. We will also provide examples of scalar function and create a user-defined function. By the end of this article, the readers will have a better understanding on how to effectively use the UDFs in SQL Server to improve our database performance.
Create a Table for Demonstration
We first need to create a demo table for our further demonstration of user-defined functions in the SQL Server. Let’s create a table named “Products” and insert it with the product’s data. The table contains two columns which are ProductName and Price.
ProductID INT,
ProductName VARCHAR(100),
Price DECIMAL(10, 2)
);
GO
INSERT INTO Products (ProductID, ProductName, Price)
VALUES
(1, 'Product A', 100.00),
(2, 'Product B', 50.00),
(3, 'Product C', 75.00);
This creates a table with three columns: the “product id” as the primary key, the “product name” as a string column with a maximum length of 50 characters, and the “price” as a decimal column with a maximum of 10 digits, with 2 digits after the decimal point.
Output:
1 Product A 100.00
2 Product B 50.00
3 Product C 75.00
Scalar User-Defined Functions
As we work in SQL Server, we often use these three user-defined functions: CREATE, ALTER, and DROP. These commands allow for the creation, modification, and removal of user-defined functions within the database. The CREATE command is used to create a new user-defined function. It involves specifying the object type as FUNCTION. The body of the function consists of SQL statements that define the logic and operations to be performed. These statements can involve calculations, data manipulations, and other functions.
Suppose we want to create a user-defined function called “CalculateDiscountedPrice”. This function takes the product price as a parameter and calculates the discounted price based on a discount percentage.
(
@price DECIMAL (10, 2),
@discount DECIMAL (5, 2)
)
RETURNS DECIMAL (10, 2)
AS
BEGIN
DECLARE @discountedPrice DECIMAL (10, 2);
SET @discountedPrice = @price - (@price * @discount / 100);
RETURN @discountedPrice;
END;
Output:
FROM Products;
ProductID ProductName Price DiscountedPrice
1 Product A 100.00 90.00
2 Product B 50.00 45.00
3 Product C 75.00 67.50
The ALTER command is used to modify an existing User-Defined function. It allows us to make changes to the function’s definition such as adding or removing the parameters, modifying the logic, or changing the return data type. This command is useful when there is a need to update the behaviour of a User-Defined function without recreating it entirely. The syntax for the ALTER command is the same as the CREATE statement.
RETURNS DECIMAL(10, 2)
AS
BEGIN
DECLARE @discountedPrice DECIMAL(10, 2);
-- Apply 50% discount
-- updated logic
SET @discountedPrice = @price * 0.5;
RETURN @discountedPrice;
END;
Output:
FROM Products;
ProductID ProductName Price DiscountedPrice
1 Product A 100.00 50.00
2 Product B 50.00 25.00
3 Product C 75.00 37.50
The DROP command can remove a user-defined function from the database. It permanently deletes the function and its associated metadata.
Output:
Completion time: 2023-07-10T 18:45:23.2082580 + 05:30
We must be caution when using the DROP command as it irreversibly removes the user-defined functions from the database. The user should take a backup of required things.
Using the CREATE, ALTER, and DROP commands, we have the flexibility to create, modify, and remove the User-Defined functions as needed. This helps us to design and implement the custom functions according to our requirements. The USER-DEFINED FUNCTIONS provide a powerful mechanism for complex calculations, data manipulations, and other operations which promote the code reusability.
A Basic User-Defined Function
There are further benefits of user-defined functions in SQL Server. Let us create a very basic function that categorizes the products as either expensive or cheap based on their prices.
(
@price DECIMAL (10, 2)
)
RETURNS VARCHAR(10)
AS
BEGIN
DECLARE @category VARCHAR(10);
IF (@price > 60)
SET @category = 'expensive';
ELSE
SET @category = 'cheap';
RETURN @category;
END;
Here in the T-SQL query, we declare the “@Price” parameter of “DECIMAL(10,2)” type which represents the price of the product. The function then declares a local variable “@Category” of type “VARCHAR(10)” to hold the category of the product. If the price of the product is greater than 60, the function sets the category to “Expensive”. Otherwise, it sets the category to “Cheap”. Finally, the function returns the category of the product.
FROM Products;
ProductID ProductName Price Category
1 Product A 100.00 expensive
2 Product B 50.00 cheap
3 Product C 75.00 expensive
This T-SQL returns a table with three columns: “ProductName”, “Price”, and “Category”. The “Category” column displays the output of the “fn_GetProductCategory” function for each product.
Conclusion
User-defined functions are a powerful tool in SQL Server to encapsulate the reusable code logic and perform the calculations or transformations on the data. By creating the user-defined functions, we can improve the code organization, simplify the queries, and enhance the overall database performance. Moreover, is very important to use the user-defined functions fairly and avoid overusing them to maintain an optimal query performance.
Source: linuxhint.com