SQL Functions

5 Key excerpts on "SQL Functions"

• 

  eBook - ePub

  The Real MCTS SQL Server 2008 Exam 70-433 Prep Kit

  Database Design

  • Mark Horninger(Author)
  • 2009(Publication Date)
  • Syngress

    (Publisher)

  For small requests, such as the one described in the preceding example, the use of simple queries fits the application needs perfectly, and the management of their code is easy. However, as time passes and your databases grow larger and more complex, simple queries will not satisfy the needs of your application. In addition, maintaining the code for T-SQL queries in this complex environment becomes a difficult and stressful job. As a database developer, you should be aware that a rich programming logic will be necessary to accomplish this task and to maintain the code in an efficient way.

  To help us achieve these goals, Microsoft SQL Server provides three programmable objects focused on giving us ways to develop and maintain a rich programming logic in our T-SQL queries: functions, stored procedures, and triggers. Besides the built-in set of functions that we saw in Chapter 2 , you can create user-defined functions (UDFs) to group pieces of code that perform some processing and results in a scalar value or a set of rows; this code will be reused across the application. Stored procedures also allow you to group pieces of code, but the return is not mandatory, and it allows us the creation of a secure interface between database and application. Triggers are event-responsive codes, which are executed according to database events.

  In this chapter, we will present more information on these objects, including their features and uses. We will focus on the creation and management of these objects, and some best practices in preparation for creating them in the real world.

  Implementing Functions

  Chapter 2 showed you some of the most common built-in functions that can be used in your application, providing great functionality and minimizing the coding task. For example, you can use the GETDATE( ) function to return the current system date and time for use in your INSERT statements, or the AVG( ) function to return the average value of a column in your SELECT .

  Although Microsoft SQL Server provides a great set of built-in functions, you can also create your own function to encapsulate a routine that performs an important action in your application, such as a complex calculation. For example, you can create a function that calculates the number of hours an employee worked in a month, based on his or her work schedule.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  The SQL Workshop

  A New, Interactive Approach to Learning SQL

  • Frank Solomon, Prashanth Jayaram, Awni Al Saqqa(Authors)
  • 2019(Publication Date)
  • Packt Publishing

    (Publisher)

  The complexity of the software we build expands as we build it. In part, this becomes unavoidable because the mass of the software itself also expands. However, we can definitely avoid the complexity of repeating blocks of code. Modern software development products give us a way to place one copy of a repeating block of code in one defined location. Then, in the software, we can replace all repeated instances of that code block with a call to that one defined copy of the code. We call this one defined block of software a function. Think of a function as a box that takes in zero or more values and returns one or more values in a structured way. A function simplifies software, and it makes the software we build much easier to maintain, repair, and enhance. If a program has the same identical code block repeated one hundred times, the same required change to all of those blocks would require the same work and testing—one hundred times. If we replace those blocks with one hundred calls to a single function, and in some way call the function when and where we need it, we can change and test the function code in one location. This would instantly give us a incredibly huge saving in time, effort, and expense. MySQL offers built-in functions that we can directly call in the Workbench query windows and in the stored procedures we build. MySQL and SQL products generally, include built-in functions that include the following categories:

  • Date and time
  • Datatype conversion
  • Mathematical
  • Statistical
  • String

  Even better, MySQL offers us a way to build our own custom functions. We'll see both types of MySQL Functions. For example, we could easily build MySQL Functions that calculate the number of hours between the time of function execution and the first second of January 1 of that year. These functions could have major value for finance, insurance, and inventory management applications. SQL Functions offer many of the advantages of SQL stored procedures as described earlier, but a function does not offer the exact same advantages and flexibility. Functions can't update or delete table data. Functions can't call stored procedures. Additionally, external programs and applications can't call a SQL function as easily.

  Launch MySQL and type these statements in the query window: SET @var1 = NULL, @var2 = 'A test string';    #  1.      Declare variables and                             #          assign a value to a variable SET @var1 = 3;         #  2. Set @var1 to 3 SELECT LOG(@var1);         #  3. Select the natural log of @var1 SELECT @var1;         #  4. @var1 did not change SELECT EXP(LOG(@var1));     #  5. Nested function calls: natural log, then exponential SELECT LOG(EXP(@var1));     #  6. Nested function calls: exponential, then natural log SELECT @var1;         #  7. @var1 did not change SET @var1 = @var1 * 5;     #  8. @var1 has a new value SELECT @var1;         #  9. Select @var1 SELECT @var2;         # 10. Set @var2 = natural log of @var1 SELECT UPPER(@var2);     # 11. Select UPPER CASE @var2

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Learn T-SQL From Scratch

  An Easy-to-Follow Guide for Designing, Developing, and Deploying Databases in the SQL Server and Writing T-SQL Queries Efficiently

  • Brahmanand Shukla(Author)
  • 2021(Publication Date)
  • BPB Publications

    (Publisher)

  T-SQL is a strong tool. But just like other tools, it has no meaning unless the person using it knows how to use it. Built-in functions are one of such useful features of SQL Server which gives more out of just a plain raw data. You can play with your data and generate meaningful analytics out of it with the help of these functions. There are many such features that we’ll talk about gradually as we’ll move ahead.

  Structure

  In this chapter, we will cover the following topics:

  • Aggregate functions
  • String functions
  • Numeric functions
  • Date functions

  Objective

  After studying this chapter, you’ll be able to work with the various built-in functions ranging from aggregate functions used for aggregation, string functions used with the string’s values, the numeric function used with the numeric values, and date functions used with the date values. These string, numeric, and date values could be a static value or a constant, a variable, or a column. By the end of this chapter, you’ll be able to implement these built-in functions.

  Aggregate functions

  Before we understand the aggregate functions, let’s first understand what a function is: A function is a block of organized and reusable code used to perform predefined actions and return a value.

  For example, if you want the addition of two values then you can create a function to accept two input parameters value 1 and value 2 and the function will perform value 1 + value 2 and return the result. Suppose you supplied value 1 as 100 and value 2 as 200 then the function will return the result as 100 + 200 = 300

  Sign up to read

  Learn more about book
• 

  No longer available |Learn more

  SQL Pocket Primer

  • Oswald Campesato(Author)
  • 2022(Publication Date)
  • Mercury Learning and Information

    (Publisher)

  multiple-row functions that return one result per set of rows. Specifically, single-row functions in SQL will

  • manipulate data items
  • accept arguments and return one value
  • act on each row that is returned
  • return one result per row
  • may modify the data type
  • can be nested
  • accept arguments that can be a column or an expression

  SQL Character Functions

  There two types of character functions: case-manipulation functions and character manipulation functions. Case manipulation functions in SQL include the following:

  • LOWER
  • UPPER
  • INITCAP

  Character manipulation functions in SQL include the following built-in functions:

  • SUBSTRING
  • LENGTH
  • INSTR
  • LPAD | RPAD
  • TRIM
  • REPLACE

  Following this section are some one-line examples of some of the preceding built-in functions, where you need to replace my_table with a suitable table name and replace fname and phone_number with attributes from your table in your database:

  Remove leading spaces:

  SELECT LTRIM(fname) from my_table;

  Remove leading and trailing spaces:

  SELECT TRIM(fname) from my_table;

  Replace “-” with a space (“ ”):

  SELECT fname, REPLACE(phone_number, '-', ' ') as p_number FROM my_table;

  SQL supports built-in number functions, include the following functions:

  • ROUND
  • TRUNC
  • MOD

  An example of the built-in truncate() function (which is different from the TRUNCATE keyword) is as follows:

  -- the value 12.345 is replaced with 12:

  SELECT TRUNCATE (average, 0) from my_table;

  String Operators in SQL

  SQL supports the following string operators that perform the concatenation of strings and partial matches of strings against meta characters:

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  SQL Functions Programmer's Reference

  • Alex Kriegel, Arie Jones, Ryan K. Stephens, Ronald R. Plew, Robert F. Garrett(Authors)
  • 2005(Publication Date)
  • Wrox

    (Publisher)

  With that thought, the proper implementation of indexes and constraining excessive data in the result set can effectively manage the amount of disk I/O that your functions initiate. Examining the Impact of SQL Functions in SQL Statements The impact of functions on performance, however, are not all bad. Functions are precompiled code that have their execution plans already determined and laid out ahead of time. They can provide positive benefits if used in the right way. 631 Understanding the Impact of SQL Functions on Query and Database Performance SELECT Clause Functions in the SELECT clause can provide a benefit in performance over using more complicated logic to compile results. Consider the following simple query for example: SELECT SALES.EMPID, EMPLOYEES.EMPNAME, AVG(SALES.SALES) FROM SALES, EMPLOYEES WHERE SALES.EMPID = EMPLOYEES.EMPID GROUP BY SALES.EMPID ORDER BY EMPNAME; In this simple statement, the built-in AVG function provides us with an easy-to-implement (and fast) way in which to calculate the average of each employee’s sales. How would you implement this without the use of the AVG function? More than likely, you would have to use cursors and temporary tables to get the same aggregate information provided by the AVG function. This would not only be less efficient in terms of things like locking (because of the cursors) but would also have to be compiled by the RDBMS when executed. This would make extra overhead on the RDBMS (such as parsing, determining a query plan, and executing the excess code). FROM Clause Using table-valued functions or functions that return table or rowset objects in the FROM clause is almost always the place in your queries that can take the biggest hit as far as performance is concerned. This is really caused by a two-fold problem. The first part of the problem occurs when the resultant rowset is very large. Large rowsets are held in memory, and the more memory they consume, the less there is for the other queries to use.

  Sign up to read

  Learn more about book