Effect of Functions on Performance

Whenever a function is performed on a column, the server cannot use an index to resolve a query based on that column. To devise a “plan” to answer your query in the least amount of time, the server must act based on information available to it before the query runs. Because a function will take some action on a column, the server can’t know what the data will be until after the query runs. Using functions in the SELECT list is OK, but using them in the WHERE clause can cause performance problems.

Function Examples

The purpose of this section is to give you a list of examples to observe how each function is used in a real-world situation. At the end of this section is a table summarizing each function, its parameters, and what it’s good for.

Within each function section, I’ve listed the functions in order from “really useful” to “cool, but I don’t use it that much.” It’s OK to browse through this section, looking at each function and getting an idea of what it does. You might want to just skip through and look at the code segments.

String Functions

String functions let you manipulate character data. Browse through the examples provided here to get an idea of how you might use them in your own programs.

UPPER and LOWER

Converts a string to all uppercase or all lowercase.

select       upper(au_fname) “Allcaps”
from         authors
where   au_id   = ‘409-56-7008’

Allcaps
--------------------
ABRAHAM

These functions are especially useful for matching strings in “dirty” text fields where case can be anything. For example, if data entry operators had input names into a table using some uppercase, some lowercase, and some first-letter caps, this query would find the name McEwan, MCEWAN, or Mcewan:

select         customer_id
from           customers
where         upper(cust_lname) = “MCEWAN”

---

Technical Note:  If you perform a function on a column, the server is unable to use an index to resolve the query. The server must decide on a plan of action before running your query and doesn’t know what the result will be of upper(cust_lname), for example. So it must scan the table to answer the query.

If you often need to search a table for uppercase names, you may want to keep two copies of the name: an uppercase name and a mixed-case name. Put an index on the uppercase name.

---

SUBSTRING(source, start, length), RIGHT(source, length)

Extracts a string from the middle of a larger string (SUBSTRING) or from the end of a string (RIGHT). There is no LEFT function. Useful for manipulating strings in columns where several fields have been concatenated into a single column. Although this is a bad database design practice, dirty data crops up all the time in the real world. Using these functions is often the heart of a data-cleansing effort.

Suppose I have just received a table, “customers,” with a single column, “info.” The first six characters contain the customer ID, and the last fifteen contain the customer’s name, with some useless (to me) data in the middle. To select the information I want, I could use this query:

select  substring(info, 1, 6) “CustID”,
        right(info, 15) Name
from    customers

CustID  Name
------  --------------
123125  Pyramid Cnsltg
123126  Creamy Dairy
[…]

SUBSTRING takes three arguments: the source string, where to start (1 is the first character), and how many characters to take. RIGHT takes two arguments: the source string and how many characters to take.

RTRIM (< source string >), LTRIM (< source string>)

These two functions trim blanks off the beginning of a string (LTRIM) or the end of a string (RTRIM). There is no dual-function TRIM. Note that you can nest functions, though. If I were inserting some data into a table, and the data included blanks at the beginning and end, I would want to trim it to avoid putting blanks in. Even if the column is defined as a varchar column, if you explicitly insert blanks in the string, the row will retain it.

This example doesn’t insert—we’ll get to that on Day 14. Instead, here is how you might trim the blanks off the beginning and end of a column:

select    ltrim(rtrim(title))
from      titles

Both functions accept a single parameter: the string to be trimmed.

STR(<source number>, [length of output], [scale])

Converts numeric data to a string. There is no VAL function to convert strings to numbers, but the generic CONVERT function will accomplish either goal. STR takes three parameters: source number, length of the new string that will be created by the function, and the number of digits to the right of the decimal.

Select    “The price is “ + str(price, 6, 3)
from      titles

------------------
The price is 19.990
The price is 11.950
The price is  2.990
The price is 19.990
The price is 19.990
The price is  2.990

The STR() function is useful for converting numeric data in a way that looks nice. The CONVERT function will convert numeric data to a string, but it won’t add insignificant zeroes or line up all the decimal points.

ASCII(<source character >) and CHAR(<source tinyint>)

These functions convert a single character to its ASCII code (ASCII) or an ASCII code to its character. If you pass more than one character to the ASCII function, it returns the code for the first letter only.

select ascii(‘A’), char(65)

-----------   -
65            A

The char() function can be useful for inserting keycodes that cannot be easily typed, such as a TAB or CR/LF.