Is that adviseble to use 'UNION ALL' in a sql query whisch i am using

[sbind77]

Hi

I have a situation to use 'UNION ALL' in one of my SQL Query
which i am using in my ASP page

This i am using to avoid multiple submission of SQL queries.

so my question is will the UNION ALL decrease the performa ce of the System.If the answer is yes then what is the advisable thing to overcome the UNION ALL
Thank You
sbind77

 

[balves]

UNION ALL is going to give you the duplicate rows in the query.

I believe it executes a little faster than UNION because it doesn't comb through the resultsets to eliminate the duplicates.

Of course, if you have a huge number of duplicates being returned, that would have a performance impact.

 

[tlbroadbent]

As balves mentioned UNION ALL returns duplicates. From your description you are selecting data from multiple sources so you either won't have duplicates or will want to return all the records selected regardless.

If you omit the ALL then SQL Server will sort the data and then eliminate duplicates. For small result sets you probably won't notice any difference but for large result sets, sorting is costly - usually more costly than returning duplicate records. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.