Bob Ward enumerates some anti-patterns SQL Server can guilt you over:
If a query uses certain antipatterns, it will be detected during query optimization. For both SQL Server and Azure SQL (internally on by default), if these antipatterns are detected when optimizing the query, and the query_antipattern event has been added as part of a running extended event session, the output will be captured. The output will contain the relevant capture fields configured for the extended event session, allowing one to quickly identify which queries contain these antipatterns and are, therefore, prime candidates for tuning.
Read on for more information about this extended event, which is new to SQL Server 2022. I haven’t used this yet, so the two caveats I’m about to give are speculative in nature…though when has that ever stopped me? Caveat the first: just because something shows up as an anti-pattern doesn’t mean it needs to be fixed. There can be good reasons why you have chosen what is normally a less-efficient path. Caveat the second: just because something doesn’t show up as an anti-pattern doesn’t mean it’s fine. These are likely directional and my guess is that SQL Server will be fairly conservative in its estimation of what constitutes an anti-pattern so that you don’t get a lot of false positives.