SQL Server 2019 New Feature — Msg 8152, Level 16, State 14 String or binary data would be truncated. Improvement!

There are all kinds of new features in the new version of SQL Server 2019.  One of the ones I am excited about is the additional information you now receive be default when you try to insert data that is too long to fit into an existing field.  In prior versions you would get the message below, but you would have no idea what row or field was causing the issue:

Msg 8152, Level 16, State 14, Line 5 String or binary data would be truncated.

Here is the structure of a table I am trying to insert into.  The Username field has a maximum length of 10 characters.  SQL Server 2016 database.

 

Now I try and insert a value that is too many characters and you get the dreaded:  Msg 8152 error.  For this simple insert it is not bad, but if you are loading a million rows, you have no idea which row or field is causing the issue.

 

Now here is the same insert on  a SQL Server 2019 database.  You can see that the error message now tells you what field is causing the issue and how many characters it would have to truncate to fit into the field.

This only works on databases with a compatibility level of 150 and greater.  See below for a test.  First I check the level and it shows it is 150 which is SQL Server 2019 compatibility.  For a list of all of the compatibility levels, please follow this link:  https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver15

Now I am going to change it to SQL Server 2016 compatibility.

And now I get the SQL Server 2016 version of the error.

 

If I change it back to 150 I get the improved message.

 

And now we are back to the full error message.

 

This change actually came out in SQL Server SP2 CU6 and SQL Server 2017 CU12, but you have to set trace flag 460.  I try and not to run any of my production servers with trace flags unless absolutely necessary, so I never was able to enjoy the improved message.

You can read more about enabling the trace flag to enable this message on older versions by following knowledge base # 4468101

https://support.microsoft.com/en-us/help/4468101/optional-replacement-for-string-or-binary-data-would-be-truncated