Compilation of T-SQL oddities and pitfalls
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
;

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
;

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]
;

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 &lt;&gt; 5
;


SELECT   *
FROM     EXAMPLE_TABLE
WHERE    COL1 = 5
         OR NOT(COL1 = 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
;

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))
;

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'
;