Writing highly efficient T-SQL code in scalable environment, and understanding what goes into type of code, has been one of the bigger challenges that I've enjoyed taking on in my humble developement career.
In many interviews that I've held to hire these type of people, you'd be surprised how few candidates really know their stuff in this regard.