Sfrgttk

Like all programming languages, SQL has its oddities when compared to a generic programming language. Some of these have tripped us up.

It would be nice if there were an already-compiled list so that we could be forewarned of these pitfalls before stepping into them. What are some T-SQL oddities that we need to be aware of?

We don't mean an exhaustive study of all the specific structures, functions, and constructions unique to the SQL language. We mean a concise list of the places where SQL diverges consequentially from other standard programming languages or from the usual and expected conventions of programming. Things that a reasonably skilled programmer coming from another language could be expected to stumble over.

Below, we have started a list of the kind of examples we have in mind:

1. Trailing spaces are ignored by some functions and operators, such as LEN() and equality. The first query returns 5, 5, 5, 5, 5, 5. The second query returns EQUAL. These results can be quite surprising to a new SQL programmer.

SELECT   LEN('Hello') AS [0 trailing spaces],

         LEN('Hello ') AS [1 trailing spaces],

         LEN('Hello  ') AS [2 trailing spaces],

         LEN('Hello   ') AS [3 trailing spaces],

         LEN('Hello    ') AS [4 trailing spaces],

         LEN('Hello          ') AS [10 trailing spaces]

;





SELECT   CASE

              WHEN 'Hello' = 'Hello    ' THEN 'EQUAL'

              ELSE 'NOT EQUAL'

         END

;

2. Application of negative and positive signs has lower operator precedence than multiplication and division. This causes products preceded with negative signs to be evaluated first. Thus, the first expression in the query below yields 9.0, whereas the second expression yields -1.0.

SELECT   6.0/2.0*3.0,

         6.0/-2.0*3.0

;

3. Casting a blank string to integer, bigint, or float results in zero, whereas casting a blank string to decimal or numeric produces an error.

SELECT   CAST('' AS INT) AS [INT],

         CAST('' AS BIGINT) AS [BIGINT],

         CAST('' AS FLOAT) AS [FLOAT]

;



SELECT   CAST('' AS DECIMAL(15,6)) AS [DECIMAL],

         CAST('' AS NUMERIC(15,6)) AS [NUMERIC]

;

4. SQL has one of the most strongly defined NULL value of any programming language. It nearly constitutes a third logic value ("unknown") in a three-valued logic, rather than merely an indicator of "uninitialized variable". A three-valued logic can produce surprising results to those who are more accustomed to two-valued logic. The queries below will not necessarily return every row in the table. Rows in which COL1 is NULL will not be returned.

SELECT   *

FROM     EXAMPLE_TABLE

WHERE    COL1 = 5

         OR COL1 <> 5

;





SELECT   *

FROM     EXAMPLE_TABLE

WHERE    COL1 = 5

         OR NOT(COL1 = 5)

;

5. The strongly-defined NULL value and three-valued logic can cause bafflement in more subtle ways. The first query below will not return any rows, even when COL1 contains a value not in the excluded list (e.g., 6 or 7 or 8). The second query will return all rows, as expected, but the third query will never return any rows.

SELECT   *

FROM     EXAMPLE_TABLE

WHERE    COL1 NOT IN (1, 2, 3, 4, 5, NULL)

;







SELECT   *

FROM     EXAMPLE_TABLE

WHERE    1 = 1

;







SELECT   *

FROM     EXAMPLE_TABLE

WHERE    NULL = NULL

;

6. You'd better be sure you understand scale and precision and how they interact. Otherwise, the query below will make you recoil in horror.

SELECT   CAST(0.0000006 AS DECIMAL(38,22)) * CAST(1.000000 AS DECIMAL(38,22))

;