String or binary data would be truncated

This is my first post about some new features introduced in MS SQL 2019. For now, the final version of MS SQL 2019 is not yet released but I am using a stable CTP 3.2 version.

There is a large chance that you already see the error message:

“String or binary data would be truncated”

Ok, let’s see one of the possible examples how you can get such an error.

Example:

USE [master];
GO
ALTER DATABASE [WideWorldImporters] SET COMPATIBILITY_LEVEL = 140;
GO
USE [WideWorldImporters];
GO
CREATE TABLE Data(
	FirstName NVARCHAR(64) 
	, LastName NVARCHAR(6)
);

INSERT INTO Data(FirstName, LastName) VALUES ('Bob' , 'Marley');
INSERT INTO Data(FirstName, LastName) VALUES ('John' , 'Doe');
INSERT INTO Data(FirstName, LastName) VALUES ('Jack' , 'Daniels');

You see where the error is. Let’s suppose that I was using or created a table containing a text column (char, (n)varchar, etc.) that was to short for the data that I want to insert in the same column. In this example I am using an old compatibility level for my database (140 = MS SQL 2017). Since the last entry “Jack Daniels” has more than 6 characters in the last name the error message is raised.

Sometimes this error message can be very frustrating and almost impossible to detect what insert statement (or record) is the cause of the issue. Imagine that we are trying to insert some values from another table, or a complex select statement.

But the Microsoft team did a great job in the 2019 version of the product. Let’s change the compatibility version of the database to 150 (150 = MS SQL 2019) and see what the result of the same script will be.

USE [master];
GO
ALTER DATABASE [WideWorldImporters] SET COMPATIBILITY_LEVEL = 150;
GO
USE [WideWorldImporters];
GO
CREATE TABLE Data(
	FirstName NVARCHAR(64) 
	, LastName NVARCHAR(6)
);

INSERT INTO Data(FirstName, LastName) VALUES ('Bob' , 'Marley');
INSERT INTO Data(FirstName, LastName) VALUES ('John' , 'Doe');
INSERT INTO Data(FirstName, LastName) VALUES ('Jack' , 'Daniels');

Of course, the error message is still here but now it is more intuitive, and it could lead us to the problematic record.

Now we know some basic parameters of the error:

  • Table in which the insert error occurred - in table 'WideWorldImporters.dbo.Data',
  • The destination column name - column 'LastName' and finally
  • The value (part of the value) that was the cause of the error - Truncated value: 'Daniel'.

This is a great enhancement that could save us a lot of time and make our “debugging” easier.

*You can download the complete SQL Script with all examples from the post here: SQL Script.sql (982.00 bytes)