Different Types of SQL Server Functions

0 0

SQL Server provides a variety of built-in functions that help perform calculations, modify data, and retrieve information. These functions can be classified into several categories based on their functionality:

1. Scalar Functions

Scalar functions return a single value based on the input value. These are often used in SELECT queries, WHERE clauses, or anywhere a single value is required.

Examples:

• String Functions: Manipulate and format string data.
  • LEN(): Returns the length of a string.
  • UPPER(), LOWER(): Convert a string to uppercase or lowercase.
  • REPLACE(): Replaces all occurrences of a substring.
• Numeric Functions: Perform mathematical operations.
  • ABS(): Returns the absolute value.
  • ROUND(): Rounds a number to a specified number of decimal places.
  • CEILING(), FLOOR(): Rounds numbers up or down.
• Date and Time Functions: Manipulate date and time values.
  • GETDATE(): Returns the current date and time.
  • DATEADD(): Adds an interval to a date.
  • DATEDIFF(): Calculates the difference between two dates.
• System Functions: Provide system-level information.
  • SYSTEM_USER: Returns the current user.
  • HOST_NAME(): Returns the name of the connected host.

2. Aggregate Functions

Aggregate functions are used to perform calculations on a set of values and return a single result. They are commonly used with the GROUP BY clause.

Examples:

• COUNT(): Counts the number of rows.
• SUM(): Returns the total sum of a numeric column.
• AVG(): Calculates the average value.
• MIN(), MAX(): Return the smallest or largest value.

3. Table-Valued Functions (TVFs)

These functions return a table, rather than a single value. They can be used like a table in queries.

Inline Table-Valued Functions

CREATE FUNCTION GetEmployeesByDepartment(@DeptId INT)
RETURNS TABLE
AS
RETURN (SELECT * FROM Employees WHERE DepartmentID = @DeptId);

Multi-Statement Table-Valued Functions

CREATE FUNCTION GetProductsByCategory(@CategoryID INT)
RETURNS @Products TABLE (ProductID INT, ProductName NVARCHAR(100))
AS
BEGIN
INSERT INTO @Products
SELECT ProductID, ProductName FROM Products WHERE CategoryID = @CategoryID;
RETURN;
END

4. User-Defined Functions (UDFs)

SQL Server allows users to create their own functions, which can be either scalar or table-valued.

Scalar UDF

CREATE FUNCTION GetEmployeeFullName(@FirstName NVARCHAR(50), @LastName NVARCHAR(50))
RETURNS NVARCHAR(100)
AS
BEGIN
RETURN @FirstName + ' ' + @LastName;
END

5. System Functions

SQL Server provides many built-in system functions that provide information about the server, databases, and connections.

Examples:

• Metadata Functions: OBJECT_ID(), DB_NAME()
• Configuration Functions: @@VERSION, @@TRANCOUNT

6. Ranking Functions

These functions are used to rank rows over a result set, often used with the OVER() clause to partition result sets.

Examples:

• ROW_NUMBER(): Assigns a unique row number to each row.
• RANK(): Provides the rank of each row within a partition.
• DENSE_RANK(): Like RANK() but without gaps in rankings.
• NTILE(): Divides rows into a specified number of groups.

Key Differences Between Function Types

Interview Questions Related to SQL Server Functions

• What is the difference between Scalar and Table-Valued Functions?Scalar functions return a single value, while Table-Valued functions return a table.
• Can you use a function in the WHERE clause of a query?Yes, scalar functions can be used in WHERE, SELECT, GROUP BY, and ORDER BY clauses.
• What is the difference between ROW_NUMBER() and RANK()?ROW_NUMBER() assigns unique numbers to rows, while RANK() assigns numbers with ties but may leave gaps.
• What is a limitation of Multi-Statement Table-Valued Functions?Multi-Statement Table-Valued Functions tend to have poorer performance compared to inline functions because they involve temporary table creation.
• How does the COALESCE() function work in SQL Server?COALESCE() returns the first non-null expression among its arguments.

By understanding these SQL Server functions, you can handle a variety of data operations, from simple calculations to complex data retrieval and manipulation. Each function type is suited for different tasks, so knowing when and how to use them is crucial for efficient SQL programming.