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 T1UNION ALL
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.
]]>