Order by Clause in Union All | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Order by Clause in Union All

Hi, I am having one issue related to Order by Clause. i am working with two table i.e. T1, T2 and i am writing query to get data from T1 & T2 as below SELECT name,id,sdate,serilno,pagedata,doclocation,docdescription as Type from T1
SELECT name,Id,sdate,empno,pagedata,doclocation,docname as Type from T2
ORDER BY Type Whenever i am executing above query in development envrionment it is returning me data but whenever i am executing above query in testing envrionment it is giving me below error. /*****
Server: Msg 8626, Level 16, State 1, Line 1
Only text pointers are allowed in work tables, never text, ntext, or image columns. The query processor produced a query plan that required a text, ntext, or image column in a work table.
My T1 table structure is as below
name varchar(200),
Id int,
sDate datetime,
serilno int,
pagedata image,
doclocation varchar(200),
docdescription varchar(200) My T2 table structure is same as above only difference empno int,
docname varchar(200) As per my understanding i am getting above error because image data can’t be sort.
do any one knows how to fix above issue. I need ORDER BY TYPE because thats the way my front understand. Please help Thanks and Regards
Ravi K
Maybe clustered indexes on docname and docdescription? A clustered index will prevent a sort if ordering by that column, but I’m not sure if the UNION ALL will still force one when it merges the results of the two select statements.
You have execution plan in testing environment that requires worktable. Try to force execution plan from development environment or use temporary table to insert rows from both tables.