June 24, 2009 at 8:28 pm
I have a query where I have to select a image column with group by. When I am trying to do that its giving the below error.
Msg 306, Level 16, State 2, Line 1
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
As a workaround I had put the query without image column in select and group by clauses in a dervied table and used a join condition. The problem now is when i execute the query in Standard edition instead of returning 1000 rows its returning some millions of rows. Whereas if I execute the same query in enterprise edition its working fine. Both are RTM versions only.
Initial query
select tbl1.col1,tbl2.col1,tbl1.imgcol,tbl1.colx
from table1 tbl1,table2 tbl2
where tbl1.colx=tbl2.coly
group by tbl1.col1,tbl2.col1,tbl1.imgcol,tbl1.colx
order by tbl1.col1
Workaround query
select d.*,o.imgcol
from
(
select tbl1.col1,tbl2.col1,tbl1.colx
from table1 tbl1,table2 tbl2
where tbl1.colx=tbl2.coly
group by tbl1.col1,tbl2.col1,tbl1.colx
) as d,tbl1 o
where d.colx=o.colx
Please help me
[font="Verdana"]Thanks
Chandra Mohan[/font]
June 24, 2009 at 9:32 pm
The problem is that these queries may not be equivalent. What is the relationship between table1 and table2, is it a one to many relationship?
Some sample data for the relavent columns in each table (except the image columns) would help in visualize the relationship. Be sure the data is represtative of the data in your tables. Don't provide just one or two records for each table, but enough to give an idea of the data. In place of the image column, you might provide an integer value that represents each image. Duplicate images should have the same value so that we can see if there are duplicate images in the records.
June 25, 2009 at 7:06 am
Can you post actual execution plans for each of the queries?
Since you're testing it on two different servers, are you 100% positive that the same data exists in each? Also, be sure that both databases have their indexes fragmented and the statistics updated in the same manner. Any of this can change the execution plans generated, although it usually won't affect the data returned.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply