T-SQL Fu: The Story Continues

This is a continuation of my last blog post, in which I mentioned that the online T-SQL Forums are like temples where young novices learn the mysteries of T-SQL under the patient guidance of a Master, reminiscent of the 1970’s TV series Kung Fu.

I wanted to provide a way for novices to get up to speed a little more quickly by revealing many of the mysteries of T-SQL all at once so they don't have to stumble at the beginning.

And so, continuing our story…

We left off with a fateful morning, when our young novice asked about his training in the mysterious art of T-SQL Fu…

---

Master, is my training almost over? When will I reach my full potential in T-SQL Fu?

Grasshopper, you still have much to learn. Quickly as you can, snatch this pebble from my hand. (The young novice tries and fails.) When you can take the pebble from my hand, it will be time for you to leave.

---

Time continues its irreversible march forward, and the training and questions continue…

---

Master, the ISNULL() function is cutting off my string. What’s going on?

declare @c char(1) 
set @c=null 
select isnull(@c,'12345')     
/* Returns '1' */ 

Grasshopper, the result of the ISNULL() function is always the exact same datatype as the first argument, which most people discover by accident because they don’t read the documentation. I would suggest using COALESCE() instead since it is an ANSI standard function, whereas ISNULL() is not. Also, COALESCE() will take multiple parameters:

declare @c char(1) 
set @c=null 
select coalesce(@c,null,reverse(@c),null-null,'12345')   
/* Returns '12345' */ 

---

Master, my data is getting cut off again. And why is it getting cut off at different points?

declare @s varchar 
set @s='ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890' 
select @s   
/* Returns 'A' */ 
 
select cast('ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890' as varchar) 
/* Returns 'ABCDEFGHIJKLMNOPQRSTUVWXYZ1234' */ 

Grasshopper, you did not stipulate the number of characters in your VARCHAR declarations. For whatever reason, the CONVERT() and CAST() functions assume 30 bytes, but DECLARE assumes only 1 byte. The following XML example also implies only 1 byte:

declare @x xml 
set @x='<data>ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890</data>' 
select @x.value('data[1]','varchar')   
/* Returns 'A' */ 

The lesson here is that you should always explicitly specify the number of characters in a VARCHAR or NVARCHAR declaration.

---

Master, this is clearly a bug. Everyone knows that all years divisible by 4 are leap years!

declare @LeapDay2000 datetime 
set @LeapDay2000='2000-02-29' 
select LeapDay1900=dateadd(year,-100,@LeapDay2000) 
      ,LeapDay2100=dateadd(year,+100,@LeapDay2000) 
/*  
LeapDay1900             LeapDay2100 
----------------------- ----------------------- 
1900-02-28 00:00:00.000 2100-02-28 00:00:00.000 
*/ 

Grasshopper, I am afraid you are mistaken. All years divisible by 4 are leap years EXCEPT for years divisible by 100; HOWEVER, years that are divisible by 400 (like 2000) ARE leap years. I am thankful that I will not be alive in 2100 to see the confusion that arises when people with “every 4 years” drilled into their heads insist that there should be a 29th of February.

So be assured that T-SQL knows what it is doing. However, you may be amused to know that Excel does NOT know what it’s doing. It thinks that 1900 was a leap year, as you can see below, where I subtract 1 from March 1, 1900:



Again, there was no such date as February 29, 1900. Interestingly, though, Excel does seem to be aware that 2100 will not be a leap year. As you young people say, “Go figure.”

---

Master, my system must be corrupt. It says that it cannot convert the date, but everyone knows that a date string in YYYY-MM-DD format is universally accepted.

select convert(datetime,'2009-09-15') 
/*  
Msg 242, Level 16, State 3, Line 2 
The conversion of a char data type to a datetime data type  
resulted in an out-of-range datetime value. 
*/ 

Grasshopper, I’m afraid you are incorrect. When you stepped away from your computer to go out for a Jamba Juice, I secretly typed SET LANGUAGE BRITISH in your SSMS session. The ANSI SQL format of YYYY-MM-DD is NOT language independent. It will not work in language settings that put day before month. The only sure-fire language-independent way of using date strings is to enter them as an 8-character string with no separators in YYYYMMDD format, or the more clumsy ODBC or ISO8601 formats:

select convert(datetime,'20090915') 
select convert(datetime,{d '2009-09-15'}) 
select convert(datetime,'2009-09-15T00:00:00') 

---

Many weeks later, our young novice is pulling the soggy earbuds of his iPod out of his morning cup of Green Tea as his Master approaches…

---

Master, this query keeps bombing. It doesn’t make sense, because shouldn’t T-SQL evaluate the left-most ISNUMERIC() function first and, if false, not even bother to do the CONVERT()? All programming languages do that!

select CustomerID,CompanyName,PostalCode 
from Northwind.dbo.Customers 
where isnumeric(PostalCode)=1 and convert(int,PostalCode)>99000 
/* 
Msg 245, Level 16, State 1, Line 1 
Conversion failed when converting the nvarchar value 'WA1 1DP' to data type int. 
*/ 

Grasshopper, T-SQL works differently from other languages. It does not always evaluate WHERE predicates from left to right. It is free to evaluate them in any order that it sees as the most cost-effective. In this case, it considers the CONVERT() function to be more cost-effective than the ISNUMERIC() function and so it does the CONVERT() predicate first. But note the following example:

select CustomerID,CompanyName,PostalCode 
from Northwind.dbo.Customers 
where Country='USA' and convert(int,PostalCode)>99000 
/* 
CustomerID CompanyName             PostalCode 
---------- ----------------------- ---------- 
LAZYK      Lazy K Kountry Store    99362 
OLDWO      Old World Delicatessen  99508 
*/ 

This query completed successfully. In this case it evaluated the Country=’USA’ predicate first, because it is more cost-effective than the CONVERT() predicate. Note that even though it scanned all the other customers, it ONLY evaluated the CONVERT() predicate AFTER it determined that the Country was ‘USA’.

If one of your predicates is dependent on the other, as was your case with ISNUMERIC() and CONVERT(), then the safest way to construct the query is with a CASE statement:

select CustomerID,CompanyName,PostalCode 
from Northwind.dbo.Customers 
where case 
        when isnumeric(PostalCode)=1 
        then convert(int,PostalCode) 
        else -1 
      end>99000 
/* 
CustomerID CompanyName             PostalCode 
---------- ----------------------- ---------- 
LAZYK      Lazy K Kountry Store    99362 
OLDWO      Old World Delicatessen  99508 
*/ 

---

Master, I don’t understand why this query is behaving this way. I used a LEFT JOIN because I wanted ALL employees to come out regardless of whether they have Orders with Freight over $800 or not. But it’s not producing ALL employees. Doesn’t a LEFT JOIN guarantee that?

select e.EmployeeID,LastName,OrderID,Freight 
from Northwind.dbo.Employees e 
left join Northwind.dbo.Orders o on e.EmployeeID=o.EmployeeID 
where o.Freight>800 
order by e.EmployeeID 
/* 
 EmployeeID LastName    OrderID  Freight 
----------- ---------- -------- -------- 
          2 Fuller        10691   810.05 
          3 Leverling     10540  1007.64 
          5 Buchanan      10372   890.78 
          7 King          11030   830.75 
*/ 

Grasshopper, the query IS doing a LEFT JOIN in the manner you expect, and it is creating an intermediate set of ALL employees with their orders (if any). However, it is your WHERE clause that is effectively filtering out your employees with no orders at all. Remember this important rule: If you’re doing a LEFT JOIN, do not refer to any of the right-hand-side’s fields in the WHERE clause. Instead, put the condition in the ON clause of the JOIN itself:

select e.EmployeeID,LastName,OrderID,Freight 
from Northwind.dbo.Employees e 
left join Northwind.dbo.Orders o on e.EmployeeID=o.EmployeeID 
                                    and o.Freight>800 
order by e.EmployeeID 
/* 
 EmployeeID LastName    OrderID  Freight 
----------- ---------- -------- -------- 
          1 Davolio        NULL     NULL 
          2 Fuller        10691   810.05 
          3 Leverling     10540  1007.64 
          4 Peacock        NULL     NULL 
          5 Buchanan      10372   890.78 
          6 Suyama         NULL     NULL 
          7 King          11030   830.75 
          8 Callahan       NULL     NULL 
          9 Dodsworth      NULL     NULL 
*/ 

The only exception to this rule is if you’re specifically checking for NULLs in the right-hand side, as in this example, which finds customers with no orders at all:

select c.CustomerID 
from Northwind.dbo.Customers c 
left join Northwind.dbo.Orders o on c.CustomerID=o.CustomerID 
where o.CustomerID is null 
/* 
CustomerID 
---------- 
FISSA 
PARIS 
*/ 

---

The days melt into weeks, and the passing of many moons beget years… until one day…

---

Master, there is a man who wanders about this temple and other monasteries, talking in a rude manner. He tells me that I have many design flaws in my data and that I’m performing operations that should be done in the application layer instead. He tells me I’m using “hillbilly” naming conventions instead of ISO 11179-5 metadata standards, and that IDENTITY keys are not valid because they are meaningless “Kabbalah” numbers with none of the relational properties of a proper key. He told me that I shouldn’t do attribute splitting and that Date and Time are an atomic component representing a temporal dimension as an interval. He also says the ROW_NUMBER() function is a disastrous sequential operation that… Master? MASTER?

Grasshopper, I am sorry. I didn’t mean to nod off. (He straightens his robes). I know of this man. What is your question?

Master, I don’t have one. I just wanted to talk about him. I think that he just goes out of his way to taunt me and others. If you filter out the inappropriate references to “hillbilly” and “Kabbalah”, perhaps small parts of some of the things he says have merit in a dry textbook-like theoretical sense, but he doesn’t seem to be living in the real world. I just ignore him and pretend he’s not there.

Grasshopper, I think you may have reached enlightenment.

Master, I am honored. Please look at this T-SQL Script:

select Pebble 
into Grasshoppers.Hand 
from Masters.Hand 
option (fast 1) 

Grasshopper, I am most proud. You are ready to leave. Remember always that a wise man walks with his head bowed, humble like the dust.

---

And so, our young hero, once a novice, now a T-SQL Fu Master, walks off into the sunset with head bowed. He has mastered the art of T-SQL Fu, but his training is not yet finished. There is much work ahead in studying SSRS Fu and SSIS Fu and SSAS Fu and BI Fu and MDX Fu and…

T-SQL Fu: The Path To Understanding

Do you remember the old 1970’s TV series Kung Fu? It’s the story of Kwai Chang Caine, a half-American half-Chinese Shaolin priest on the run in 1870’s America, sprinkled with flashbacks to his training with Master Po and Master Kan in the Shaolin temple back in China.

The various T-SQL forums available on the web kind of remind me of those flashbacks… they are like temples or monasteries where young novices can ask questions and learn the quirky idiosyncrasies of T-SQL under the patient guidance of a Master.

These forums are full of threads with simple common questions that get asked over and over again. It would be most helpful if novices could get up to speed quickly by having some of the mysteries of T-SQL opened up to them all at once so that they don’t have to stumble at the beginning… A way to begin to unlock the mysteries of the art of T-SQL Fu…

---

It is the dawn of a new day, and as the morning sunlight filters in through the high temple window…

---

Master, these queries are giving the wrong results! It’s quite obvious what I’m trying to accomplish in these queries. Is the server corrupt or something?

select Result = 3+4 * 5+6 
/* 
 Result 
------- 
     29 
*/ 
 
select EmployeeID,TitleOfCourtesy,LastName,City 
from Northwind.dbo.Employees 
where City='Seattle' or City='Tacoma' 
  and TitleOfCourtesy='Dr.' 
/* 
 EmployeeID TitleOfCourtesy  LastName  City 
----------- ---------------- --------- -------- 
          1 Ms.              Davolio   Seattle 
          2 Dr.              Fuller    Tacoma 
          8 Ms.              Callahan  Seattle 
*/ 
 
select Item = 'A' union select 'B' union select 'C' 
intersect 
select 'C'
/* 
Item 
---- 
A 
B 
C 
*/ 

Grasshopper, there is no corruption. All of these examples are giving the correct results. You formatted your queries according to your intentions, but T-SQL doesn’t care about formatting. It cares about an order of operations that it must follow. Multiplication and Division take precedence over Addition and Subtraction and therefore they are evaluated first. AND takes precedence over OR. And INTERSECT takes precedence over UNION and EXCEPT. Use parentheses to arrive at the outcome you’re intending:

select Result = (3+4) * (5+6) 
/* 
 Result 
------- 
     77 
*/
 

select EmployeeID,TitleOfCourtesy,LastName,City 
from Northwind.dbo.Employees 
where (City='Seattle' or City='Tacoma')
  and TitleOfCourtesy='Dr.' 
/* 
 EmployeeID TitleOfCourtesy  LastName  City 
----------- ---------------- --------- -------- 
          2 Dr.              Fuller    Tacoma 
*/
 

(select Item = 'A' union select 'B' union select 'C') 
intersect 
select 'C' 
/* 
Item 
---- 
C 
*/

---

Master, the following query couldn’t be simpler. Why is it not returning 0.5?

select Result = 1/2  
/* Returns 0 */

Grasshopper, in this query, T-SQL sees a 1 and a 2, which are both integers, so it performs integer arithmetic, producing an integer result with no remainder and no decimals. Therefore 1/2 evaluates to 0. Add a decimal point to force it to use decimal arithmetic instead:

select Result = 1./2  
/* Returns 0.500000 */

---

Master, you just got through telling me that T-SQL will divide two integers using integer arithmetic. It’s not doing it with this query:

select 2147483649/2  
/* Returns 1073741824.500000 */ 

Grasshopper, that first number is not an integer. Books Online states that integers range from -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647). Your number exceeds the range, so T-SQL interprets it as a decimal instead.

---

Master, I confirmed that Books Online states that the highest integer is 2^31-1. But when I try that number in a query, it gives me a strange answer:

select 2^31-1  
 /* Returns 28 */ 

Grasshopper, I’m afraid that Books Online uses an unfortunate syntax. In many programming languages, the carat (^) is used for exponentiation. But in T-SQL, the carat indicates a Bitwise Exclusive Or (XOR) operation. The bits in the result are set to 1 if either bit (but not both bits) in the operands are equal to 1. To illustrate:

select Result = 2^31 
/* 
Bit representation of 2:   00010 
Bit representation of 31:  11111 
-------------------------------- 
Exclusive Or (XOR) Result: 11101 = 29 
*/ 

In fact, there are other bitwise operators in T-SQL like an ampersand (&) for AND, a vertical line (|) for (non-exclusive) OR, and a tilde (~) for NOT. Consider, for example, the following query:

select ~5&6^7|8 
/* 
Bit representation of 5:   0101 
------------------------------- 
Result of ~5               1010 
Bit representation of 6:   0110 
------------------------------- 
Result of ~5&6             0010 
Bit representation of 7    0111 
------------------------------- 
Result of ~5&6^7           0101 
Bit representation of 8    1000 
------------------------------- 
Result of ~5&6^7|8         1101 = 13 
*/ 

But back to your original question… If you want to use exponentiation in T-SQL, use the POWER() function.

---

Master, something strange is going on here. I queried for rows with UnitPrice over $2.00, but I’m getting unexpected results.

select OrderID,ProductID,UnitPrice 
from Northwind..[Order Details]  
where UnitPrice!  >  2 
/* 
 OrderID  ProductID  UnitPrice 
-------- ---------- ---------- 
   10252         33       2.00 
   10269         33       2.00 
   10271         33       2.00 
   10273         33       2.00 
   10341         33       2.00 
   10382         33       2.00 
   10410         33       2.00 
   10414         33       2.00 
   10415         33       2.00 
   10454         33       2.00 
   10473         33       2.00 
*/ 

Grasshopper, you are not very observant this morning, despite the 2 Venti Mocha Frappuccinos that you guzzled. Note the exclamation mark, which you evidently typed in error under the influence of too much caffeine. This implies NOT when used with a comparison operator. So you are effectively asking for rows with UnitPrice NOT greater than $2.00. The comparison operators of !< and !> are not used very often… most people prefer using the >= and <= operators to accomplish the same thing.

---

The days of training continue… And there are still many questions… One bitterly-cold winter morning, our young novice is fruitlessly trying to scratch candle wax off his laptop keyboard as his Master approaches…

---

Master, I found a bug in T-SQL. The comparisons I’m doing in the examples below are clearly equal, but T-SQL thinks they are not.

declare @f float 
set @f=0.1 
if @f*@f = 0.1*0.1  
  print 'Ok'  
  else print 'Huh?' 
/* Prints 'Huh?' */ 
 
declare @a float, @b float 
select @a=14.58, @b=0.54 
if @a+@b = 14.58+0.54 
  print 'Ok' 
  else print 'Huh?' 
/* Prints 'Huh?' */ 

Grasshopper, the datatypes of FLOAT and REAL are known as “Approximate Numerics”. Not all values (like 0.1 for example) can be represented exactly in those formats. If you want to learn more about the inaccuracies of floating point math, you can do a Google search.

In your first example, you are multiplying two floating-point numbers (@f*@f) together and you are multiplying two decimals (0.1*0.1 = 0.01) together. T-SQL will take that decimal result of 0.01 and convert it to floating-point in order to do the comparison. But because of floating-point inaccuracies, a floating-point 0.1 multiplied by a floating-point 0.1 does not equal a floating-point 0.01. It is a similar situation with your second example.

Please observe:

select cast(0.1 as float)*cast(0.1 as float) - cast(0.01 as float) 
/* Returns 1.73472347597681E-18 */ 
 
select cast(14.58 as float)+cast(0.54 as float) - cast(14.58+0.54 as float) 
/* Returns 1.77635683940025E-15 */ 

Use the datatypes of NUMERIC or DECIMAL for mathematical calculations that produce exact results.

---

Master, I don’t understand why the following are giving me different results. Is floating point inaccuracy coming into play again somehow?

if 3 * 1./3 = 1 
  print 'Ok' 
  else print 'Huh?' 
/* Prints 'Ok' */ 
 
if 1./3 * 3 = 1 
  print 'Ok' 
  else print 'Huh?' 
/* Prints 'Huh?' */ 

Grasshopper, each of these two queries evaluate their expressions from left to right. The first query multiplied 3 times 1., coming up with 3.000000, and then it divided that by 3, coming up with 1.000000. The second query divided 1. by 3, coming up with 0.333333, and then multiplied that by 3, coming up with 0.999999. That intermediate result of 0.333333 is only an approximation of a repeating decimal where the digit of 3 goes on forever. I would suggest using the ROUND() function.

---

Master, why do the following 2 queries have different outcomes? I thought that T-SQL implicitly converted characters and strings to integers when there are expressions that mix the two.

select Result = 2 + '3' + '4' 
/* Returns 9 */ 
 
select Result = '3' + '4' + 2 
/* Returns 36 */ 

Grasshopper, again, the queries are being evaluated left to right. The second query sees two characters as the first two operands (‘3’ and ‘4’) and so it interprets the plus sign (+) as a concatenation and therefore comes up with an intermediate result of a string (‘34’). Finally, that string is converted to an integer before adding 2 to come up with the result of 36.

---

Master, this is really weird. Why does T-SQL think that 1 is equal to NULL?

if 1<>null 
  print 'Good... 1 is not equal to NULL' 
  else print 'HUH? T-SQL THINKS 1 IS EQUAL TO NULL!' 
/* Prints 'HUH? T-SQL THINKS 1 IS EQUAL TO NULL!' */ 

Grasshopper, any expression involving NULL, whether it be comparative or boolean or arithmetic, evaluates to NULL. And so, in your IF condition, the comparison of 1<>NULL evaluates to NULL, but an IF statements needs a TRUE/FALSE, so T-SQL evaluates the NULL to FALSE, and that is why the ELSE statement executed. When comparing to NULL, use IS NULL or IS NOT NULL. Don’t ever use =NULL or <>NULL.

---

Master, the length of the following string is clearly 10. What’s going on?

select len('12345'+space(5))   
/* Returns 5 */ 

Grasshopper, the LEN() function ignores trailing blanks. The DATALENGTH() function will give you the true number of bytes. But note that I said bytes rather than characters. You must realize that VARCHAR characters are 1 byte each while NVARCHAR characters are 2 bytes each. Observe…

declare @vchar varchar(20) 
       ,@nvchar nvarchar(20) 
set @vchar='12345'+space(5) 
set @nvchar='12345'+space(5) 
select datalength(@vchar),datalength(@nvchar) 
/* Returns 10 and 20 */ 

So you’ll have to remember to divide by 2 to get the number of characters in an NVARCHAR string. Either that, or you can use a more generalized method that works for both VARCHAR and NVARCHAR:

declare @vchar varchar(20) 
       ,@nvchar nvarchar(20) 
set @vchar='12345'+space(5) 
set @nvchar='12345'+space(5) 
select len(@vchar+'*')-1, len(@nvchar+'*')-1 
/* Returns 10 and 10 */ 

---

The first monsoon season passes, and then, one fateful day…

---

Master, is my training almost over? When will I reach my full potential in T-SQL Fu?

Grasshopper, you still have much to learn. Quickly as you can, snatch this pebble from my hand. (The young novice tries and fails.) When you can take the pebble from my hand, it will be time for you to leave.

---

To be continued… Click here to read the next installment.