Wake Up to T-SQL

Tired of the same old business problems? Try something new

Editor's Note: Send your experts-only T-SQL tips to Itzik Ben-Gan at [email protected]. If we use your tip in the magazine, you'll receive $100 and an exclusive T-SQL Black Belt shirt.

I spend many hours in class each month teaching about SQL Server and T-SQL. One of the most common activities that all of us (well, most of us) have practiced in the classroom as students is of course sleeping. I can tell you that from the teacher's viewpoint, it's not a pretty sight. Trying to teach a bunch of lethargic students can be tough and unpleasant. I've amused myself with the thought of sneaking up behind a nodding student and shouting loudly in his ear or taking a pair of scissors to the ponytail of a dreamy student. But for the sake of my job, I turned to more productive thoughts about how to realistically make them stay awake. To be frank, I think that "dozing-student syndrome" is mostly the teacher's fault. And I'll share a little secret with you: Assuming the teacher knows his or her stuff, teaching is easier than learning, contrary to popular belief.

While learning new material, a student has to cope with new concepts and new logical ideas. This learning process takes a lot of energy and effort, and if the teacher can't keep the students interested, the result is inevitable. A student in class has different needs than a client in a consultancy job. Clients have business problems they want solved, but students have a limit to the number of business scenarios they can handle. Let another customer/employee/order/product/invoice problem escape your lips, and you'll lose them.

The trick I've learned is to turn 90 degrees and take a totally different approach. I'm not talking about just passing the time until the break; I'm talking about teaching techniques that you can use to sharpen the students' T-SQL skills without covering business scenarios. Watch the class perk up when you announce, "Now, we're going to learn how to draw with T-SQL!" For those of you who aren't instructors, you can think of "drawing with T-SQL" as a fun way to learn T-SQL techniques you might find yourself implementing in totally unrelated business scenarios.

Angles of Interest

Let me show you an example that takes this 90-degree approach literally. Look at the script that Listing 1 shows and try to figure out what it does. This query relies on geometrical concepts—mainly the Pythagorean theorem—but I get to that later. The code in Listing 1 populates a temporary table called #Words with binary values. Each row stores a binary value that represents a different word out of a sentence. I converted the binary values from character strings to hide the content of the sentence from anyone who looks at the script. The query that follows the INSERT statements creates a smiling face with the sentence's words forming the mouth.

When I wrote the query, I sketched on paper something similar to what Figure 1 shows to help me plan my calculations. When you analyze a T-SQL problem such as producing a drawing, try to think in dynamic terms, meaning that the solution shouldn't be specific to the current number of rows in the input table. In this case, the size and position of the different elements making up the drawing should be dependent on the number of words making up the sentence. Looking at Figure 1, try to imagine the image as three separate, concatenated parts: the eyes, nose, and mouth. You can handle each part separately.

First, let's deal with the eyes. Two strings, each made up of four characters, materialize each eye. The rest of the strings in the rectangle containing the eyes are made up of four spaces each. You can use the values in the column key_col in the #Words table in your calculations as "row numbers" because the key values were generated by the identity property. The maximum key_col value actually represents the number of rows in the #Words table. Thinking dynamically, the strings that make up the eyes are in rows 2, 3, max_key - 1, and max_key - 2. You can use a CASE expression to determine whether the current row falls in the eyes area, and if so, produce a string of four nonspace characters. If the current row is outside that area, you produce spaces. Note that you need to calculate max_key and make it available to all rows in order to include it in the CASE expression. You can achieve this result by calculating the max_key value in a derived table, then cross-joining it with the #Words table, as Listing 2 shows.

You can produce the nose in a similar manner. The nose is made up of two strings of eight characters that are in rows max_key ÷ 2 and max_key ÷ 2 + 1. Thus, the following CASE expression produces the nose:

CASE  WHEN key_col IN(max_key/2,max_key/2+1) THEN REPLICATE('/', 8)  ELSE SPACE(8)END

Note that I'm relying on the fact that T-SQL returns an integer as a result of an integer division. Even if the #Words table had an odd number of rows, max_key ÷ 2 would still return a whole integer. Concatenate the above expression to the CASE expression that creates the eyes, as Listing 3 shows, and you have the first two parts of the face.

The mouth is the trickiest part. To produce it, we're going to use the help of Pythagoras and his theorem, which states that for any right triangle, the square of the hypotenuse is equal to the sum of the squares of the other two sides. Take another look at Figure 1, and think of the mouth as half a circle with a center located directly between the nostrils. The circle's radius is easy to calculate: num_rows ÷ 2. Note that the code performs an integer division here as well; however, you need an accurate value for the radius that would accommodate a division by 2 (one digit to the right of the decimal point). You can multiply an integer expression by 1. (read "one dot") to force the result to be of a decimal data type instead of an integer. I also subtracted 1 from the number of rows in the table to make the output look more aesthetic.

In T-SQL terms, you express the radius as (1. * COUNT(*) - 1) ÷ 2; its value is calculated in the derived table R to make it available to all the rows in the query. The tricky part here is to calculate, for each row, the distance between the rectangle encapsulating the nose and the beginning of the string that produces the mouth. After calculating that value, you add spaces equal to that distance and concatenate nonspace characters to it to form the mouth.

Looking at a sample row in the right-angled triangle in Figure 1, the missing part of the calculation is side a. The hypotenuse (c) is known—it's equal to the radius. Side b is fairly easy to calculate—ABS(radius - key_col + 1). The Pythagorean theorem says that in a right-angled triangle, the square of the hypotenuse (c) is equal to the sum of the squares of the other sides (a and b). Expressed as an equation, the theorem is a2 + b2 = c2. You have b and c, so you can express the value of a as (check)(c2 ­ b2). In T-SQL, the expression is SQRT(SQUARE(radius)-SQUARE(ABS(radius - key_col + 1))). Finally, wrap the above expression with the SPACE() function to generate the space that precedes the mouth, and concatenate it to the expression you have so far. When you run the concatenated code, you get a face with eyes, nose, and the space that precedes the mouth.

All that's left is to produce the string that makes the mouth itself. The string can contain anything, of course, but I used words to generate the strings. As I mentioned earlier, I stored the binary representation of the words in the #Words table to make the code a little more mysterious and interesting for my students (and readers). To decipher the "sophisticated" encryption algorithm I used, you can use CAST(word AS VARCHAR(15)). Concatenate this CAST expression to what you have so far, and you're almost finished. Thus far, we have a face with eyes, nose, and a mouth that looks more like a beard because each word is of a different size. To make the mouth look like a mouth, you can make all the strings composing the mouth the same size by concatenating a few characters such as a tilde (~) to each word. Use max_word_length - word_length to determine the number of characters that need to be concatenated. The finished query, which Listing 1 shows, produces the result that Figure 2 shows: a smiley face. :-)

Wake Up to the Possibilities

To generate the INSERT statements for this example by using binary values instead of plain text, you can use the script that Listing 4 shows (run it in text mode in Query Analyzer, not in grid mode). You can simply replace the message text in Listing 4 with words from your own message, then run the code in the listing. As I mentioned earlier, the query is dynamic, so the number of rows is unimportant. From my experience, people respond positively when they get a smiley face and a message as a result of some strange-looking T-SQL script. I hope this example keeps you awake for a while; I'll cover more of T-SQL's drawing capabilities in my next column.