SQL Server Performance Forum – Threads Archive
Union with select distinct
When using UNION and wanting to eliminate duplicate rows is there any advantage of usingUNION ALL with SELECT DISTINCT vs just UNION select distinct x,y,z from table
union all
select distinct x,y,z from table vs select x,y,z from table
union
select x,y,z from table
Two queries may return different rowset. First one may return duplicates. It will filter out dups from first and second select but not duplicates caused by having the same row returned by both selects. Second select will return no dups.
I think second is better as far as performance is concerned Madhivanan
UNION ALL runs faster, so that’s not correct Madhivanan. The two statements have completely different functionality, so performance shouldn’t be the overriding concern here. If you can use UNION ALL, then you should. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
Well, if you know that two selects return rowsets with no intersection, then two statements will return the same data. Union all is faster, but select distinct is slower then just select for the same reason union all is faster. In that case it is hard to tell. First query could be faster if parallelism is involved, but that’s just my wild guess. I agree that you can compare performance only if you are sure they return the same data, which is not the case here. Otherwise it doesn’t make sense.
I feel its difficult to say which query will perform faster.
first one has union all which is faster as compared to just union but first one involves fetching of distinct records too. so it seems it can perform not so good. so its better to c the profiler and the amount of rows the table has.
After that we can conclude which is better i feel.
]]>