Joining on varchar fields: issues with int conver

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Joining on varchar fields: issues with int conver

Author

Topic

Maxer
Yak Posting Veteran

51 Posts

Posted - 2008-02-21 : 13:47:53

I am working with a database that has customerIDs stored as varchar.

An example customer ID could be 0012345 or 7654321.

I'm having issues because it appears that the customerIDs are being converted to integers in queries such as

SELECT blah
FROM CustomerPhoneNumbers
WHERE CustomerID IN (SELECT distinct CustomerID from blah...)

What happens is that while the query returns results it seems to be taking 0012345 and converting it to 12345.

Is there any way to stop that from happening as it seems to considerably slow the query speed. (I tested by hard coding the IN statement with IN ('0012345', '...',....) and that executed many times faster).

Suggestions on how to deal with this?

Also to make things interesting: This is executing against a linked server and it is using a view.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-21 : 13:58:24

Why don't you use JOIN instead of IN.

SELECT blah
FROM Table1 t1 JOIN Table2 t2 on t1.CustomerID = t2.CustomerID

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Maxer
Yak Posting Veteran

51 Posts

Posted - 2008-02-21 : 14:19:59

Good question.

I initially tried that but had poor results over the linked server.

On my local server I have a list of customerIDs and on the linked server is the "master list" or central database.

So say I have my list of customerIDs on my local server and I need to hit the linked/remote server for their phone numbers.

I'd do something like:

SELECT phones.CustID, phones.CustPhone
FROM LinkedServer.blah.blah.CustPhones as phones
JOIN localCustTable AS local ON phones.custID = local.custID
WHERE local.callback = 1

(say I wanted to get phone numbers to make call backs or some random excuse)

However, what I found was that it appeared my local server was bringing the entire remote table down and comparing it locally instead of just sending my small list of local customerIDs to the remote server for processing.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources