In my earlier post on SQL SERVER – Delete Duplicate Rows, I showed you a method of removing duplicate rows with the help of ROW_NUMBER() function and COMMON TABLE EXPRESSION. In this post, I am going to show you a tricky method of removing duplicate rows using traditional UNION operator.
SQL SERVER – Puzzle – Solution – Why Does UNION ALL Work but UNION Produces Error?
Last week, I asked a very simple puzzle about SQL SERVER – Puzzle – Why Does UNION ALL Work but UNION Produces Error?. The puzzle was very simple and interesting both together. I got quite a few valid answers to the puzzle so far. You may check original puzzle post to see all the valid answers. In this blog post we will quickly discuss the answer of our puzzle.
SQL SERVER – Puzzle – Why Does UNION ALL Work but UNION Produces Error?
You already know what UNION and UNION ALL operators do. They combine datasets while UNION keeps the distinct data, UNION ALL will retain all the data. You can read more about that over here: SQL SERVER – Difference Between Union vs. Union All – Optimal Performance Comparison. Let us see why does anyone all work, but union produces error in this puzzle.
SQL SERVER – Select Unique Data From a Column Exist in Two Different Table
If you have not understood what I am going to talk in this blog post by just reading the title, I think you are a normal human like me. Just the other day I received an email from a blog reader with subject line as “Select Unique Data From a Column Exist in Two Different Table”.
Interview Question of the Week #017 – Performance Comparison of Union vs Union All
Here is what I just learned from email. One of the very prestigious organization asks one of their candidate following questions – Question: If you have options to use Union or Union All – which one of the option will use keeping performance as a top most requirement for the query. Answer: Though…
Read MoreSQL SERVER – UNION With TEXT DataType Returns Error but and UNION ALL Works – Reasons
Earlier I demonstrated a very interesting behavior in my blog post – UNION and UNION ALL with TEXT DataType – Observation, where we see that UNION clause with TEXT datatype returns Error but with UNION ALL it does not return an error. I did not know that earlier before I posted that blog…
Read MoreSQL SERVER – UNION and UNION ALL with TEXT DataType – Observation
You can find the difference between UNION and UNION ALL in the following posts Union vs. Union All – Which is better for performance? There is one more difference between the two when a column of TEXT datatype is involved. When you use UNION for a TEXT column you will get…
Read More