Q&A

Q Those functions are interesting, but it seems like a lot of work to type in all that stuff just to generate a simple result set.

A That’s true. Functions are frequently used in the production of stored procedures and triggers. These advanced topics are covered later in the book, on Days 16 through 18.

Q Don’t functions take CPU time to operate? Why didn’t you mention the performance implications of that?

A Functions do take time to operate, but in the SQL Server world, the bottleneck on performance is almost always disk I/O. CPU time is cheap but data access is expensive. If a function can save disk I/O, the small CPU hit is worth it. Remember, however, that applying a function to a column in the WHERE clause prevents the server from using an index to resolve the query.

Workshop

The Workshop provides quiz questions to help you solidify your understanding of the material covered, as well as exercises to provide you with experience in using what you’ve learned. Try to understand the quiz and exercise answers before continuing on to the next day’s lesson. Answers are provided in the appendix.

Quiz

1.  What would be the result of this query?

   select “My name is “ + au_fname
   from   authors
   where  au_id = ‘172-32-1176’

2.  What would be the result of this query:

   select “My price is “ + price
   from   titles
   where  title like “%Computer%”

Exercises

1.  What is today’s date? What is the day of the week?

2.  Display a list of employees in a single column using the following format: Last Name, First Name MI. For middle initial, use the fourth character in the employee’s last name.

3.  How many hours until the millennium?

4.  Display the following information in a single column called “Employee Info”: an employee’s last name, the employee’s job_id number, and the year in which the employee was hired (in four-digit format). Order the data from the most recently hired employee to the oldest veteran. Be sure to include spaces between each field, and make the job IDs and years line up vertically.