sql server 2000 instance specific setting to cause varchar to int error

Question

Hi

I am having an inconsistent issue being produced on one of my servers whereby I have the following

Select * 
from SomeVarcharTable v
join SomeIntTable i on i.MyInt=v.MyVarchar
Where v.Id = SomeID

The "MyVarChar" column is surprisingly of type varchar, and "MyInt" is of type int.

Whats curious is that when I run this on my development instance of Sql 2000, I receive no error.

When I run this on my Live instance of Sql 2000 I receive the following error:

Syntax error converting the varchar value '1.4' to a column of data type int.

Does anyone know of any server settings which might cause this. I have gone so far as to do a restore of my production database to my development system and the error doesn't occur.

I wondered whether it was something to do with the sql query engine, but this was working without an issue a few days ago.

Also, I am aware that this is not best practice, and that I could fix the above query by this:

Select * 
from SomeVarcharTable v
join SomeIntTable i on cast(i.MyInt as varchar)=v.MyVarchar
Where v.Id = SomeID

I am more interested at this stage in wondering why the issue has been caused, so I can educate others in my company

Thanks in advance

Mark.

Answer 1

I believe, when you are trying to compare a left hand INT value with the right side VARCHAR, the sql tries to implicitly convert the VARCHAR to an INT value and then compare. If it cannot implicitly convert varchar to INT, then it throws the above error which you are encountering.

integer data type has a higher precedence over a varchar data type. Since the integer data type has a higher precedence, the varchar data type is implicitly converted by SQL Server to an integer data type, and not the other way around.

Answer 2

This is a data issue. The live environment contains data in the varchar column that is not a valid integer, whereas presumably your dev box doesn't and when the join is being done, it is attempting to convert the VARCHAR to an INTEGER to match on the MyInt column. If MyVarchar contains any value that is not a valid integer, then you will get that error.

The issue is you are storing values in VARCHAR that you are treating in your query as being a valid INTEGER. The fact you are storing them in a VARCHAR instead of an INTEGER column, which of course would be the ideal, indicates in your system that they are not intended to always be valid integers and that's a problem.

So, you either need to:
1) change MyVarchar to be an integer column and only allow valid ints to be stored
2) CAST MyInt to a VARCHAR in the join as you are doing (not great for performance)
3) don't try to join when MyVarchar is not a valid integer (equally not great for performance) e.g.

Select * 
from SomeVarcharTable v
join SomeIntTable i on i.MyInt=v.MyVarchar
Where v.Id = SomeID
    AND v.MyVarchar NOT LIKE '%[^0-9]%' -- ignore rows where MyVarchar is not a valid int

4) create a new INTEGER column, alongside MyVarchar, and store the MyVarchar values in there that are valid integers, and use that to join instead. You'd be having some duplicate data, but performance-wise you'd at least be able to index and join on matching types without any workarounds.

Update: Check out this reference on data type precedence. Note INTEGER has a higher precedence than VARCHAR, hence is why VARCHARs are converted to INTEGERs.