Now that you have the knowledge to write simple SELECT statements, it is time to explore some of the other features of T-SQL that allow you to manipulate how the data is displayed, filtered, or ordered. To create expressions in T-SQL, you use functions and operators along with literal values and columns. The reasons for using expressions in T-SQL code are many. For example, you may want to display only the year of a column of the DATETIME data type on a report, or you may need to calculate a discount based on the order quantity in an order-entry application. Any time the data must be displayed, filtered, or ordered in a way that is different from how they are stored, you can use expressions and functions to manipulate the results.

You will find a very rich and versatile collection of functions and operators available to create expressions that manipulate strings and dates and much more. You can use expressions in the SELECT, WHERE, and ORDER BY clauses as well as in other clauses you will learn about in Chapter 7.

Expressions Using Operators

You learned how to use several comparison operators in the WHERE clause in Chapter 3. In this section, you will learn how to use operators to concatenate strings and perform mathematical calculations in T-SQL queries.

Concatenating Strings

The concatenation operator (+) allows you to connect two strings. The syntax is simple: <string or column name> + <string or column name>. Start up SQL Server Management Studio if it is not already running, and connect to your development server. Open a new query window, and type in and execute the code in Listing 4-1.

Listing 4-1. Concatenating Strings

--1
SELECT 'ab' + 'c';

--2
SELECT BusinessEntityID, FirstName + ' ' + LastName AS "Full Name"
FROM Person.Person;

--3
SELECT BusinessEntityID, LastName + ', ' + FirstName AS "Full Name"
FROM Person.Person;

Figure 4-1 shows the partial results of running Listing 4-1. Query 1 shows that you can concatenate two strings. Queries 2 and 3 demonstrate concatenating the LastName and FirstName columns along with either a space or a comma and space. Notice that you specified the alias, Full Name, to provide a column header for the result of the expressions combining FirstName and LastName. If you did not provide the alias, the column header would be (No column name), as in query 1. In reality, it doesn’t have a name at all, so it is very important that you always alias expressions.

Concatenating Strings and NULL

In Chapter 3 you learned about the challenges when working with NULL in WHERE clause expressions. When concatenating a string with a NULL, NULL is returned. Listing 4-2 demonstrates this problem. Type the code in Listing 4-2 into a new query window and execute it.

Listing 4-2. Concatenating Strings with NULL Values

SELECT BusinessEntityID, FirstName + ' ' + MiddleName +
    ' ' + LastName AS "Full Name"
FROM Person.Person;

Figure 4-2 shows the results of Listing 4-2. The query combines the FirstName, MiddleName, and LastName columns into a Full Name column. The MiddleName column is optional; that is, NULL values are allowed. Only the rows where the MiddleName value has been entered show the expected results. The rows where MiddleName is NULL return NULL. You will learn how to fix this problem later in this chapter.

CONCAT

SQL Server 2012 introduced another powerful tool for concatenating strings and even nonstring values. The CONCAT function takes any number of values as arguments and automatically concatenates them together. The values can be passed to the CONCAT function as variables, columns or as literal values. The output is always implicitly converted to a string data type. Run the code in Listing 4-3 to see how to use the CONCAT function.

Listing 4-3. CONCAT Examples

--1 Simple CONCAT function
SELECT CONCAT ('I ', 'love', ' writing', ' T-SQL') AS RESULT;

--2 Using variable with CONCAT
DECLARE @a VARCHAR(30) = 'My birthday is on '
DECLARE @b DATE = '1980/08/25'
SELECT CONCAT (@a, @b) AS RESULT;

--3 Using CONCAT with table rows
SELECT CONCAT (AddressLine1, PostalCode) AS Address
FROM Person.Address;

--4 Using CONCAT with NULL
SELECT CONCAT ('I',' ','love', ' ', 'using',' ','CONCAT',' ',
    'because',' ','NULL',' ','values',
    ' ','vanish',' ','SEE:',NULL,'!') AS RESULT;

Query 1 command simply concatenates four separate string values. Query 2 declares two variables and then concatenates those into a single result. Query 3 uses the CONCAT function in a SELECT clause to concatenate table rows. The final example, query 4, shows that NULL values are ignored when using CONCAT. Figure 4-3 shows the output. I’ve only showed the partial results for the third example.

ISNULL and COALESCE

Two functions are available to replace NULL values with another value. The first function, ISNULL, requires two parameters: the value to check and the replacement for NULL values. COALESCE works a bit differently. COALESCE will take any number of parameters and return the first non-NULL value. T-SQL developers often prefer COALESCE over ISNULL because COALESCE meets ANSI (American National Standards Institute) standards, while ISNULL does not. Also, COALESCE is more versatile. Here is the syntax for the two functions:

ISNULL(<value>,<replacement>)
COALESCE(<value1>,<value2>,…,<valueN>)

Type in and execute the code in Listing 4-4 to learn how to use ISNULL and COALESCE.

Listing 4-4. Using the ISNULL and COALESCE Functions

--1
SELECT BusinessEntityID, FirstName + ' ' + ISNULL(MiddleName,'') +
    ' ' + LastName AS "Full Name"
FROM Person.Person;

--2
SELECT BusinessEntityID, FirstName + ISNULL(' ' + MiddleName,'') +
    ' ' + LastName AS "Full Name"
FROM Person.Person;

--3
SELECT BusinessEntityID, FirstName + COALESCE(' ' + MiddleName,'') +
    ' ' + LastName AS "Full Name"
FROM Person.Person;

Figure 4-4 shows the partial result of running the code. Query 1 uses the ISNULL function to replace any missing MiddleName values with an empty string in order to build Full Name. Notice in the results that whenever MiddleName is missing, you end up with two spaces between FirstName and LastName. Line 3 in the results of query 1 contains two spaces between Kim and Ambercrombie because a space is added both before and after the ISNULL function. To correct this problem, move the space inside the ISNULL function instead of before it: ISNULL(' ' + MiddleName,''). Concatenating a space (or anything really) with NULL returns NULL. When the MiddleName value is NULL, the space is eliminated and no extra spaces show up in your results. Instead of ISNULL, query 3 contains the COALESCE function. If MiddleName is NULL, the next non-NULL value, the empty string, is returned.

Concatenating Other Data Types to Strings

To concatenate nonstring values to strings, the nonstring value must be converted to a string. If the string value can be implicitly converted to a number, the values will be added together instead. Run this statement to see what happens: SELECT 1 + '1';. If the desired result is 11 instead of 2, the numeric value must be explicitly converted to a string using either the CAST or CONVERT function. If you attempt to concatenate a nonnumeric string and a number without converting, you will receive an error message. Run this example to see the error: SELECT 1 + 'a';. This is because integers have higher precedence than strings.

Use one of the functions, CAST or CONVERT, to convert a number or date value to a string. Here is the syntax:

CAST(<value> AS <new data type>)
CONVERT(<new data type>,<value>)

Listing 4-5 demonstrates how to use these functions. Type in and execute the code in a query window.

Listing 4-5. Using CAST and CONVERT

--1
SELECT CAST(BusinessEntityID AS NVARCHAR) + ': ' + LastName
    + ', ' + FirstName AS ID_Name
FROM Person.Person;

--2
SELECT CONVERT(NVARCHAR(10),BusinessEntityID) + ': ' + LastName
    + ', ' + FirstName AS ID_Name
FROM Person.Person;

--3
SELECT BusinessEntityID, BusinessEntityID + 1 AS "Adds 1",
    CAST(BusinessEntityID AS NVARCHAR(10)) + '1'AS "Appends 1"
FROM Person.Person;

Figure 4-5 shows the partial results of running the code. The functions in queries 1 and 2 have very different syntaxes, but they accomplish the same result. They both change the BusinessEntityID values from integers into a string data type (NVARCHAR) so that it can be concatenated to a string. Many programmers prefer CAST over CONVERT because CAST is compliant with the ANSI SQL-99 standard. Query 1 specifies just NVARCHAR as the data type without a size. By default, the maximum length will be 30 characters. If you need to cast to a value more than 30 characters, you must specify a length argument greater than 30. As a best practice, always specify the length. Query 3 demonstrates the difference between converting the numeric value and not converting it. For more information about CONVERT, take a look at the “CONVERT” section later in the chapter.

Developers must often concatenate strings for reports or for loading data from one system to another. Now practice what you have learned about concatenating strings within a T-SQL query by completing Exercise 4-1.

Using Mathematical Operators

You can use several operators to perform simple mathematical operations on numeric values. Use the plus symbol (+) to perform addition, the hyphen (–) to perform subtraction, the asterisk (*) to perform multiplication, and the slash (/) to perform division. One operator that may be new to you is the modulo (%) operator, which returns the remainder when division is performed on the two values. For example, 5 % 2 returns 1 because 1 is the remainder when you divide 5 by 2. One common use for modulo is to determine whether a number is odd or even when the second value in the expression is 2. If the result is 1, then the value is odd; if the result is 0, then the value is even. Listing 4-6 shows how to use some of the mathematical operators. Type in and execute the code to see the results.

Listing 4-6. Using Mathematical Operators

--1
SELECT 1 + 1 AS ADDITION, 10.0 / 3 AS DIVISION, 10 / 3 AS [Integer Division], 10 % 3 AS MODULO;

--2
SELECT OrderQty, OrderQty * 10 AS Times10
FROM Sales.SalesOrderDetail;

--3
SELECT OrderQty * UnitPrice * (1.0 - UnitPriceDiscount)
    AS Calculated, LineTotal
FROM Sales.SalesOrderDetail;

--4
SELECT SpecialOfferID,MaxQty,DiscountPct,
    DiscountPct * ISNULL(MaxQty, 1000) AS MaxDiscount
FROM Sales.SpecialOffer;

Take a look at the results shown in Figure 4-6. Query 1 shows how to perform calculations on literal values. Note that 10 / 3 does not equal 3.333. Because the two operators in that expression are integers, the result is also an integer. Query 2 shows the result of multiplying the values stored in the OrderQty column by 10.

Query 3 replicates the precalculated LineTotal column by calculating the value by using an expression. The LineTotal column is a “computed column.” The actual formula used in the table definition looks a bit more complicated than the one I used since it checks for NULL values. The simplified formula I used requires parentheses to enforce the logic, causing subtraction to be performed before multiplication. Because multiplication has a higher precedence than subtraction, use parentheses to enforce the intended logic. Query 4 shows how to use the ISNULL function to substitute the value 1000 when the MaxQty is NULL before multiplying by the DiscountPct value.

Practice what you have learned about mathematical operators to complete Exercise 4-2.

When using operators, you must keep the data types of the values in mind. When performing an operation that involves two different data types, the expression will return values for the data type with the highest precedence if possible. What value can be rolled into the other value? For example, an INT can be converted to a BIGINT, but not the other way around. In other words, if a value can be a valid INT, it is also a valid BIGINT. However, many valid BIGINT values are too big to be converted to INT. Therefore, when an operation is performed on a BIGINT and an INT, the result will be a BIGINT.

It is not always possible to convert the lower precedence data type to the higher precedence data type. A character can’t always be converted to a numeric value. This is why the expression 1 + 'a' fails. For a list of possible data types in order of precedence, see the article “Data Type Precedence” in SQL Server’s help system, Books Online.

Using String Functions

You will find a very rich set of T-SQL functions for manipulating strings. You often have a choice of where a string will be manipulated. If the manipulation will occur on one of the columns in the select-list, it might make sense to utilize the client to do the work if the manipulation is complex, but it is possible to do quite a bit of manipulation with T-SQL. You can use the string functions to clean up data before loading them into a database. This section covers many of the commonly used string functions. You can find many more in Books Online.

RTRIM and LTRIM

The RTRIM and LTRIM functions remove spaces from the right side (RTRIM) or left side (LTRIM) of a string data types, respectively. You may need to use these functions when working with fixed-length data types (CHAR and NCHAR) or to clean up flat-file data before it is loaded from a staging database into a data warehouse. The syntax is simple:

RTRIM(<string>)
LTRIM(<string>)

Type in and execute the code in Listing 4-7. The first part of the code creates and populates a temporary table. Don’t worry about understanding that part of the code at this point.

Listing 4-7. Using the RTRIM and LTRIM Functions

--Create the temp table
CREATE TABLE #trimExample (COL1 VARCHAR(10));
GO
--Populate the table
INSERT INTO #trimExample (COL1)
VALUES ('a'),('b  '),('  c'),('  d  '),

--Select the values using the functions
SELECT COL1, '*' + RTRIM(COL1) + '*' AS "RTRIM",
    '*' + LTRIM(COL1) + '*' AS "LTRIM"
FROM #trimExample;

--Clean up
DROP TABLE #trimExample;

Figure 4-7 shows the results of the code. The INSERT statement added four rows to the table with no spaces (a), spaces on the right (b), spaces on the left (c), and spaces on both (d). Inside the SELECT statement, you will see that asterisks surround the values to make it easier to see the spaces in the results. The RTRIM function removed the spaces from the right side; the LTRIM function removed the spaces from the left side. T-SQL doesn’t contain a native function that removes the spaces from both sides of the string, but you will learn how to get around this problem in the section “Nesting Functions” later in the chapter.

LEFT and RIGHT

The LEFT and RIGHT functions return a specified number of characters on the left or right side of a string, respectively. Developers use these functions to parse strings. For example, you may need to retrieve the three-character extension from file path data by using RIGHT. Take a look at the syntax:

LEFT(<string>,<number of characters)
RIGHT(<string>,<number of characters)

Listing 4-8 demonstrates how to use these functions. Type in and execute the code.

Listing 4-8. Using the LEFT and RIGHT Functions

SELECT LastName,LEFT(LastName,5) AS "LEFT",
    RIGHT(LastName,4) AS "RIGHT"
FROM Person.Person
WHERE BusinessEntityID IN (293,295,211,297,299,3057,15027);

Figure 4-8 shows the results. Notice that even if the value contains fewer characters than the number specified in the second parameter, the function still works to return as many characters as possible.

LEN and DATALENGTH

Use LEN to return the number of characters in a string. Developers sometimes use another function, DATALENGTH, incorrectly in place of LEN. DATALENGTH returns the number of bytes in a string. DATALENGTH returns the same value as LEN when the string is a CHAR or VARCHAR data type, which takes one byte per character. The problem occurs when using DATALENGTH on NCHAR or NVARCHAR data types, which take two bytes per character. In this case, the DATALENGTH value is two times the LEN value. This is not incorrect; the two functions measure different things. The syntax is very simple:

LEN(<string>)
DATALENGTH(<string>)

Type in and execute the code in Listing 4-9 to learn how to use LEN and DATALENGTH.

Listing 4-9. Using the LEN and DATALENGTH Functions

SELECT LastName,LEN(LastName) AS "Length",
    DATALENGTH(LastName) AS "Internal Data Length"
FROM Person.Person
WHERE BusinessEntityID IN (293,295,211,297,299,3057,15027);

Figure 4-9 shows the results. The Length column displays a count of the characters, while the Internal Data Length column displays the number of bytes.

CHARINDEX

Use CHARINDEX to find the numeric starting position of a search string inside another string. By checking to see whether the value returned by CHARINDEX is greater than zero, you can use the function to just determine whether the search string exists inside the second value. Developers often use CHARINDEX to locate a particular character, such as the at symbol (@) in an e-mail address column, along with other functions when parsing strings. You will learn more about this in the “Nesting Functions” section later in the chapter. The CHARINDEX function requires two parameters: the search string and the string to be searched. An optional parameter, the start location, instructs the function to ignore a given number of characters at the beginning of the string to be searched. The following is the syntax; remember that the third parameter is optional (square brackets surround optional parameters in the syntax):

CHARINDEX(<search string>,<target string>[,<start location>])

Listing 4-10 demonstrates how to use CHARINDEX. Type in and execute the code to learn how to use this function.

Listing 4-10. Using the CHARINDEX Function

SELECT LastName, CHARINDEX('e',LastName) AS "Find e",
    CHARINDEX('e',LastName,4) AS "Skip 3 Characters",
    CHARINDEX('be',LastName) AS "Find be",
    CHARINDEX('Be',LastName) AS "Find Be"
FROM Person.Person
WHERE BusinessEntityID IN (293,295,211,297,299,3057,15027);

Figure 4-10 shows the results. The Find e column in the results displays the first location of the letter e in the LastName value. The Skip 3 Characters column displays the first location of the letter e when the first three characters of the LastName value are ignored. Finally, the Find be column demonstrates that you can use the function with search strings that are more than one character in length. There are many versions of AdventureWorks available and some of those are case sensitive. If you are using a case-sensitive version, your results will be slightly different. You’ll see different values returned when searching for be and Be.

SUBSTRING

Use SUBSTRING to return a portion of a string starting at a given position and for a specified number of characters. In some ways, it is just a more powerful LEFT function. For example, an order-entry application may assign a customer ID based on the first seven letters of the customer’s last name plus digits 4 to 9 of the phone number. The SUBSTRING function requires three parameters: the string, a starting location, and the number of characters to retrieve. If the number of characters to retrieve is greater than the length of the string, the function will return as many characters as possible. If the start location is past the end of the string, then an empty string, not a NULL, will be returned. Here is the syntax of SUBSTRING:

SUBSTRING(<string>,<start location>,<length>)

Type in and execute the code in Listing 4-11 to learn how to use SUBSTRING.

Listing 4-11. Using the SUBSTRING Function

SELECT LastName, SUBSTRING(LastName,1,4) AS "First 4",
    SUBSTRING(LastName,5,50) AS "Characters 5 and later"
FROM Person.Person
WHERE BusinessEntityID IN (293,295,211,297,299,3057,15027);

Notice in the results shown in Figure 4-11 that if the starting point is located after the available characters (Abel and Bell), an empty string is returned. Otherwise, in this example, the FirstName column is divided into two strings.

CHOOSE

CHOOSE is a function new with SQL Server 2012 that allows you to select a value in an array based on an index. The CHOOSE function requires an index value and list of values for the array. Here is the basic syntax for the CHOOSE function:

CHOOSE ( index, val_1, val_2 [, val_n ] )

The index simply points to the position in the array that you want to return. Listing 4-12 shows a basic example.

Listing 4-12. Using the CHOOSE Function

SELECT CHOOSE (4, 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i'),

Figure 4-12 shows the results of running the CHOOSE function. Keep in mind that the results take the highest data type precedence. This means if there is an integer in the list, the CHOOSE function will try to convert any results to an integer. If the value is a string, the CHOOSE command will throw an error. You will need to convert any integer values in the array to VARCHAR to avoid this error. If the position doesn’t exist, a NULL value will be returned.

REVERSE

REVERSE returns a string in reverse order. I often use it along with the CHARINDEX and RIGHT functions to find a file name from the file’s path. I use CHARINDEX on the reversed string to find the last backslash in the path, which then tells me how many characters, minus 1, on the right side of the string I need to grab. The same method can be used to parse an e-mail address. To see how to do this, see the example in the “Nesting Functions” later in the chapter. Type in and execute this code to learn how to use REVERSE:

SELECT REVERSE('!dlroW ,olleH')

UPPER and LOWER

Use UPPER and LOWER to change a string to either uppercase or lowercase. You may need to display all uppercase data in a report, for example. The syntax is very simple:

UPPER(<string>)
LOWER(<string>)

Type in and execute the code in Listing 4-13 to learn about using UPPER and LOWER.

Listing 4-13. Using the UPPER and LOWER Functions

SELECT LastName, UPPER(LastName) AS "UPPER",
    LOWER(LastName) AS "LOWER"
FROM Person.Person
WHERE BusinessEntityID IN (293,295,211,297,299,3057,15027);

Take a look at the results in Figure 4-13. All LastName values appear in uppercase in the UPPER column, while they appear in lowercase in the LOWER column.

REPLACE

Use REPLACE to substitute one string value inside another string value. REPLACE has three required parameters, but it is very easy to use. Use REPLACE to clean up data; for example, you may need to replace slashes (/) in a phone number column with hyphens (-) for a report. Here is the syntax:

REPLACE(<string value>,<string to replace>,<replacement>)

Type in and execute the code in Listing 4-14 to learn how to use REPLACE.

Listing 4-14. Using the REPLACE Function

--1
SELECT LastName, REPLACE(LastName,'A','Z') AS "Replace A",
    REPLACE(LastName,'A','ZZ') AS "Replace with 2 characters",
    REPLACE(LastName,'ab','') AS "Remove string"
FROM Person.Person
WHERE BusinessEntityID IN (293,295,211,297,299,3057,15027);

--2
SELECT BusinessEntityID,LastName,MiddleName,
    REPLACE(LastName,'a',MiddleName) AS "Replace with MiddleName",
    REPLACE(LastName,MiddleName,'a') AS "Replace MiddleName"
FROM Person.Person
WHERE BusinessEntityID IN (285,293,10314);

Notice in the results shown in Figure 4-14 that the REPLACE function replaces every instance of the string to be replaced. It doesn’t matter if the strings in the second and third parameter are not the same length, as shown in the column Replace with 2 characters. The Remove string column shows a convenient way to remove a character or characters from a string by replacing them with an empty string represented by two single quotes. Because the last name Bell doesn’t contain any of the values to be replaced, the value doesn’t change.

Query 2 demonstrates that the second and third parameters don’t have to be literal values by using the MiddleName column either as the string to replace in the Replace MiddleName column or as the replacement in the Replace with MiddleName column.

Nesting Functions

The previous sections showed how to use one function at a time to manipulate strings. If the results of one expression must be used as a parameter of another function call, you can nest functions. For example, you can nest the LTRIM and RTRIM functions to remove the spaces from the beginning and ending of a string like this: LTRIM(RTRIM(' test ')). Keep in mind when writing nested functions that you work from the inside out. The inner-most function is executed first and the outer functions execute against the results. Let’s look at some examples. Type in and execute the example shown in Listing 4-15 to display the domains in a list of e-mail addresses and the file name from a list of file paths.

Listing 4-15. Nesting Functions

--1
SELECT EmailAddress,
    SUBSTRING(EmailAddress,CHARINDEX('@',EmailAddress) + 1,50) AS DOMAIN
FROM Production.ProductReview;

--2
SELECT physical_name,
    RIGHT(physical_name,CHARINDEX('',REVERSE(physical_name))-1) AS FileName
FROM sys.database_files;

Figure 4-15 shows the results of running this code. Query 1 first uses the CHARINDEX function to find the location of the at symbol (@). The results of that expression are used as a parameter to the outer SUBSTRING function. To display the characters after the @ symbol, add 1 to the position of the @ symbol.

Query 2 finds the file name in a list of file paths. The query performs a SELECT command from the system view sys.database_files. After the SELECT command the inner REVERSE function reverses the string value. Then the outer CHARINDEX finds the location of the backslash () symbol and subtracts one character to remove it from the results. By using that result as the second parameter of the RIGHT function, the query returns the file name. When writing a query like this, take it a step at a time and work from the inside out. You may have to experiment a bit to get it right.

This section covered a sample of the many functions available to manipulate strings in T-SQL. Complete Exercise 4-3 to practice using these functions.

Using Date and Time Functions

Just as T-SQL features a rich set of functions for working with string data, it also boasts an impressive list of functions for working with date and time data types. In this section, you’ll take a look at some of the most commonly used functions for date and time data.

GETDATE and SYSDATETIME

Use GETDATE or SYSDATETIME to return the current date and time of the server. The difference is that SYSDATETIME returns seven decimal places after the second, the datetime2(7) data type, while GETDATE returns only three places, the DATETIME data type.

GETDATE and SYSDATETIME are nondeterministic functions. This means that they return different values each time they are called. Most of the functions in this chapter are deterministic, which means that a function always returns the same value when called with the same parameters and database settings. For example, the code CHARINDEX('B','abcd') will always return 2 if the collation of the database is case insensitive. In a case-sensitive database, the expression will return 0.

Run this code several times to see how these functions work:

SELECT GETDATE(), SYSDATETIME();

DATEADD

Use DATEADD to add a number of time units to a date. The function requires three parameters: the date part, the number, and a date. T-SQL doesn’t have a DATESUBTRACT function, but you can use a negative number to accomplish the same thing. You might use DATEADD to calculate an expiration date or a date that a payment is due, for example. Table 4-1 from Books Online lists the possible values for the date part parameter in the DATEADD function and other date functions. Here is the syntax for DATEADD:

DATEADD(<date part>,<number>,<date>)

Type in and execute the code in Listing 4-16 to learn how to use the DATEADD function.

Listing 4-16. Using the DATEADD Function

--1
SELECT OrderDate, DATEADD(year,1,OrderDate) AS OneMoreYear,
    DATEADD(month,1,OrderDate) AS OneMoreMonth,
    DATEADD(day,-1,OrderDate) AS OneLessDay
FROM Sales.SalesOrderHeader
WHERE SalesOrderID in (43659,43714,60621);

--2
SELECT DATEADD(month,1,'1/29/2009') AS FebDate;

Figure 4-16 shows the results of Listing 4-16. In query 1, the DATEADD function adds exactly the time unit specified in each expression to the OrderDate column from the Sales.SalesOrderHeader table. Notice in the results of query 2 that because there is no 29th day of February 2009, adding one month to January 29, 2009, returns February 28, the last possible day in February that year.

DATEDIFF

The DATEDIFF function allows you to find the difference between two dates. The function requires three parameters: the date part and the two dates. The DATEDIFF function might be used to calculate how many days have passed since unshipped orders were taken, for example. Here is the syntax:

DATEDIFF(<datepart>,<early date>,<later date>)

See Table 4-1 for the list of possible date parts. Listing 4-17 demonstrates how to use DATEDIFF. Be sure to type in and execute the code.

Listing 4-17. Using the DATEDIFF Function

--1
SELECT OrderDate, GETDATE() CurrentDateTime,
    DATEDIFF(year,OrderDate,GETDATE()) AS YearDiff,
    DATEDIFF(month,OrderDate,GETDATE()) AS MonthDiff,
    DATEDIFF(d,OrderDate,GETDATE()) AS DayDiff
FROM Sales.SalesOrderHeader
WHERE SalesOrderID in (43659,43714,60621);

--2
SELECT DATEDIFF(year,'12/31/2008','1/1/2009') AS YearDiff,
    DATEDIFF(month,'12/31/2008','1/1/2009') AS MonthDiff,
    DATEDIFF(d,'12/31/2008','1/1/2009') AS DayDiff;

Figure 4-17 shows the results. Your results from query 1 will be different from mine because the query uses GETDATE(), a nondeterministic function, instead of hard-coded dates or dates from a table. Remember that you can use the date part name or abbreviate it. The examples abbreviate “day” with “d.” Even though query 2 compares the difference between two dates that are just one day apart, the differences in years and months are both 1. The DATEDIFF rounds up the result to the nearest integer and doesn’t display decimal results.

DATENAME and DATEPART

The DATENAME and DATEPART functions return the part of the date specified. Developers use the DATENAME and DATEPART functions to display just the year or month on reports, for example. DATEPART always returns an integer value. DATENAME returns a character string, the actual name when the date part is the month or the day of the week. Again, you can find the possible date parts in Table 4-1. The syntax for the two functions is similar:

DATENAME(<datepart>,<date>)
DATEPART(<datepart>,<date>)

Type in and execute the code in Listing 4-18 to learn how to use DATENAME and DATEPART.

Listing 4-18. Using the DATENAME and DATEPART Functions

--1
SELECT OrderDate, DATEPART(year,OrderDate) AS OrderYear,
    DATEPART(month,OrderDate) AS OrderMonth,
    DATEPART(day,OrderDate) AS OrderDay,
    DATEPART(weekday,OrderDate) AS OrderWeekDay
FROM Sales.SalesOrderHeader
WHERE SalesOrderID in (43659,43714,60621);

--2
SELECT OrderDate, DATENAME(year,OrderDate) AS OrderYear,
    DATENAME(month,OrderDate) AS OrderMonth,
    DATENAME(day,OrderDate) AS OrderDay,
    DATENAME(weekday,OrderDate) AS OrderWeekDay
FROM Sales.SalesOrderHeader
WHERE SalesOrderID in (43659,43714,60621);

Figure 4-18 displays the results. You will see that the results are the same except for spelling out the month and weekday in query 2. One other thing to keep in mind is that the value returned from DATEPART is always an integer, while the value returned from DATENAME is always a string, even when the expression returns a number.

DAY, MONTH, and YEAR

The DAY, MONTH, and YEAR functions work just like DATEPART. These functions are just alternate ways to get the day, month, or year from a date. Here is the syntax:

DAY(<date>)
MONTH(<date>)
YEAR(<date>)

Type in and execute the code in Listing 4-19 to see that this is just another way to get the same results as using the DATEPART function.

Listing 4-19. Using the DAY, MONTH, and YEAR Functions

SELECT OrderDate, YEAR(OrderDate) AS OrderYear,
    MONTH(OrderDate) AS OrderMonth,
    DAY(OrderDate) AS OrderDay
FROM Sales.SalesOrderHeader
WHERE SalesOrderID in (43659,43714,60621);

Figure 4-19 displays the results of the code from Listing 4-19. If you compare the results of query 1 from Listing 4-18 to that when the DATEPART function was used, you will see that they are the same.

CONVERT

You learned about CONVERT earlier in the chapter when I talked about concatenating strings. To append a number or a date to a string, the number or date must first be cast to a string. The CONVERT function has an optional parameter called style that can be used to format a date or number.

I have frequently seen code that used the DATEPART function to break a date into its parts and then cast the parts into strings and concatenate them back together to format the date. It is so much easier just to use CONVERT to accomplish the same thing! Here is the syntax:

CONVERT(<data type, usually varchar>,<date>,<style>)

Type in and execute the code in Listing 4-20 to compare both methods of formatting dates. Take a look at the SQL Server Books Online article “CAST and CONVERT” for a list of all the possible formats.

Listing 4-20. Using CONVERT to Format a Date/Time Value

--1 The hard way!
SELECT CAST(DATEPART(YYYY,GETDATE()) AS VARCHAR) + '/' +
    CAST(DATEPART(MM,GETDATE()) AS VARCHAR) +
    '/' +  CAST(DATEPART(DD,GETDATE()) AS VARCHAR) AS DateCast;

--2 The easy way!
SELECT CONVERT(VARCHAR,GETDATE(),111) AS DateConvert;

--3
SELECT CONVERT(VARCHAR,OrderDate,1) AS "1",
    CONVERT(VARCHAR,OrderDate,101) AS "101",
    CONVERT(VARCHAR,OrderDate,2) AS "2",
    CONVERT(VARCHAR,OrderDate,102) AS "102"
FROM Sales.SalesOrderHeader
WHERE SalesOrderID in (43659,43714,60621);

Figure 4-20 shows the results of Listing 4-20. Notice in query 1 that you not only had to use DATEPART three times, but you also had to cast each result to a VARCHAR in order to concatenate the pieces back together. Query 2 shows the easy way to accomplish the same thing. This method is often used to remove the time from a DATETIME data type. Query 3 demonstrates four different formats. Notice that the three-digit formats always produce four-digit years.

FORMAT

SQL Server 2012 introduced the FORMAT function. The primary purpose is to simplify the conversion of date/time values as string values. Another purpose of the format function is to convert date/time values to their cultural equivalencies. This function is easy to use and is similar to other programming languages. If performance is important, however, you will want to stick with using the CONVERT function as covered earlier in the chapter. Here is the syntax:

FORMAT(value, format [, culture ])

The FORMAT function greatly simplifies how date/time values are converted, and it should be used for date/time values instead of the CAST or CONVERT functions. Listing 4-21 shows some examples.

Listing 4-21. FORMAT Function Examples

DECLARE @d DATETIME = GETDATE();

SELECT FORMAT( @d, 'dd', 'en-US' ) AS Result;
SELECT FORMAT( @d, 'yyyy-M-d') AS Result;
SELECT FORMAT( @d, 'MM/dd/yyyy', 'en-US' ) AS Result;

Figure 4-21 shows the results. Keep in mind the letters for each part of the date are case sensitive. For example, if you switch mm for MM you will get back minutes instead of months.

DATEFROMPARTS

SQL Server 2012 also introduced a simple method to derive a date, time, or date and time from a list of values. The primary function is called DATEFROMPARTS but there is also a version of the function for each date or time data type. Listing 4-22 shows some examples.

Listing 4-22. DATEFROMPARTS Examples

SELECT DATEFROMPARTS(2012, 3, 10) AS RESULT;
SELECT TIMEFROMPARTS(12, 10, 32, 0, 0) AS RESULT;
SELECT DATETIME2FROMPARTS (2012, 3, 10, 12, 10, 32, 0, 0) AS RESULT;

Figure 4-22 shows the results from each function. The first function returns only the date. The TIMEFROMPARTS function returns a time. Finally, the DATETIME2FROMPARTS returns both a date and a time. If a value is out of the range of either a date or time (for example, if you were to type 13 for the month value), the function will throw an error.

EOMONTH

One of the most useful functions introduced with SQL Server 2012 is the EOMONTH function. It returns the date of the last day of the supplied month argument. You can also supply an offset to return the end of the month for another month. Here is an example that you can run to see how EOMONTH works:

SELECT EOMONTH(GETDATE()) AS [End of this month],
    EOMONTH(GETDATE(),1) AS [End of next month],
    EOMONTH('2009-01-01') AS [Another month];

This section covered a sample of the functions available for manipulating dates. Practice what you have learned by completing Exercise 4-4.

Using Mathematical Functions

You can use several mathematical functions on numeric values. These include trigonometric functions such as SIN and TAN and logarithmic functions that are not used frequently in business applications. This section discusses some of the more commonly used mathematical functions.

ABS

The ABS function returns the absolute value of the number—the distance between the number and zero. Type in and execute this code to see how to use ABS:

SELECT ABS(2) AS "2", ABS(-2) AS "-2"

POWER

The POWER function returns the power of one number to another number. The syntax is simple:

POWER(<number>,<power>)

There may not be many uses for POWER in business applications, but you may use it in scientific or academic applications. Type in and execute the code in Listing 4-23.

Listing 4-23. Using the POWER Function

SELECT POWER(10,1) AS "Ten to the First",
    POWER(10,2) AS "Ten to the Second",
    POWER(10,3) AS "Ten to the Third";

Figure 4-23 displays the results. The POWER function returns a FLOAT value. Caution must be taken, however, with this function. The results will increase in size very quickly and can cause an overflow error. Try finding the value of 10 to the 10th power to see what can happen.

SQUARE and SQRT

The SQUARE function returns the square of a number, or the number multiplied to itself. The SQRT function returns the opposite, the square root of a number. Type in and execute the code in Listing 4-24 to see how to use these functions.

Listing 4-24. Using the SQUARE and SQRT Functions

SELECT SQUARE(10) AS "Square of 10",
    SQRT(10) AS "Square Root of 10",
    SQRT(SQUARE(10)) AS "The Square Root of the Square of 10";

Figure 4-24 shows the results. Notice that the third expression in the query is a nested function that squares 10 and then takes the square root of that result.

ROUND

The ROUND function allows you to round a number to a given precision. The ROUND function is used frequently to display only the number of decimal places required in the report or application. The ROUND function requires two parameters, the number and the length, which can be either positive or negative. It also has an optional third parameter that causes the function to just truncate instead of rounding if a nonzero value is supplied. Here is the syntax:

ROUND(<number>,<length>[,<function>])

Type in and execute the code in Listing 4-25 to learn how to use ROUND.

Listing 4-25. Using the ROUND Function

SELECT ROUND(1234.1294,2) AS "2 places on the right",
    ROUND(1234.1294,-2) AS "2 places on the left",
    ROUND(1234.1294,2,1) AS "Truncate 2",
    ROUND(1234.1294,-2,1) AS "Truncate -2";

You can view the results in Figure 4-25. When the expression contains a negative number as the second parameter, the function rounds on the left side of the decimal point. Notice the difference when 1 is used as the third parameter, causing the function to truncate instead of rounding. When rounding 1234.1294, the expression returns 1234.1300. When truncating 1234.1294, the expression returns 1234.1200. It doesn’t round the value; it just changes the specified digits to zero.

RAND

RAND returns a float value between 0 and 1. RAND can be used to generate a random value. This might be used to generate data for testing an application, for example. The RAND function takes one optional integer parameter, @seed. When the RAND expression contains the seed value, the function returns the same value each time. If the expression doesn’t contain a seed value, SQL Server randomly assigns a seed, effectively providing a random number. Type in and execute the code in Listing 4-26 to generate a random number.

Listing 4-26. Using the RAND Function

SELECT CAST(RAND() * 100 AS INT) + 1 AS "1 to 100",
    CAST(RAND()* 1000 AS INT) + 900 AS "900 to 1900",
    CAST(RAND() * 5 AS INT)+ 1 AS "1 to 5";

Because the function returns a float value, multiply by the size of the range and add the lower limit (see Figure 4-26). The first expression returns random numbers between 1 and 100. The second expression returns random numbers between 900 and 1900. The third expression returns random values between 1 and 5.

If you supply a seed value to one of the calls to RAND within a batch of statements, that seed affects the other calls. The value is not the same, but the values are predictable. Run this statement several times to see what happens when a seed value is used:

SELECT RAND(3),RAND(),RAND();

If you leave out the seed, you will see different results each time. Another interesting thing is that, even though you see different values in each column, you will see the same values in each row. Run this statement multiple times to see what happens:

SELECT RAND(),RAND(),RAND(),RAND()
FROM sys.objects;

Just like strings and dates, you will find several functions that manipulate numbers. Practice using these functions by completing Exercise 4-5.

Logical Functions and Expressions

T-SQL contains a number of functions and the CASE expression that allow you to add conditional expressions to a query. You can return a value that depends on another value or the results of an expression. These techniques are similar to using IF. . .THEN, CASE or SWITCH in other programming languages.

The CASE Expression

Use the CASE expression to evaluate a list of expressions and return the first one that evaluates to true. For example, a report may need to display the season of the year based on one of the date columns in the table. CASE is similar to Select Case or Switch used in other programming languages, but it is used inside the statement.

There are two ways to write a CASE expression: simple or searched. The following sections will explain the differences and how to use them.

Simple CASE

To write the simple CASE expression, come up with an expression that you want to evaluate, often a column name, and a list of possible values. Here is the syntax:

CASE <test expression>
    WHEN <comparison expression1> THEN <return value1>
    WHEN <comparison expression2> THEN <return value2>
    [ELSE <value3>] END

Type in and execute the code in Listing 4-27 to learn how to use the simple version of CASE.

Listing 4-27. Using Simple CASE

SELECT Title,
    CASE Title
    WHEN 'Mr.' THEN 'Male'
    WHEN 'Ms.' THEN 'Female'
    WHEN 'Mrs.' THEN 'Female'
    WHEN 'Miss' THEN 'Female'
    ELSE 'Unknown' END AS Gender
FROM Person.Person
WHERE BusinessEntityID IN (1,5,6,357,358,11621,423);

Figure 4-27 shows the results. Even though the CASE expression took up a lot of room in the query, it is producing only one column in the results. For each row returned, the expression evaluates the Title column to see whether it matches any of the possibilities listed and returns the appropriate value. If the value from Title doesn’t match or is NULL, then whatever is in the ELSE part of the expression is returned. If no ELSE exists, the expression returns NULL.

Searched CASE

Developers often use the searched CASE syntax when the expression is too complicated for the simple CASE syntax. For example, you might want to compare the value from a column to several values in an IN list or use greater-than or less-than operators. The CASE expression returns the first expression that returns true. This is the syntax for the searched CASE:

CASE WHEN <test expression1> THEN <value1>
[WHEN <test expression2> THEN <value2>]
[ELSE <value3>] END

Type in and execute the code in Listing 4-28 to learn how to use this more flexible method of CASE.

Listing 4-28. Using Searched CASE

SELECT Title,
    CASE WHEN Title IN ('Ms.','Mrs.','Miss') THEN 'Female'
    WHEN Title = 'Mr.' THEN 'Male'
    ELSE 'Unknown' END AS Gender
FROM Person.Person
WHERE BusinessEntityID IN (1,5,6,357,358,11621,423);

This query returns the same results (see Figure 4-28) as the results shown in Listing 4-27. The CASE expression evaluates each WHEN expression independently until finding the first one that returns true. It then returns the appropriate value. If none of the expressions returns true, the function returns the value from the ELSE part or NULL if no ELSE is available.

One very important note about using CASE is that the return values must be of compatible data types. For example, you can’t have one part of the expression returning an integer while another part returns a nonnumeric string. Precedence rules apply as with other operations. Type in and run this example to see what happens:

SELECT Title,
    CASE WHEN Title IN ('Ms.','Mrs.','Miss') THEN 1
    WHEN Title = 'Mr.' THEN 'Male'
    ELSE '1' END AS Gender
FROM Person.Person
WHERE BusinessEntityID IN (1,5,6,357,358,11621,423);

Listing a Column as the Return Value

It is also possible to list a column name instead of hard-coded values in the THEN part of the CASE expression. This means that you can display one column for some of the rows and another column for other rows. Type in and execute the code in Listing 4-29 to see how this works.

Listing 4-29. Returning a Column Name in CASE

SELECT VacationHours,SickLeaveHours,
    CASE WHEN VacationHours > SickLeaveHours THEN VacationHours
    ELSE SickLeaveHours END AS 'More Hours'
FROM HumanResources.Employee;

In this example (see Figure 4-29), if there are more VacationHours than SickLeaveHours, the query displays the VacationHours column from the HumanResources.Employee table in the More Hours column. Otherwise, the query returns the SickLeaveHours.

IIF

SQL Server 2012 introduced one easier method of writing a simple CASE expression. Starting with SQL Server 2012 you can now use an IIF function to return a result based on whether a Boolean expression is true or false. To create an expression with the IFF function, you need a Boolean expression and the values to return based on the results. Here is the basic syntax for the IIF function:

IIF ( boolean_expression, true_value, false_value )

Execute the code in Listing 4-30. The first IIF function is a simple execution, while the second IFF shows how you can introduce variables into the statement.

Listing 4-30. Using the IIF Function

--1 IIF function without variables
SELECT IIF (50 > 20, 'TRUE', 'FALSE') AS RESULT;

--2 IIF function with variables
DECLARE @a INT = 50
DECLARE @b INT = 25
SELECT IIF (@a > @b, 'TRUE', 'FALSE') AS RESULT;

Figure 4-30 shows the results. Keep in mind that all rules that apply to CASE expressions also apply to IIF functions.

COALESCE

You learned about COALESCE earlier in the chapter in the “Concatenating Strings and NULL” section. You can use COALESCE with other data types as well and with any number of arguments to return the first non-NULL value. You can use the COALESCE function in place of ISNULL. If a list of values must be evaluated instead of one value, you must use COALESCE instead of ISNULL. COALESCE may be used when concatenating strings or any time that a replacement for NULL must be found. Type in and execute the code in Listing 4-31 to learn more about COALESCE.

Listing 4-31. Using the COALESCE Function

SELECT ProductID,Size, Color,
    COALESCE(Size, Color,'No color or size') AS 'Description'
FROM Production.Product
where ProductID in (1,2,317,320,680,706);

Figure 4-31 displays the results. The COALESCE function first checks the Size value and then the Color value to find the first non-NULL value. If both values are NULL, then the string No color or size is returned.

Administrative Functions

T-SQL contains many administrative functions that are useful for developers. SQL Server also has many functions that help database administrators manage SQL Server; these functions are beyond the scope of this book. Listing 4-32 shows a few examples of functions that return information about the current connection, such as the database name and application.

Listing 4-32. A Few System Functions

SELECT DB_NAME() AS "Database Name",
    HOST_NAME() AS "Host Name",
    CURRENT_USER AS "Current User",
    SUSER_NAME() AS "Login",
    USER_NAME() AS "User Name",
    APP_NAME() AS "App Name";

Take a look at Figure 4-32 for my results; your results will probably be different. When I ran the query, I was connected to the AdventureWorks database on a computer named KATHIKELL as the dbo (database owner) user while using Management Studio.

In addition to the functions used to manipulate strings, dates, and numbers, you will find many system functions. Some of these work on different types of data, such as CASE, while others provide information about the current connection. Administrators can manage SQL Server using dozens of system functions not covered in this book. Complete Exercise 4-6 to practice using the logical and system functions covered in the previous sections.

Using Functions in the WHERE and ORDER BY Clauses

So far you have seen functions used in the SELECT list. You may also use functions in the WHERE and ORDER BY clauses. Take a look at Listing 4-33 for several examples.

Listing 4-33. Using Functions in WHERE and ORDER BY

--1
SELECT FirstName
FROM Person.Person
WHERE CHARINDEX('ke',FirstName) > 0;

--2
SELECT LastName,REVERSE(LastName)
FROM Person.Person
ORDER BY REVERSE(LastName);

--3
SELECT BirthDate
FROM HumanResources.Employee
ORDER BY YEAR(BirthDate);

Figure 4-33 shows the results of Listing 4-34. Even though it is very easy to use a function on a column in the WHERE clause, it is important to note that performance may suffer. If the database designer created an index on the searched column, the database engine must evaluate each row one at a time when a function is applied to a column. It still may search the column in the index, one value at a time, which could still be better than searching every row of the table.

Practice using functions in the WHERE and ORDER by clauses by completing Exercise 4-7.

The TOP Keyword

Use the TOP keyword to limit the number or percentage of rows returned from a query. Here is the syntax:

SELECT TOP(<number>) [PERCENT] [WITH TIES] <col1>,<col2>
FROM <table1> [ORDER BY <col1>]

The ORDER BY clause is optional, but most of the time, you will use it to determine which rows the query returns when using TOP. Sometimes you will see the same rows returned over and over even when not using an ORDER BY. This doesn’t mean that there is any guarantee about the rows returned. It rarely makes sense to request the TOP N rows without ordering. Usually one sorts by some criteria in order to get the TOP N rows in that sequence.

The WITH TIES option means that if there are rows that have identical values in the ORDER BY clause, the results will include all the rows even though you now end up with more rows than you expect. Type in and execute the code in Listing 4-34 to learn how to use TOP.

Listing 4-34. Limiting Results with TOP

--1
DECLARE @Rows INT = 2;
SELECT TOP(@Rows) PERCENT CustomerID, OrderDate, SalesOrderID
FROM Sales.SalesOrderHeader
ORDER BY SalesOrderID;

--2
SELECT TOP(2) CustomerID, OrderDate, SalesOrderID
FROM Sales.SalesOrderHeader
ORDER BY OrderDate;

--3
SELECT TOP(2) WITH TIES CustomerID, OrderDate, SalesOrderID
FROM Sales.SalesOrderHeader
ORDER BY OrderDate;

--4
SELECT TOP(2) CustomerID, OrderDate, SalesOrderID
FROM Sales.SalesOrderHeader
ORDER BY NEWID();

Figure 4-34 shows the results. Query 1 shows that you can use a variable along with TOP. This has been possible since version 2005 of SQL Server. It also demonstrates the use of the PERCENT option. Query 2 is a typical example returning just two rows. Query 3 demonstrates the WITH TIES option. Otherwise, it is identical to Query 2. It returns many more rows because there are many orders placed on the same date. Query 4 demonstrates a trick to get random rows. If you sort by the NEWID function, you will get different rows each time you run the query.

Thinking About Performance

In Chapter 3 you learned how to use execution plans to compare two or more queries and determine which query uses the least resources or, in other words, performs the best. In this chapter, you have seen how using functions can affect performance. Review the “Thinking About Performance” section in Chapter 3 if you need to take another look at how to use execution plans or to brush up on how SQL Server uses indexes.

Functions can be used in the WHERE clause to filter out unneeded rows. Although I am not saying that you should never include a function in the WHERE clause, you need to realize that including a function that operates on a column may cause a decrease in performance.

The Sales.SalesOrderHeader table does not contain an index on the OrderDate column. Run the following code to create an index on the column. Don’t worry about trying to understand the code at this point.

--Add an index
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id =
    OBJECT_ID(N'[Sales].[SalesOrderHeader]')
    AND name = N'DEMO_SalesOrderHeader_OrderDate')
DROP INDEX [DEMO_SalesOrderHeader_OrderDate]
    ON [Sales].[SalesOrderHeader] WITH ( ONLINE = OFF );
GO

CREATE NONCLUSTERED INDEX [DEMO_SalesOrderHeader_OrderDate]
    ON [Sales].[SalesOrderHeader]
([OrderDate] ASC);

Toggle on the Include Actual Execution Plan setting before typing and executing the code in Listing 4-35.

Listing 4-35. Compare the Performance When Using a Function in the WHERE Clause

--1
SELECT SalesOrderID, OrderDate
FROM Sales.SalesOrderHeader
WHERE OrderDate >= '2005-01-01 00:00:00'
    AND OrderDate <= '2006-01-01 00:00:00';

--2
SELECT SalesOrderID, OrderDate
FROM Sales.SalesOrderHeader
WHERE YEAR(OrderDate) = 2005;

Query 1 finds all the orders placed in 2005 without using a function. Query 2 uses the YEAR function to return the same results. When looking at performance differences of queries against the AdventureWorks database, keep in mind that it is a very small database and the queries may seem quick. In real life, tables can contain millions of rows where you will experience performance differences more realistically.

Take a look at the execution plans in Figure 4-35 to see that query 1 performs much better with a relative query cost of 7 percent. When executing query 2, the database engine performs a scan of the entire index to see whether the result of the function applied to each value meets the criteria. The database engine performs a seek of the index in query 1 because it only has to compare the actual values, not the results of the function, for each value.

Remove the index you created for this demonstration by running this code:

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id =
    OBJECT_ID(N'[Sales].[SalesOrderHeader]')
    AND name = N'DEMO_SalesOrderHeader_OrderDate')
DROP INDEX [DEMO_SalesOrderHeader_OrderDate]
    ON [Sales].[SalesOrderHeader] WITH ( ONLINE = OFF );

Run Listing 4-35 again now that the index is gone. Figure 4-36 shows that with no index on the OrderDate column, the performance is almost identical. Now the database engine must perform a scan of the table (in this case, the clustered index) to find the correct rows in both of the queries. Notice that the execution plan suggests an index to help the performance of query 1. It doesn’t suggest an index for query 2 because an index won’t help.

You can see from these examples that writing queries is more than just getting the correct results; performance is important, too.

Summary

Using expressions in T-SQL with the built-in functions and operators can be very convenient. There is a rich collection of functions for string and date manipulation as well as mathematical and system functions and more. It’s possible to use expressions and functions in the SELECT, WHERE, and ORDER BY clauses. You must use caution when using functions in the WHERE clause; it is possible to decrease performance.

Answers to the Exercises

This section provides solutions to the exercises found on writing queries with expressions.

Solutions to Exercise 4-1: Expressions Using Operators

Use the AdventureWorks database to complete this exercise.

1. Write a query that returns data from the Person.Address table in this format AddressLine1 (City PostalCode) from the Person.Address table.

   SELECT AddressLine1 + '  (' + City + ' ' + PostalCode + ') '
   FROM Person.Address;

2. Write a query using the Production.Product table displaying the product ID, color, and name columns. If the color column contains a NULL value, replace the color with No Color.

   SELECT ProductID, ISNULL(Color, 'No Color') AS Color, Name
   FROM Production.Product;

3. Modify the query written in question 2 so that the description of the product is returned formatted as Name: Color. Make sure that all rows display a value even if the Color value is missing.

   SELECT ProductID, Name + ISNULL(': ' + Color,'') AS Description
   FROM Production.Product;

4. Write a query using the Production.Product table displaying a description with the ProductID: Name format. Hint: You will need to use a function to write this query.

   Here are two possible answers:

   SELECT CAST(ProductID AS VARCHAR) + ': ' +  Name AS IDName
   FROM Production.Product;

   SELECT CONVERT(VARCHAR, ProductID) + ': ' + Name AS IDName
   FROM Production.Product;

5. Explain the difference between the ISNULL and COALESCE functions.

You can use ISNULL to replace a NULL value or column with another value or column. You can use COALESCE to return the first non-NULL value from a list of values or columns.

Solutions to Exercise 4-2: Using Mathematical Operators

Use the AdventureWorks database to complete this exercise.

1. Write a query using the Sales.SpecialOffer table. Display the difference between the MinQty and MaxQty columns along with the SpecialOfferID and Description columns.

   SELECT SpecialOfferID, Description,
        MaxQty - MinQty AS Diff
   FROM Sales.SpecialOffer;

2. Write a query using the Sales.SpecialOffer table. Multiply the MinQty column by the DiscountPct column. Include the SpecialOfferID and Description columns in the results.

   SELECT SpecialOfferID, Description, MinQty * DiscountPct AS Discount
   FROM Sales.SpecialOffer;

3. Write a query using the Sales.SpecialOffer table that multiplies the MaxQty column by the DiscountPct column. If the MaxQty value is NULL, replace it with the value 10. Include the SpecialOfferID and Description columns in the results.

   SELECT SpecialOfferID, Description,     ISNULL(MaxQty,10) * DiscountPct AS Discount
   FROM Sales.SpecialOffer;

4. Describe the difference between division and modulo.

When performing division, you divide two numbers, and the result, the quotient, is the answer. If you are using modulo, you divide two numbers, but the reminder is the answer. If the numbers are evenly divisible, the answer will be zero.

Solution to Exercise 4-3: Using Functions

Use the AdventureWorks database to complete this exercise. If you need help, refer to the discussion of the functions to help you figure out which ones to use.

1. Write a query that displays the first 10 characters of the AddressLine1 column in the Person.Address table.

   Here are two possible solutions:

   SELECT LEFT(AddressLine1,10) AS Address10
   FROM Person.Address;

   SELECT SUBSTRING(AddressLine1,1,10) AS Address10
   FROM Person.Address;

2. Write a query that displays characters 10 to 15 of the AddressLine1 column in the Person.Address table.

   SELECT SUBSTRING(AddressLine1,10,6) AS Address10to15
   FROM Person.Address;

3. Write a query displaying the first and last names from the Person.Person table all in uppercase.

   SELECT UPPER(FirstName) AS FirstName,
        UPPER(LastName) AS LastName
   FROM Person.Person;

4. The ProductNumber in the Production.Product table contains a hyphen (-). Write a query that uses the SUBSTRING function and the CHARINDEX function to display the characters in the product number following the hyphen. Note: There is also a second hyphen in many of the rows; ignore the second hyphen for this question. Hint: Try writing this statement in two steps, the first using the CHARINDEX function and the second adding the SUBSTRING function.

   --Step 1
   SELECT ProductNumber, CHARINDEX('-',ProductNumber)
   FROM Production.Product;

   --Step 2
   SELECT ProductNumber,
        SUBSTRING(ProductNumber,CHARINDEX('-',ProductNumber)+1,25) AS ProdNumber
   FROM Production.Product;

Solution to Exercise 4-4: Using Date and Time Functions

Use the AdventureWorks database to complete this exercise.

1. Write a query that calculates the number of days between the date an order was placed and the date that it was shipped using the Sales.SalesOrderHeader table. Include the SalesOrderID, OrderDate, and ShipDate columns.

   SELECT SalesOrderID, OrderDate, ShipDate,
         DATEDIFF(day,OrderDate,ShipDate) AS NumberOfDays FROM Sales.SalesOrderHeader;

2. Write a query that displays only the date, not the time, for the order date and ship date in the Sales.SalesOrderHeader table.

   SELECT CONVERT(VARCHAR(12),OrderDate,111) AS OrderDate,
        CONVERT(VARCHAR(12), ShipDate,111) AS ShipDate
   FROM Sales.SalesOrderHeader;

3. Write a query that adds six months to each order date in the Sales.SalesOrderHeader table. Include the SalesOrderID and OrderDate columns.

   SELECT SalesOrderID, OrderDate,
        DATEADD(m,6,OrderDate) AS Plus6Months
   FROM Sales.SalesOrderHeader;

4. Write a query that displays the year of each order date and the numeric month of each order date in separate columns in the results. Include the SalesOrderID and OrderDate columns.

   Here are two possible solutions:

   SELECT SalesOrderID, OrderDate, YEAR(OrderDate) AS OrderYear,
        MONTH(OrderDate) AS OrderMonth
   FROM Sales.SalesOrderHeader;

   SELECT SalesOrderID, OrderDate, DATEPART(yyyy,OrderDate) AS OrderYear,
        DATEPART(m,OrderDate) AS OrderMonth
   FROM Sales.SalesOrderHeader;

5. Change the query written in question 4 to display the month name instead.

   SELECT SalesOrderID, OrderDate,
         DATEPART(yyyy,OrderDate) AS OrderYear,
         DATENAME(m,OrderDate) AS OrderMonth
   FROM Sales.SalesOrderHeader;

Solution to Exercise 4-5: Using Mathematical Functions

Use the AdventureWorks database to complete this exercise.

1. Write a query using the Sales.SalesOrderHeader table that displays the SubTotal rounded to two decimal places. Include the SalesOrderID column in the results.

   SELECT SalesOrderID, ROUND(SubTotal,2) AS SubTotal
   FROM Sales.SalesOrderHeader;

2. Modify the query from question 1 so that the SubTotal is rounded to the nearest dollar but still displays two zeros to the right of the decimal place.

   SELECT SalesOrderID, ROUND(SubTotal,0) AS SubTotal
   FROM Sales.SalesOrderHeader;

3. Write a query that calculates the square root of the SalesOrderID value from the Sales.SalesOrderHeader table.

   SELECT SQRT(SalesOrderID) AS OrderSQRT
   FROM Sales.SalesOrderHeader;

4. Write a statement that generates a random number between 1 and 10 each time it is run.

   SELECT CAST(RAND() * 10 AS INT) + 1;

Solution to Exercise 4-6: Using Logical and System Functions

Use the AdventureWorks database to complete this exercise.

1. Write a query using the HumanResources.Employee table to display the BusinessEntityID column. Also include a CASE expression that displays “Even” when the BusinessEntityID value is an even number or “Odd” when it is odd. Hint: Use the modulo operator.

   SELECT BusinessEntityID,
        CASE BusinessEntityID % 2
        WHEN 0 THEN 'Even' ELSE 'Odd' END
   FROM HumanResources.Employee;

2. Write a query using the Sales.SalesOrderDetail table to display a value (“Under 10” or “10–19” or “20–29” or “30–39” or “40 and over”) based on the OrderQty value by using the CASE expression. Include the SalesOrderID and OrderQty columns in the results.

   SELECT SalesOrderID, OrderQty,
        CASE WHEN OrderQty BETWEEN 0 AND 9
               THEN 'Under 10'
           WHEN OrderQty BETWEEN 10 AND 19
               THEN '10-19'
           WHEN OrderQty BETWEEN 20 AND 29
               THEN '20-29'
           WHEN OrderQty BETWEEN 30 AND 39
               THEN '30-39'
           ELSE '40 and over' end AS range
   FROM Sales.SalesOrderDetail;

3. Using the Person.Person table, build the full names using the Title, FirstName, MiddleName, LastName, and Suffix columns. Check the table definition to see which columns allow NULL values and use the COALESCE function on the appropriate columns.

   SELECT COALESCE(Title + ' ','') + FirstName +
        COALESCE(' ' + MiddleName,'') + ' ' + LastName +
        COALESCE(', ' + Suffix,'')
   FROM Person.Person;

4. Look up the SERVERPROPERTY function in Books Online. Write a statement that displays the edition, instance name, and machine name using this function.

   SELECT SERVERPROPERTY('Edition'),
        SERVERPROPERTY('InstanceName'),
        SERVERPROPERTY('MachineName'),

Solution to Exercise 4-7: Using Functions in the WHERE and ORDER BY Clauses

Use the AdventureWorks database to complete this exercise.

1. Write a query using the Sales.SalesOrderHeader table to display the orders placed during 2005 by using a function. Include the SalesOrderID and OrderDate columns in the results.

   --one possible solution.
   SELECT SalesOrderID, OrderDate
   FROM Sales.SalesOrderHeader
   WHERE YEAR(OrderDate) = 2005;

2. Write a query using the Sales.SalesOrderHeader table listing the sales in order of the month the order was placed and then the year the order was placed. Include the SalesOrderID and OrderDate columns in the results.

   SELECT SalesOrderID, OrderDate
   FROM Sales.SalesOrderHeader
   ORDER BY MONTH(OrderDate), YEAR(OrderDate);

3. Write a query that displays the PersonType and the name columns from the Person.Person table. Sort the results so that rows with a PersonType of IN, SP, or SC sort by LastName. The other rows should sort by FirstName. Hint: Use the CASE expression.

   SELECT PersonType, FirstName, MiddleName, LastName
   FROM Person.Person
   ORDER BY CASE WHEN PersonType IN ('IN','SP','SC')
         THEN LastName ELSE FirstName END;