Replacement for UNION? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Replacement for UNION?

Author

Topic

Bjoern
Starting Member

1 Post

Posted - 2006-10-09 : 00:57:08

Hi,

I have a table named id_v which looks like this:

id1 id2 id3 value

and a query with up to 5000 dynamically generated SELECT-Statements combined by UNION:

SELECT 'B2' AS cell, value FROM id_v WHERE id1=... AND id2=... AND id3=...
UNION
SELECT 'F20' AS cell, value FROM id_v WHERE id1=... AND id2=... AND id3=...
UNION
SELECT 'A1' AS cell, value FROM id_v WHERE id1=... AND id2=... AND id3=...
UNION
...

As a result I get something like this:

B2 303.5
F20 20.5
A1 0
...

Is this already the fastest way to get the desired results?
Any tips are appreciated.

Thanks in advance,
Bjoern

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-09 : 01:15:47

It's difficult to say whether this is appropriate or not, unless we know the reason you are doing it this way.

One improvement you can do is - if you know that there are no duplicates while doing union - you can use UNION ALL instead of UNION.

Harsh Athalye
India.
"Nothing is Impossible"

Kristen
Test

22859 Posts

Posted - 2006-10-09 : 01:36:37

I don't know if it will be faster but:


SELECT CASE WHEN id1=... AND id2=... AND id3=... THEN 'B2' 
            WHEN id1=... AND id2=... AND id3=... THEN 'F20' 
            END AS cell,
        value
FROM id_v 
WHERE    (id1=... AND id2=... AND id3=...)
      OR (id1=... AND id2=... AND id3=...)

should be able to do the same job, and maybe!! the optimiser will make mincemeat of the WHERE clause

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources