Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
It looks like a typo on the "not like" statement, shouldn't it be DocNo and not DonNo? –  user9164 Oct 16 '12 at 13:27
    
@user9164 Most likely yes. Fixed it up. –  Paul White Oct 17 '12 at 9:11
add comment

migrated from stackoverflow.com Oct 16 '12 at 8:06

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 5 down vote accepted

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.

share|improve this answer
    
I've seen SQL a number of times do the order by before the where clause, especially when doing a table scan for the where clause. By ordering first it can stop the scan as soon as it finds the first match –  Greg Oct 16 '12 at 0:45
    
Currently it does offer such a construct (at least for the invalid conversion case). Try_Convert(int, substring(DocNo, 2, Len(DocNo)-1) Desc but 2012 only so no use to the OP. –  Martin Smith Oct 16 '12 at 8:42
1  
@Greg There's one here: connect.microsoft.com/SQLServer/feedback/details/691535/… –  Paul White Oct 16 '12 at 12:14
    
I encourage everyone to vote on this Connect item and also see through some of the other items posted by Paul White on MS Connect: connect.microsoft.com/SQLServer/… –  usr Oct 16 '12 at 13:35
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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