start here https://www.mssqltips.com/sqlservertip/3074/use-caution-with... Many are fixed but should never have been there. Some are still extant.

There's plenty more low grade shit in what was once a goof product.

I haven't used the features you discuss but then I need basic functionality to work. I've run into stupid performance bugs with trivial CTEs, and I found a link showing a CTE error which I could duplicate (pgres got it right, mssql didn't). Can't find link, sorry.