In Microsoft SQL Server, in-built functions provide a wide range of tools for manipulating and analyzing data. This chapter explores various categories of in-built functions and their real-world use cases.
Notes
Chapter 18: User-Defined Functions (UDFs)
In Microsoft SQL Server, User-Defined Functions (UDFs) are custom functions that allow you to encapsulate logic and perform specific tasks. This chapter explores the creation, usage, and real-world applications of UDFs.
1. Creating UDFs
Scalar UDFs:
Scalar UDFs return a single value.
Example :
Creating a Scalar UDF
CREATE
FUNCTION CalculateDiscount(@Price
DECIMAL(10, 2),
@Discount
DECIMAL(5, 2))
RETURNS
DECIMAL(10, 2))
AS BEGIN
RETURN @Price * (1 - @Discount / 100);
END;
In this example, a scalar UDF named CalculateDiscount calculates the discounted price.
Table-Valued UDFs:
Table-Valued UDFs return a table result.
Example :
creating a table-valued UDF
CREATE
FUNCTION GetOrdersByCustomer(@CustomerID INT)
RETURNS
TABLE AS
RETURN (
SELECT
OrderID, OrderDate
FROM
Orders
WHERE
CustomerID = @CustomerID );
This table-valued UDF, GetOrdersByCustomer, retrieves orders for a specific customer.
2. Using UDFs
Scalar UDF Usage:
Example :
Using a scalar UDF
SELECT
OrderID, TotalAmount = dbo.CalculateDiscount(Price, 10)
FROM
OrderDetails
This query applies the CalculateDiscount scalar UDF to calculate discounted prices for products.
Table-Valued UDF Usage:
Example :
using a table-valued UDF
SELECT
o.OrderID, o.OrderDate, c.CustomerName
FROM
dbo.GetOrdersByCustomer(123)
AS
o
JOIN
Customers
AS
c
ON
o.CustomerID = c.CustomerID;
This query uses the GetOrdersByCustomer table-valued UDF to retrieve orders for a specific customer and joins them with customer details.
3. Real-world UDF Examples
E-commerce System:
- Price Calculation: Create a scalar UDF to calculate the final price of a product, including discounts, taxes, and shipping costs.
- User Recommendations: Implement a table-valued UDF to suggest products to users based on their purchase history.
Healthcare Records System:
- Patient History: Develop a table-valued UDF to retrieve a patient's medical history, including diagnoses, treatments, and prescriptions.
- Billing Calculation: Design a scalar UDF to calculate patient bills, considering insurance coverage and co-payments.
4. Key Takeaways
- UDFs allow you to encapsulate custom logic in SQL Server.
- Scalar UDFs return single values, while table-valued UDFs return tables.
- UDFs can be used in various real-world scenarios to enhance data processing and analysis.
5. Exercise Questions
- Create a scalar UDF named CalculateTax that calculates the tax amount for a given product price and tax rate.
- Write a table-valued UDF called GetTopProducts that returns the top N products with the highest sales.
- Use the CalculateTax scalar UDF to calculate the tax for a product with a price of $100 and a tax rate of 8%.
- Develop a table-valued UDF named GetEmployeeSales that retrieves sales data for employees based on their IDs.
- Explain the advantages of using UDFs over inline SQL code for encapsulating custom logic and reusability.
- Discuss the performance considerations when using UDFs, especially in scenarios involving large datasets and complex calculations.
In Microsoft SQL Server, subqueries are powerful tools that allow you to nest one query within another. This chapter explores subqueries, their use cases, and the CROSS APPLY operator for real-world scenarios.