Functions in SQL Server Management Studio

Scalar Functions

Scalar functions return a single value. These are the most common type of function.

• String Functions: For manipulating text data. Examples include LEN(), SUBSTRING(), UPPER(), LOWER(), REPLACE().
• Numeric Functions: For performing mathematical operations. Examples include ABS(), CEILING(), FLOOR(), ROUND(), SQRT().
• Date and Time Functions: For working with dates and times. Examples include GETDATE(), DATEADD(), DATEDIFF(), YEAR(), MONTH(), DAY().
• Aggregate Functions: These operate on a set of rows and return a single aggregate value. Examples include COUNT(), SUM(), AVG(), MIN(), MAX().
• System Functions: Provide information about the SQL Server environment. Examples include DB_NAME(), USER_NAME(), @@ROWCOUNT.

Table-Valued Functions

Table-valued functions (TVFs) return a result set that can be treated as a table. They are particularly useful for encapsulating complex logic that needs to return multiple rows and columns.

• Inline Table-Valued Functions: A concise way to define a TVF.
• Multi-Statement Table-Valued Functions: Allow for more complex logic with multiple T-SQL statements.

Calling Built-in Functions

You can call built-in functions directly in your SELECT, WHERE, ORDER BY clauses, and within other expressions.

-- Example: Using string and date functions
SELECT
    ProductName,
    LEN(ProductName) AS NameLength,
    UPPER(ProductName) AS UppercaseName,
    GETDATE() AS CurrentDateTime
FROM
    Production.Product
WHERE
    LEN(ProductName) > 10;

Using Aggregate Functions

Aggregate functions are typically used with the GROUP BY clause to perform calculations on groups of rows.

-- Example: Counting products by color
SELECT
    Color,
    COUNT(*) AS NumberOfProducts
FROM
    Production.Product
WHERE
    Color IS NOT NULL
GROUP BY
    Color
ORDER BY
    NumberOfProducts DESC;

You can extend SQL Server's capabilities by creating your own functions using T-SQL. This is done using the CREATE FUNCTION statement.

Scalar User-Defined Functions

-- Example: A simple scalar UDF to calculate sales tax
CREATE FUNCTION dbo.CalculateSalesTax (@price DECIMAL(10, 2))
RETURNS DECIMAL(10, 2)
AS
BEGIN
    RETURN @price * 0.0825; -- Assuming 8.25% tax rate
END;
GO

-- Calling the scalar UDF
SELECT
    ProductName,
    ListPrice,
    dbo.CalculateSalesTax(ListPrice) AS EstimatedTax
FROM
    Production.Product
WHERE
    ListPrice > 50;

Table-Valued User-Defined Functions

-- Example: An inline TVF to get products by a specific category
CREATE FUNCTION dbo.GetProductsByCategory (@categoryName NVARCHAR(50))
RETURNS TABLE
AS
RETURN
(
    SELECT
        p.ProductID,
        p.Name AS ProductName,
        p.ProductNumber
    FROM
        Production.Product AS p
    INNER JOIN
        Production.ProductSubcategory AS sc ON p.ProductSubcategoryID = sc.ProductSubcategoryID
    INNER JOIN
        Production.ProductCategory AS c ON sc.ProductCategoryID = c.ProductCategoryID
    WHERE
        c.Name = @categoryName
);
GO

-- Calling the inline TVF
SELECT *
FROM dbo.GetProductsByCategory('Bikes');