There are several interesting or helpful and generally under-used SQL Server features that Microsoft added over time. In the future, I would like to dive somewhat deep into some of them. For now, here are some of the latest ones that I have found to be either currently helpful or potentially very useful:

T‐SQL Enhancements

Microsoft has been updating T-SQL over the years. Here are some of the best ones:

• TRUNCATE TABLE WITH PARTITION– Allows data truncation based on partitions. That means you would add your data using a partition function and you would also be able TRUNCATE the data with the same partition function – a minimal logged operation and is generally much speedier than a regular DELETE.
  There are some new and handy string functions:
• CONCAT_WS – Concatenates strings, with a specified separator.
• STRING_AGG – Joins strings across multiple rows. This one is handy for creating single data fields from multiple rows of data, like an email list out of a column of email data.
• TRANSLATE – Similar to REPLACE, but only does a one for one substitution.
• TRIM – Finally! No more RTRIM(LTRIM(X)) crap. This one has been so obviously lacking in T-SQL for literally decades that it’s almost embarrassing it took so long to add it.
• SELECT INTO…ON – Allows you to pick the filegroup for your newly created table. Prior to this, all SELECT…INTO tables have been on the PRIMARY filegroup. This can be used for arranging your data on disk and can be helpful in optimizing data reads and writes.

Security Features

SQL has added a lot of new security/encryption features recently:

Dynamic data masking – Limits exposure to sensitive data with masking. You can hide things like credit card or SSN data.
Row level security – Self-explanatory, but new in SQL Server 2016. Other RDBMS systems have had this for a while, so it’s basically keeping up with the times to have it.
Always Encrypted– Separates those who own the data frooud.m those who manage the data. Encryption keys are managed outside of the database. The keys are never revealed to SQL Server. It is somewhat complicated to implement, but it is very useful for data security. 

Stretch DB

Store portions of the database in the cloud (Azure). You can keep commonly used data on a local SQL Server, and less commonly used data is seamlessly stored and accessed in an Azure SQL database in the cloud.

Temporal Tables

Point in time data access. It is relatively straightforward to implement. Some simple schema considerations and a property set on the table. At that point as data is added or changed, copies are maintained based on the time. And from this you are able to query and get specific result based on a point in time.

Some of the most interesting (to me) are the Always Encrypted and the Temporal Tables. As new features are added and as the current features progress there is always something new to look forward to learning about SQL Server.