I think CTEs and window functions in version 8 were big, combined with udf you could write some very complicated queries for data analysis without exponentially growing number of joins.