Here’s some new T-SQL features that will be included in the upcoming SQL Server 2019 release. I’m using the Release Candidate for 2019.
I’ve posted this script to Github with some code to demonstrate these features.
Table Variable Deferred Compilation:
Currently, when using table variables, the execution plan will assume that there is only one row of data. In 2019, the plan won’t be created until the variable is filled for the first time, and build the plan off of that data. This may not be optimal if the data set is highly variable from run to run.
Mssqltips: table variable deferred compilation
Command to give a quick (if not completely accurate) count of distinct values in a table.
I’ve posted earlier on the Data Discovery And Classification feature in SSMS. This gives a way to tag columns that store sensitive data, and what kind of data (Financial, PII, etc.). 2019 adds T-SQL commands to manage these classifications.
SSMS has a set list of labels and information types, but with the SQL command, you can add any value you like. I didn’t find any system tables or views that listed these values. As far as I can tell, the Label and Info Type ID values would come from your own custom list of values.
Microsoft: add sensitivity classification
For string columns, we can use a new set of collations (Ending with _UTF8) to make a char or varchar column support UTF-8. So ASCII characters will still be stored as 1 byte, but any extended characters can be stored as well, even if they take 2 bytes. Previously, we would need to use a Unicode collation to store extended characters, but each character would take up 2 bytes.
This is definitely my favorite feature, one I’ve wanted for years. Currently, when you try to insert a row, one of the string values is longer that the maximum allowable length, you just get a generic message that a string will be truncated. Without knowing the column or the value provided, it was difficult to troubleshoot this issue.
Now, with 2019, we’ll get a message letting us know which column and what value were causing the issue.
Nice post. So is the “String Truncation” available in previous versions – just not turned on by default?
Yes, the feature is available in SQL Server 2016 and 2017 as well, once you install a cumulative update. I think you may have to enable a trace flag as well. But it works by default in 2019.