SQL Functions   «Prev  Next»
Lesson 1

Utilizing Functions for Modularization in SQL Server 2016

SQL Server 2016, a robust and versatile relational database management system, introduces and refines numerous features aimed at enhancing the efficiency, scalability, and manageability of database operations. Among these features, the ability to use functions stands out prominently, offering a pathway to modularize SQL statements. Let's delve into how functions achieve this goal and the advantages they bring.
  1. Understanding SQL Functions: In SQL Server 2016, a function is a precompiled, reusable routine that returns a value. There are two primary categories:
    • Scalar Functions: Return a single value.
    • Table-valued Functions: Return tabular data.
  2. Modularity Through Encapsulation: Functions allow encapsulation of frequently-used logic or complex operations, making SQL code more readable, maintainable, and reusable. For example, a function can be written to calculate VAT for a product price, and this function can be invoked wherever VAT calculations are needed, ensuring consistency and reducing redundancy.
  3. Parameterization: Functions can accept parameters, making them highly adaptable. This means you can customize the output based on variable input, adding to the function's versatility. For instance, a table-valued function might accept a date range as parameters to retrieve sales data for that period.
  4. Enhanced Security: Using functions, it's possible to grant users access to the data returned by the function without granting them permissions on the underlying tables. This provides an abstraction layer, ensuring users can only access data relevant to them.
  5. Integration with Standard SQL: Functions in SQL Server 2016 can be seamlessly integrated into standard SQL queries. Whether it's in the `SELECT`, `WHERE`, or `JOIN` clauses, functions can be invoked just like any other column or table, depending on the function type.
  6. Examples of Functions in Action:
    • Scalar Function
       CREATE FUNCTION dbo.CalculateVAT (@Price DECIMAL(10,2))
       RETURNS DECIMAL(10,2)
       AS
       BEGIN
      	 RETURN @Price * 0.20
       END;
       
    • Table-valued Function
       CREATE FUNCTION dbo.GetSalesData (@StartDate DATE, @EndDate DATE)
       RETURNS TABLE
       AS
       RETURN 
       (
      	 SELECT ProductID, SUM(QuantitySold) as TotalSold
      	 FROM Sales
      	 WHERE SaleDate BETWEEN @StartDate AND @EndDate
      	 GROUP BY ProductID
       );
       
  7. Performance Considerations: While functions enhance modularity, it's crucial to use them judiciously. Over-reliance, especially on scalar functions in large datasets, can impact performance. Always monitor and optimize functions to ensure they align with performance goals.
Functions in SQL Server 2016 act as pivotal instruments, ushering in an era of modular and organized SQL programming. By compartmentalizing logic, promoting code reuse, and ensuring data security, functions underscore the principle of efficient and effective database design and operation.


Database Engine's Ability to Support Functions

When you are working with database engines with capabilities beyond just retrieving your data, it makes sense to take advantage of these features. Many of these additonal features are derived from many of the elements that have already been covered, such as clauses in the SELECT statement, joins, and views. Another extremely useful capability is provided by an engine's ability to support functions. Functions are predefined actions that can be used on the values that you send the engine. For example, at a very simple level, you can query the database with the following, having the database complete some addition for you:

SELECT 5 + 5 

The result would be an unnamed column with a value of 10. The engine actually figures out the results for you, returning them as the answer to your "query." In this module, you will learn how to use several different kinds of functions, providing help for character-based, numeric, and date-formatted values.

Math Functions

The available math functions in SQL are fairly limited, which reflects the use of SQL as a tool for pulling out the raw data to answer questions. Any actual processing of the answers is really something left to high-level programming languages in middleware or front-end applications. For example, a Web server might connect to a database to pull back the name of a customer and credit card details, but you would not expect the database to process the card details. You start by looking at the four basic math functions supported by all flavors of SQL, and then you look at some more sophisticated math functions, which, although not part of the SQL standards, are commonly available on most database systems.

Function Operator
Multiply *
Divide /
Add +
Subtract -

What is not obvious from this list is the order of precedence of the operators.

SQL Queries - Scalar Functions

SQL scalar functions return a single value, based on the input value. The most useful scalar functions are the following:
  1. UCASE() - converts a field to upper case;
  2. LCASE() - converts a field to lower case;
  3. LEN() - returns the length of a text field;
  4. ROUND() - rounds a numeric field to the number of decimals specified.
In the following paragraphs we will an example of each scalar functions. In the first example we will use simultaneously UCASE() and LCASE() functions. However, there is an additional issue, because we must use a different statement for each specific database. We will starting by presenting the statement for MySQL. Select UCASE(name) as UPPERName, LCASE(name) as LOWERName from Customers;

SEMrush Software