July 9, 2013 at 3:44 am
Good question. 🙂
July 25, 2013 at 9:28 am
Hugo Kornelis (7/3/2013)
Iggy-SQL (7/2/2013)
To me, the explanation isn't quite right. I believe the right explanation should be something along this line:Statement 1 : The SQL Database Engine converted the Val value to character string before comparing it with the LIKE condition, therefore it returns all row, because as we all know, % is the wildcard in LIKE condition.
Statement 2 : The SQL Database Engine tried to convert the character string '%' to float before it compares it with the value in Val column using the = operator, therefore the statement fails because '%' cannot be converted into float.
Better than the explanation in the question, but still incomplete. Why would SQL Server choose to do one conversion for the first statement and another conversion for the second?
Here's the answer.
For most operations that involve mixed data types, SQL Server uses the rules of data type precedence to determine the conversions needed. Float is higher than varchar, so SQL Server will try to convert the varchar to float. That's what happens in statement 2.
Statement 1 is different because the LIKE operator is used. When using operators that require a specific data type, operands are always directly converted to that data type. LIKE requires strings, so in this case the '%' is not converted (it's already string), and the float values are converted.
+1
The real value in this question is the explanation from this thread.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 18, 2013 at 7:35 pm
Good explanation, thanks.
Viewing 3 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply