String or binary data would be truncated (VERBOSE_TRUNCATION_WARNINGS ON)

This error is usually encountered when inserting a record in a table where one of the columns is a VARCHAR or CHAR data type and the length of the value being inserted is longer than the length of the column. Before SQL 2019, it is really frustrated with this error message because you cannot find which column is the offending column. The error message is informative now in SQL Server 2019, which tells you which column is causing the issue.

We have a table before 2019 version of sql server

create table table_test (Id int identity (1,1),  Name varchar(10),  Address varchar(10),  Mobile varchar(10))

Now insert some records

Insert into table_test (Name,Address,Mobile) values ('Bagesh Kumar Singh','Noida','888880XXXX')

Getting bellow error 

It is very default to find out in which column it throws this error.

In sql server 2019, this error message has been improved. Here we will get the column name with truncate value.

Let’s see this example in sql server 2019

Insert this record

    

Oooooo  again we are getting same error!

Don’t warry

We need to set the VERBOSE_TRUNCATION_WARNINGS ON.

Now I am altering this database and setting VERBOSE_TRUNCATION_WARNINGS on.

ALTER DATABASE SCOPED CONFIGURATION   SET VERBOSE_TRUNCATION_WARNINGS = on;

   

Now I am inserting this record.

    

With the help of this error message, we can easily find out the truncated column and value. Now I am correcting it and inserting now.

  

Record inserted successfully.