There are a tons of minor improvements of features in SQL 2017 that nobody talks about so I figured before we all get swept up with SQL 2019. It might be good to quickly post something about them for posterity.
sys.dm_os_host_info
A new DMV that gives OS level info as shown below, as you can imagine it’s very basic and doesn’t really provide any real information other than the OS name and version.
Only practical use is for audits and migrations which are fairly rare and could be managed via other ways. It doesn’t really give any information on if the Database Instance is running on a Docker container which reduces its usefulness in my opinion.
STRING_AGG
Essentially it a unpivot function for string values. From a pure database design perspective it’s the exact opposite of normalization. Because you taking data from columns and merging them into a single cell.
SELECT STRING_AGG(cast(lower(CourseLevel) AS VARCHAR(max)), ' | ') WITHIN
GROUP (
ORDER BY CourseName
)
FROM [survey].Course
The most common place I see this being used is in a column like skills which has a row for each skill the developer has. The combined skills list is then presented as a delimited value to some presentation layer. The ability to sort values within the list is a nice touch.
Creating Clustered Columnstore index on LOB columns like varchar(max) and nvarchar(max)
In SQL 2016 and below you would encounter the below message when creating a CLUSTERED COLUMNSSTORE INDEX on a LOB datatype column.
Msg 35343, Level 16, State 1, Line 1
The statement failed. Column ‘CourseDetails’ has a data type that cannot participate in a columnstore index. Omit column ‘CourseDetails’.
However in SQL 2017 this is no longer the case. While there are still the other usual limitations such as no other indexes are supported etc.
The Create statement will work in SQL 2017. SSMS obviously depends on which version you’re using. Why I have added this as a minor feature is because until the limitations of a CSCI are addressed there isnt that many real world use cases where using the Clustered Columnstore index is practical let alone one that support LOB datatypes.
CONCAT_WS
The sister function to String_agg where it concatenate data from multiple columns with a delimiter unlike string STRING_AGG which concats multiple rows with a delimiter.
TRIM
Hmm I wonder what Trim does
TRANSLATE
A useful function even though it’s a minor one. At its core it’s a simpler version of replace that works with multiple escape characters.
sys.dm_db_log_stats
SELECT * FROM sys.dm_db_log_stats(DB_ID())
While technically not a minor features it is still just a DMV so I figured I’ll explain it here. The DMV basically provides a health check of the T log and the most useful columns in the DMV include the number of VLFs, the log reuse wait desc (log_tuncation_holdup_reason) and the lsn and time since last backup. The best part of this DMV for me is that it helps better visualize the log growth and truncation / shrink mechanisms for my trainings.
Please Consider Subscribing
