SQL Server is constantly growing and changing, as are most database platforms. There are lots of platform changes, among them enhancements to the T-SQL language. Microsoft has added window functions, in-memory structures, the ability to execute code in other languages, and more. Some of these features are well built and some need more work. What's always interesting to me is what actually gets built and what doesn't.
There was an article recently on evaluating features in an RDBMS, and the article uses the JSON data type in Google's Big Query as an example. The evaluation is interesting, examining whether the feature actually helps the user, or if it is mostly marketing. In this case, the feature is outside of the "normal" conventions of the platform, but it is useful.
When I look at SQL Server, there are features that I think are well implemented and others that aren't. There are some that are very helpful, but some that don't perform well, at least not at scale. There are also some that people have requested, but Microsoft hasn't decided to build (please upvote the numbers table).
Many of us use a limited number of features inside the database platforms. We SELECT, INSERT, and UPDATE often, sometimes with the built-in functions, sometimes not. Many data professionals know what to use and avoid, but often developers may assume that any feature added will perform well. Most of them do very well with a few rows of data, but can be problematic at the scale of millions of rows. While we would want all features to work well at all scales, that isn't something that's going to happen.
I accept that some features are there to help customers in different ways. I can't imagine wanting to run Java code from a database call, but some organizations see value in this, so it's a capability in SQL Server. I'm sure some people can't fathom why I think a built-in numbers table is needed when there are easy ways to build one. I guess that's why my priorities, yours, and Microsoft's will differ, at least with regard to what things to change in the platform.
When you use a feature in any datastore, there may be tradeoffs with other techniques, and there may be problem domains where the feature works well or doesn't. It's important that you understand and learn the limits and advantages of any particular feature. It's also important you know how to adopt or abandon one when it doesn't meet your needs.