Menu
Can I be sure that the result set of the following script will always be sorted like this O-R-D-E-R ?
SELECT 'O' UNION ALL SELECT 'R' UNION ALL SELECT 'D' UNION ALL SELECT 'E' UNION ALL SELECT 'R' Can it be proved to sometimes be in a different order?
527
UNION ALL keeps all of the records from each of the original data sets, UNION removes any duplicate records. UNION first performs a sorting operation and eliminates of the records that are duplicated across all columns before finally returning the combined data set.
Sets cannot have duplicate elements, so the union of the sets {1, 2, 3} and {2, 3, 4} is {1, 2, 3, 4}. Multiple occurrences of identical elements have no effect on the cardinality of a set or its contents.
Both UNION and UNION ALL operators combine rows from result sets into a single result set. The UNION operator removes eliminate duplicate rows, whereas the UNION ALL operator does not.
No, MERGE in SSIS is not the same as UNION in SQL Server. It will not remove duplicates. The only component that can remove duplicates is the SORT component, which has horrible performance.
There is no inherent order, you have to use ORDER BY. For your example you can easily do this by adding a SortOrder to each SELECT. This will then keep the records in the order you want:
SELECT 'O', 1 SortOrder UNION ALL SELECT 'R', 2 UNION ALL SELECT 'D', 3 UNION ALL SELECT 'E', 4 UNION ALL SELECT 'R', 5 ORDER BY SortOrder You cannot guarantee the order unless you specifically provide an order by with the query.
189
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
