Value\n-----------\n1\n2\n3\n4\n5<\/pre><\/div>\n\n\n\nAs expected. What about the plan?<\/p>\n\n\n\nPretty straightforward. Does table scans of each table, concatenates the two outputs, and then sorts the data and removed dups with a Distinct Sort (Sort) operator.<\/p>\n\n\n\n
At this point in the process, I think I begin to understand Aaron\u2019s concerns. Because if you are using them both in a query, then you wouldn\u2019t likely be putting the DISTINCT<\/code> operator on both sets, rather it is going on the individual queries. Something like this:<\/p>\n\n\n\nSELECT DISTINCT Value\nFROM TableA\nUNION\nSELECT DISTINCT Value\nFROM TableB;<\/pre><\/div>\n\n\n\nEither one or both, and the optimizer doesn’t change that output of the query (might change the performance, of course). So, while you obviously get the same result, you get a different plan:<\/p>\n\n\n\nThis time, it fetches all the data again with Table Scan operators, deduplicates the data using the Distinct Sort (Sort) and then merges the data with a Merge Join. Interestingly, instead of doing the distinct in a sort now, it does know that it has two sorted inputs so it can do the deduplification easier with a Union (Merge Join) that tosses out duplicates.<\/p>\n\n\n\n
Finally, what will it do if you add the DISTINCT on the output from the UNION by using a CTE to perform the original query?<\/p>\n\n\n\n
With BaseRows AS (\nSELECT Value\nFROM TableA\nUNION\nSELECT Value\nFROM TableB)\nSELECT DISTINCT *\nFROM BaseRows;<\/pre><\/div>\n\n\n\nOf course, the same output, but we are back to the same plan as we started with. The optimizer realizes that the DISTINCT<\/code> is superflous in this case and tosses out second DISTINCT<\/code>. Sadly, there is no compiler warning that says: Warning: Silly superfluous DISTINCT used<\/code>.<\/p>\n\n\n\nSecond Test: UNION and Duplicated Data from One Side<\/h2>\n\n\n\n Now the more interesting case to me that sort of goes outside of the DISTINCT<\/code> and UNION<\/code> experiment. What if one input has the duplicated data? I will reload the data like this:<\/p>\n\n\n\nTRUNCATE TABLE TableA; TRUNCATE TABLE TableB;\nINSERT INTO TableA VALUES (1),(1),(1),(1),(2),(3),(4),(5);<\/pre><\/div>\n\n\n\nNow only one table has data, and there are definitely duplicates. What will happen when this is executed?<\/p>\n\n\n\n
SELECT DISTINCT Value\nFROM TableA\nUNION\nSELECT DISTINCT Value\nFROM TableB;<\/pre><\/div>\n\n\n\nPretty obviously the same output as before:<\/p>\n\n\n\n
Value\n-----------\n1\n2\n3\n4\n5<\/pre><\/div>\n\n\n\nThe duplicates are removed in the initial queries, so the first query returns 1,2,3,4,5, and unioning that to the empty set doesn\u2019t change that..<\/p>\n\n\n\n
But what about this?<\/p>\n\n\n\n
SELECT Value\nFROM TableA\nUNION\nSELECT Value\nFROM TableB;<\/pre><\/div>\n\n\n\nHonesty point: As I am writing this, I am still in \u201cI think I know what is going to happen, but not 100% sure\u201d mode. Why don\u2019t I clearly know this? Honestly, it is kind of rare to work with UNION, because most data will have no duplicates. And 99% of the time, duplicates in your sets mean there are issues. It is why a good data programmer is not a big fan of seeing DISTINCT in code without a 100 word essay in the comments. It most often masks issues with your code.<\/p>\n\n\n\n
Thinking back to our previous query plan for this query, it scans the data, it concatenates the rows, then does a Distinct Sort. Which strongly indicates that we will get the same set of data, because I think the UNION operator will not only remove duplicates from the set operation, but also from either set.<\/p>\n\n\n\n
And the only time it used a Union (Merge Join) was when it knew it had sorted and distinct data. Much like if these sets had primary keys\\unique indexes that it could trust.<\/p>\n\n\n\n
So I will now guess, that the output of the simple UNION<\/code> query will be the same, 5 rows, with values 1,2,3,4,5 for each row.<\/p>\n\n\n\nWhich it is. So I learned something, and I hope you did as well. UNION<\/code> returns a distinct set of data, no matter if the duplication is from rows in each input, or only just the one.<\/p>\n\n\n\nThird Test: Crank up the number of rows<\/h2>\n\n\n\n Since I am here and interested. What if I loaded a load of rows into the table. Maybe 100000 each? I will put back these rows. Will it change how rows are processed? Will performance change?<\/p>\n\n\n\n
TRUNCATE TABLE TableA; TRUNCATE TABLE TableB;\nINSERT INTO TableA VALUES(1),(2),(3),(4);\nINSERT INTO TableB VALUES (2),(3),(4),(5);<\/pre><\/div>\n\n\n\nBut now I will add 100,000 sequential rows into each.<\/p>\n\n\n\n
INSERT INTO TableA\nSELECT value\nFROM Generate_Series(6,100006);\nINSERT INTO TableB\nSELECT value\nFROM Generate_Series(6,100006);<\/pre><\/div>\n\n\n\nIt should be clear that there will be loads of duplicates in these sets. Now I am going to run the same queries again. I will also turn on STATISTICS IO<\/code> and TIME<\/code> to see if they perform differently.<\/p>\n\n\n\nSET STATISTICS IO,TIME ON;\nSELECT Value\nFROM TableA\nUNION\nSELECT Value\nFROM TableB;\n\nSELECT DISTINCT Value\nFROM TableA\nUNION\nSELECT DISTINCT Value\nFROM TableB;\nSET STATISTICS IO, TIME OFF;<\/pre><\/div>\n\n\n\nYou should get a lot of rows back, 200012 to be exact. They will likely be sorted different in the output, due to the differences in how they are being processed. I tried this on Express Edition and Developer Edition. In the first image is the Express output, and the Developer Edition used parallelism.<\/p>\n\n\n\n
Express Edition, No Parallelism:<\/p>\n\n\n\n<\/p>\n\n\n\n
Developer Edition, With Parallelism:<\/p>\n\n\n\n<\/p>\n\n\n\n
While the computed cost of the second query shows up as less, the execution of the query takes about the same time in each case, with the proper version taking the lesser time and simpler plan.<\/p>\n\n\n\n
In the larger, unordered sets, the plan has changed to somewhat less optimum plans. The main difference in the plan without DISTINCT<\/code> is that instead of a Distinct Sort (Sort) operator, it uses an Aggregate (Hash Match) operator. This is more efficient in larger sets than sorting data that will not need to be presented sorted.<\/p>\n\n\n\nOn the second query, you now end up with two Aggregate (Hash Match) operators to remove the duplicated data, and then, since that data is not sorted, it uses a Union Hash Match operator (which would not need to consider that there are duplicated rows like the Aggregate one would).<\/p>\n\n\n\n
In the amount of time taken, on my very small data set sizes (simple integer data doesn’t use a tremendous amount of space\/memory), the no DISTINCT<\/code> version was a decent bit faster. Times varied a bit, but the UNION<\/code> version was consistently faster, even when the plan claimed to be faster with the parallelim.<\/p>\n\n\n\n--NO DISTINCT\nSQL Server Execution Times:\nCPU time = 157 ms, elapsed time = 921 ms.\n\n--WITH DISTINCT\nSQL Server Execution Times:\nCPU time = 218 ms, elapsed time = 1356 ms.<\/pre><\/div>\n\n\n\nSo around 50% faster, even if that is just over .4 seconds.<\/p>\n\n\n\n
Interestingly, the number of logical reads in my example turned out to be exactly the same. The output from STATISTICS IO<\/code> were exactly the same:<\/p>\n\n\n\nTable 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.\nTable 'Workfile'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.\nTable 'TableB'. Scan count 1, logical reads 161, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.\nTable 'TableA'. Scan count 1, logical reads 161, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.<\/pre><\/div>\n\n\n\nA variation<\/h2>\n\n\n\n Lastly, let’s look at one additional way you could write this. Using a UNION ALL <\/code>operator, and then using DISTINCT<\/code> to eliminate duplicates (just for comparison, using UNION<\/code> is still the better way unless you specifically need all the rows for processing before outputting DISTINCT<\/code> rows:<\/p>\n\n\n\nSET STATISTICS IO,TIME ON;\nWITH BaseRows AS (\nSELECT Value\nFROM TableA\nUNION ALL\nSELECT Value\nFROM TableB)\nSELECT DISTINCT Value\nFROM BaseRows\nSET STATISTICS IO, TIME OFF;<\/pre><\/div>\n\n\n\nYou should get the same 100006 rows output as in the previous section. The plan will more than likely be the exact same as for the UNION <\/code>version of the query (since in this case you are specifically asking for the data to be concatenated and then deduped. Now this may not always be the case, with more complicated sets since the optimizer can refactor your query as needed. But suffice it to say that the output should be exactly the same.<\/p>\n\n\n\nThe point is, plus a tangent<\/h2>\n\n\n\n UNION<\/code> does DISTINCT<\/code> for you.<\/p>\n\n\n\nNo matter if the data is distinct or not in the sets you are applying the UNION<\/code> operator to, it will remove duplicates. However, be careful to understand that for UNION ALL<\/code>, you may actually want to do a DISTINCT<\/code> on the inputs if you want to not have duplicates in one side of the object.<\/p>\n\n\n\nFor example:<\/p>\n\n\n\n
TRUNCATE TABLE TableA; TRUNCATE TABLE TableB;\nINSERT INTO TableA VALUES(1),(1),(2)\nINSERT INTO TableB VALUES (2),(3),(3);<\/pre><\/div>\n\n\n\nIf you want to get all of the data in both tables, but you need to remove duplicates on one side of the query, you can do this:<\/p>\n\n\n\n
SELECT Value\nFROM TableA\nUNION ALL\nSELECT Value\nFROM TableB\nORDER BY Value; --added for clarity<\/pre><\/div>\n\n\n\nThe output of this looks kind of like what you would expect for two exact sets:<\/p>\n\n\n\n
Value\n-----------\n1\n1\n2\n2\n3\n3<\/pre><\/div>\n\n\n\nBut, it is fine to do this, if you really need it:<\/p>\n\n\n\n
SELECT DISTINCT Value\nFROM TableA\nUNION ALL\nSELECT DISTINCT Value\nFROM TableB\nORDER BY Value; --added for clarity<\/pre><\/div>\n\n\n\nNow the output shows DISTINCT<\/code> values from one set, combined with a UNION<\/code>:<\/p>\n\n\n\nValue\n-----------\n1\n2\n2\n3\n3<\/pre><\/div>\n\n\n\nAs always, requirements matter and if you need to do what seems like weird operations, they may be needed. The second point I want to make here is that when code smells funny, you need to doublecheck you are correct.<\/p>\n\n\n\n
Appendix:<\/h2>\n\n\n\n These are the tables that are used in the article. Nothing too complex!<\/p>\n\n\n\n
USE TempDB;\nGO\n--note: no keys because this data may simulate any two sets of data\nCREATE TABLE TableA\n(\nValue int\n);\nCREATE TABLE TableB\n(\nValue int\n);<\/pre><\/div>\n","protected":false},"excerpt":{"rendered":"When I was perusing my LinkedIn feed the other day, I came across this thread about using SELECT *. In one of the replies, Aaron Cutshall noted that: “Another real performance killer is SELECT DISTINCT especially when combined with UNION. I have a whole list of commonly used hidden performance killers!” To which started my……<\/p>\n","protected":false},"author":56085,"featured_media":106785,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[53,143531],"tags":[4151,4252],"coauthors":[19684],"class_list":["post-106777","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-featured","category-t-sql-programming-sql-server","tag-sql-server","tag-t-sql-programming"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/106777","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/56085"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=106777"}],"version-history":[{"count":2,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/106777\/revisions"}],"predecessor-version":[{"id":106904,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/106777\/revisions\/106904"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media\/106785"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=106777"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=106777"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=106777"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=106777"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/05\/1-Screenshot-2025-05-13-224314.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/05\/2-Screenshot-2025-05-13-224752.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/05\/word-image-106777-3.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/05\/word-image-106777-4.png\")
<\/figure>\n\n\n\n