CODEWITHSUNDEEP
sqlsql-server
user2822523
user2822523

Reputation: 25

How to avoid Sorting in Union ALL

MY question is simple, How do you avoid the automatic sorting which the UNION ALL query does?

This is my query

SELECT * INTO #TEMP1 FROM Final

    SELECT * INTO #TEMP2 FROM #TEMP1 WHERE MomentId = @MomentId

    SELECT * INTO #TEMP3 FROM #TEMP1 WHERE RowNum BETWEEN @StartRow AND @EndRow

    SELECT * INTO #TEMP4 FROM (SELECT *FROM #TEMP3 UNION ALL SELECT *FROM #TEMP2) as tmp

    SELECT DISTINCT * FROM #TEMP4

I'm using SQL Server 2008. I need the Union ALL to perform like a simple Concatenate, which it isn't! Appreciate your help in this.

Upvotes: 0

Views: 2188

Answers (3)

Kaf
Kaf

Reputation: 33829

UNION ALL adds all the records where as UNION adds only new/distinct records.

Since you are using UNION ALL and using DISTINCT soon after, I think you are looking for UNION

SELECT * INTO #TEMP4 FROM
    (
     SELECT * FROM #TEMP3 
     UNION  --JUST UNION 
     SELECT * FROM #TEMP2
    ) AnotherTemp

Or you can simplify it as

SELECT * INTO #TEMP4 FROM
        SELECT DISTINCT * 
        FROM Final
        WHERE MomentId = @MomentId OR RowNum BETWEEN @StartRow AND @EndRow

Upvotes: 1

Mike M.
Mike M.

Reputation: 12511

I think you're mistaken on which operation is actually causing the sort. Check the code below, UNION ALL will not cause a sort. You may be looking at the DISTINCT operation, which uses a sort (it sorts all items and the eliminates duplicates)

CREATE TABLE #Temp1
(
    i int
)

CREATE TABLE #temp2
(
    i int
)

INSERT INTO #Temp1
SELECT 3 UNION ALL
SELECT 1 UNION ALL
SELECT 8 UNION ALL
SELECT 2 

INSERT INTO #Temp2
SELECT 7 UNION ALL
SELECT 1 UNION ALL
SELECT 5 UNION ALL
SELECT 6 

SELECT * INTO #TEMP3 
FROM (SELECT * FROM #Temp1 UNION ALL SELECT * FROM #temp2) X

Upvotes: 1

Michaël Benjamin Saerens
Michaël Benjamin Saerens

Reputation: 875

I'm not familiar with SQL-Server, but you might get my idea

select *, 'A' tid, rownumber() tno from tableA
union all
select *, 'B', rownumber() from tableB
order by tid, tno;

This should get you all records of tableA in their specific order, followed by all records of tableB in their specific order.

Upvotes: 0

Related Questions