SQL Server - String or binary data would be truncated: What is it, how to identify the root cause and how to fix it

Views: 952
Reading Time: 8 minutes

Hey guys!
In this article, I want to explain to you exactly what the message "String or binary data would be truncated" means, how we can identify which string is causing the error, how to hide this error message (if you want), what the The change in sys.messages has impacted this theme from SQL Server 2016 + and more!

So if you have trouble identifying and correcting occurrences of this error message, today will be the last time this will still be a problem for you.

What is “String or binary data would be truncated”

One of the most common SQL Server errors, the message "String or binary data would be truncated" occurs when a value is trying to be inserted or updated in a table and it is larger than the maximum field size.

Example 1 - Maximum field length with 10 characters, string with 10:

Example 2 - Maximum field length with 10 characters, string with 11:

3 Example - Hard to Identify Error

Look at the example 3. I am no longer entering a fixed value but from another table. This kind of situation may seem simple in a scenario such as the 1 and 2 examples, but when you are entering multiple records, especially taking data from multiple columns, it is difficult to identify which record and which column is causing this error message and This task may end up spending more time than you would like.

How to Bypass String Truncation

If you want to bypass string truncation at a given time or operation, you have the option to do so in SQL Server, as already shown in the article SQL Server - Why Not Use SET ANSI_WARNINGS OFF. I do not recommend using this technique at all, as it is a solution that is only masking the problem and not correcting it, but I would like to show that it exists and that it can be done.

When you use the SET ANSI_WARNINGS OFF command, you cause the SQL Server engine to no longer generate this error on execution, causing your 14 character text to be truncated and stored in the 10 character column. Excess characters will be silently discarded, ignoring and masking a problem writing your system data without anyone knowing.

Example:

Return with SET ANSI_WARNINGS ON (Default):

Return with SET ANSI_WARNINGS OFF:

String or binary data would be truncated in SQL Server 2019

SQL Server 2019 was released on 04 November 2019, during Microsoft Ignite, and with it, a huge range of new features were officially released.

One of these new features is the new posts available on sys.messages, which I had already shared with you in my article. SQL Server 2019 - News and Features List since late August of 2018:

With this change, it's now much easier to identify exactly where the truncation of values ​​is taking place:

According to traceflags documentation, the 460 traceflag replaces the data truncation 8152 ID message with the 2628 ID message. At the 150 database compatibility level onwards (2019), the 2628 ID message is the default and the 460 traceflag has no effect.

That is, this new behavior will only work automatically in SQL Server 2019 if the connection database is in 150 compatibility mode onwards.

Observation: This change to SQL Server 2019 sys.messages began in the CTP2.0 release and until the final release came out, it was not automatically activated, requiring the same changes as the 2017 and 2016 versions to work.

What has changed from SQL Server 2016 +

With the changes required for this implementation in SQL Server 2019, Microsoft eventually released this new message also in the 2016 (from SP2 Cumulative Update 6) and 2017 (From Cumulative Update 12) versions. And to be able to use this new message, we can use 2 in different ways:

1 Form: Using Initialization Parameter -T460

The first and most practical way is to enable 460 traceflag on the entire instance using the -T460 initialization parameter in the SQL Server service:

Once added, it will be necessary to restart the service and from then on, the new message will be working by default, without having to make any query changes:

2 Form: Using 460 Traceflag

One way that does not require restarting the SQL Server service and also does not require changing code is by using the DBCC TRACEON command, which allows you to enable this session level and instance level (global) traceflag:

Session level (only affects your session):

Instance level (global - affects all sessions):

Script used:

Remarks: To disable traceflag, use the DBCC TRACEOFF (460) or DBCC TRACEOFF (460, -1) command, depending on the parameter used for activation. If you have enabled instance-level traceflag, affecting all sessions, when you restart the SQL Server service, you must enable traceflag again.
There was a bug in SQL Server 2017 CU12 and CU13, which caused this “String or binary data would be truncated” error in insert operations involving table-type variables when the string length was longer than the field even in cases. where the code was not executed (Ex: Inside an IF with condition 1 = 0). This has been fixed in SQL Server 2017 CU14.

Identifying String Truncation Before SQL Server 2016

If you are using an earlier version of 2016 and have this difficulty identifying which values ​​are breaking the boundary of any column, I will share a simple script that can identify this kind of error even in older versions of SQL Server, creating a “clone” table of the original, with the column size set to maximum by entering the data in this new table and comparing the size used by the column size in the original table.

Example situation where script can be used:

I tried to execute my INSERT command and it gave an error in some value, some column, which I don't know what it is. Now is the time to think about the work you will have to identify this, especially if you have some 100 columns instead of just those example 3s. Having to insert the data into a clone table and keep querying and comparing the largest size. of each column… It seems laborious ..

To make this column identification work easier, I will share with you the script below, which makes this identification for you. Remember to change the source table and script name from INSERT / UPDATE to the cloned table with your original script.

Running this script will give you the exact return of which columns are too small for the data they are receiving and what is the optimal size to be able to store this data.

Observation: Be careful when using this script on very large tables. Because it needs to get the largest size of each column in the table, this operation can be a little slow and cumbersome on tables with millions of records. So have very careful!

I hope you enjoyed this article, a big hug and until the next post!
Hugs

References: