asked
Why we are getting "Error converting data type varchar to float." Error in Process1 why we are not getting in process2
**I have a table like....** CREATE TABLE #Temp ( ID INT IDENTITY(1, 1) ,Ename VARCHAR(1000) ,Sal VARCHAR(100) ,DepAvgSal VARCHAR(100) ) **I have inserted data into that table** Insert into #Temp(Ename,Sal,DepAvgSal) select 'Nikhil',10,20 union select 'Siva',20,20 union select 'Sandeep','NA',20 **I have added new column Emsg to update error msg** ALTER TABLE #Temp ADD Emsg Varchar(2000) **We have to filter Only numeric salary data.** --Process1: Update #Temp set Emsg='Error' where isnumeric(Sal)<>1 select * from #Temp where cast(Sal as float)
Interesting. From looking at the predicates in the execution plan, it looks like it's a result of the way that the database engine is applying the operations. In your later query, the filter applies naturally against the column values (Emsg is null). So the database engine is processing the Emsg column first, then passing the result to the cast operations. ![alt text][1] The first query wraps this in an isnull function. This causes the predicate to use the cast operation first. ![alt text][2] But the real problem here is data type. You're using a text column to hold numeric data. You should always use appropriate types for your data. [1]: /storage/temp/1690-q2.jpg [2]: /storage/temp/1691-q1.jpg
Many Thanks iainrobertson. But how would i know in which order database engine apply these operations?Is there any fixed precedence is there like operator precedence?
There is no hard and fast rule. It all depends on what the database engine decides is the most efficient plan. In this case, for Process 2, it has decided that filtering on a direct column value is less expensive than filtering on a column value passed through a cast operation. So it uses this first. For Process 1, this is no longer the case, so it applies the cast operations first, presumably as it thinks that this is less expensive.
15 People are following this question.
