I have a query that queries a linked SQL server 2008 database and joins data from a table in that database to a table in SQL server 2000. It was working find until one of the columns in the SQL server 2008 database was changed to varchar(max). I received an error and I fixed it by using CAST(varchar(max) column AS varchar(50)). Now my queries performance is slow compared to how it was before the change. Can you give me some suggestion on how to fix the issue. Thank you for your time.
+3
A:
As the other commenters have pointed out, why was the column changed to varchar(max) if it can be reliably cast to varchar(50)?
Varchar(max) was meant as a replacement for the text datatype and should not just be used casually. A plain varchar can support up to 8000 characters and is recognized by SQL 2000 and 2005.
If you are doing this cast() in the select list, as a join condition, or in the where clause? Distributed queries already perform slow and by adding functions (cast, left, etc) in the where clause or join conditions is only going to make it worse.
Assuming that you cannot change the varchar(max) to a plain varchar, here's an idea
Does the 2005 box have a linked server connection to the SQL 2000 box? If so, can you run the query that way. The 2005 box will be able to compare the varchar(max) to the varchar(50) directly.
acterry
2010-08-07 00:02:06
Oscar, post some additional details and we'll try to help you out.
acterry
2010-08-07 13:58:27
The vendor is the owner of the database (SQL Sever 2008). They had to change it to "Max" to accomodate long diagnosis text that in some case would exceed the 8000 limit. I have select permission on the database to pull data for reports but I don't have access to create linked server. I will ask if they are willing to link our server SQL 2000 for me.Will A - I tried your suggestion but it returned an error "Argument data type text is invalid for argument 1 of left function." Thanks for the suggestion.
Oscar
2010-08-09 15:40:04