A few things come to mind real quick.

The use of cursors

The use of table variables

Parallelism is bad

I used to be strongly opposed to the use of cursors. But that is just silly. There are many good uses - if used properly.

Table variables are a good tool. But they are not necessarily the absolute to replace temp tables as was a huge push for a while. Table variables can be a good tool or a really bad tool.

Parallelism can be a wonderful thing. In a well tuned query, parallelism can be that turbo button :-D. In a poorly tuned query parallelism can be, well - a pain. But the fact that parallelism exists doesn't imply that something is wrong or bad.

Those are a few things I have had to change my mind about over the years. And that is a good thing because that means I have hopefully learned something.