DISTINCT Papers up Problems

Aaron Bertrand wants to solve the actual problem:

I’ve quietly resolved performance issues by re-writing slow queries to avoid DISTINCT. Often, the DISTINCT is there only to serve as a “join-fixer,” and I can explain what that means using an example.

I’ve seen this a lot as well, and it usually comes from people not understanding the data model or not understanding how to use subqueries (or common table expressions, the APPLY operator, etc.) to define subsets of data.