For me, it’s CTE’s. I find it amazing to complete a calculation with clear intermediate steps, and goes a long way towards convincing people to use SQL rather than Excel to perform calculations on large tables of data.
What construct do you like using on a daily basis?
I find the MERGE statement to be extremely useful.
Window functions I really want to like, but I don’t have enough uses for them to learn them well enough to feel really comfortable with them.