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.

tr01

 

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.

tr02

 

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.

tr03

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

tr04

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

tr05b

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

tr06

 

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

tr07

 

And now we are back to the full error message.

tr08

 

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

 

 

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s