Bert Wagner puts together a list of 12 techniques for tuning SQL queries:
6. DISTINCT with few unique values
Using the DISTINCT operator is not always the fastest way to return the unique values in a dataset. In particular, Paul White uses recursive CTEs to return distinct values on large datasets with relatively few unique values. This is a great example of solving a problem using a very creative solution.
Click through for the full list as well as a video demonstration.