Sql-server – Strange SQL Statement Behavior

sql serversql-server-2008-r2

I have a simple SQL statement (executing on a SQL Server upgraded from 2000 to 2008 R2):

SELECT TOP (1)
    DocNox = CONVERT(integer, SUBSTRING(DocNo, 2, LEN(DocNo)-1)
FROM Tbl1
WHERE
    Fld1 = 19 
    AND Fld2 = 1
    AND Fld3 = 1 
    AND DocNo NOT LIKE '%-%'
ORDER BY
    CONVERT(integer, SUBSTRING(DocNo, 2, LEN(DocNo)-1) DESC;

Sample data (Serial is an IDENTITY column)

Serial   DocNo    Fld1   Fld2   Fld3
211      A1       19     1      1
212      A2       19     1      1
213      A003     19     1      1
214      X1-C1-1  20     1      1

Executing the query produces this error message:

Error converting nvarchar value '1-C1-1' to a column of data type int.

This is unexpected because the row containing that value ought to be filtered out by the WHERE clause before the conversion in the SELECT and/or ORDER BY clauses.

I have noticed that the following changes prevent the error occurring:

  • Changing the condition WHERE Fld1 = 19
  • Copying the data to another table with the same structure
  • Removing the TOP (1)
  • Removing the ORDER BY
  • Changing the ORDER BY to a different column (e.g. Serial)

But the error still occurs in the following circumstances:

  • Importing the whole table to another database
  • Running the same statement against a different database with the same data

I also made a change to the statistics that prevented the error but I cannot remember exactly what I did.

Can any one explain what is happening here?

Best Answer

This is normal. T-SQL does not generally specify an evaluation order. Intuitively you would evaluate the where clause first, but that does not have to be the case. SQL Server decided against that which is entirely valid.

Currently, SQL Server does not offer a construct to be 100% sure never to hit this problem. There is a 99% sure technique though: Wrap a case expression around anything that is dangerous:

case when DonNo like '%-%' then (/* convert */) end

This works almost always except in very few corner cases documented on Microsoft Connect. I don't have them at hand right now.

Related Question