2

Like all programming languages, T-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.

Please contribute to the wiki answer.

2
  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 NULLs of any programming language. It nearly constitutes a third logic value (either "missing" or "non-applicable") 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 mark 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))
    ;
    
  7. More of unary arithmetic operator irregularities. When applying multiple sign operators in a row, the standard notation prescribes that you use brackets: -(-(-2)). In Transact-SQL, however, you can omit the brackets:

    SELECT  -1,  - -1,  0- - -1;
    
    -- results: -1, 1, -1
    

    Note that you have to put spaces between the - signs, otherwise a -- will be interpreted as a line comment symbol. However, you do not have to put spaces between multiple unary + signs:

    SELECT  +1,  ++1,  0+++++++++++++++++++++1;
    
    -- results: 1, 1, 1
    

    Finally, you can also apply a unary + to a string! This will compile just fine:

    SELECT
      +'This is a positive note',
      +++'Super+positive note',
      'I'++++++' love'+++++++++' plus(s)es'
    ;
    
  • It seems to me that if this question is about T-SQL, then perhaps it would be better to stick to T-SQL only peculiarities. What do you think? I mean, treatment of nulls isn't really specific to the Transact-SQL dialect. – Andriy M Apr 19 '19 at 7:46
  • @AndriyM — We had originally posed the question for the SQL language in general, without regard to specific dialect. However, the moderators insisted that we make it more specific, so we chose T-SQL. Really, we don't care whether the advice is about SQL in general or about a specific dialect. We just want to compile all the pitfalls that someone coming from a different programming background might encounter. – UnLogicGuys May 2 '19 at 18:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.