SQL SERVER – Identify the column(s) responsible for “String or binary data would be truncated.”

I am happy to write this blog post as I am really happy that finally, SQL Server Engineering team has decided to fix the error which we all have been struggling for many years. I will say Since the year 2000, I have been working with SQL Server and it has always bothered me that every time when I try to insert data into my table which is larger than the data type of the column, I got an annoying error String or binary data would be truncated. I recently demonstrated this to my Comprehensive Database Performance Health Check.

To be honest, I am very much confident that every single person who has worked with SQL Server will agree that the error related to String or Binary data is the most annoying because it does tell us that error has occurred but will not tell us which column has the problem. This becomes extremely difficult to debug when you face this error doing a mass import of multiple rows with a wide table with multiple columns. I have seen users doing a different workaround to avoid this error as this just breaks your workflow so bad that the entire automation process becomes a manual process.

Let us run a quick test and here is the script for the same.

CREATE TABLE Table1
(Col1 VARCHAR(10), Col2 VARCHAR(10))
GO
INSERT INTO Table1 (Col1, Col2)
VALUES('ElevenChars','Cols')
GO

Let us see how this error occurs in SQL Server 2017.

Msg 8152, Level 16, State 30, Line 4
String or binary data would be truncated.
The statement has been terminated.

Now let see the same error in SQL Server 2019.

Msg 2628, Level 16, State 1, Line 4
String or binary data would be truncated in table
‘tempdb.dbo.Table1’, column ‘Col1’. Truncated value: ‘ElevenChar’.
The statement has been terminated.

You will see that error which has been annoying so far now finally started to make sense. One more reason to upgrade to the latest version in SQL Server.

Reference: Pinal Dave (https://blog.sqlauthority.com)