SQL Server 2019: "String or binary data would be truncated"

This article explains in-depth what the error message "String or binary data would be truncated" means and how you can identify which strings are causing this error to occur. It also explains the differences between using sys.messages variants before and after SQL Server 2017+.

When do we get error "String or binary data would be truncated”

A statement "String or binary data would be truncated" is an error that occurs when you initiate a transaction to insert or update a value in a table, and it exceeds the column's maximum length.

Firstly, let's check SQL Server version

SET NOCOUNT ON
GO
SELECT SERVERPROPERTY('ProductVersion') AS SQLServerVersion
GO

My SQL Server instance is on SQL Server 2019 CU13.

Refer to KB4518398 - SQL Server 2019 build versions

The SQL instance contains a database "ABCD" in compatibility level 140 ( SQL Server 2017)

SELECT name , compatibility_level FROM sys.databases
WHERE name='ABCD'

Let's create a new table and insert a sample record in it.

USE ABCD
GO
CREATE TABLE MyDemoTable (id INT, val VARCHAR(10))
GO
INSERT INTO MyDemoTable VALUES (1,'Hi, Hope you are doing well...') 
GO

You get an error (Message 8152) as below.

If you have multiple inserts or updates in a transaction, it might be difficult to find which value is causing this error.

SQL Server 2019 improves the error message and specifies object name, column and value causing this error message.

Let's query the sys.messages and view the error messages related to string or binary data.

SELECT * FROM sys.messages
WHERE [text] LIKE 'string or binary data%'

Sys.messages in SQL 2019

To use the enhancement, the database must be in combability level 150. Let's change the combability level and run the insert statement again.

USE [master]
GO
ALTER DATABASE [ABCD] SET COMPATIBILITY_LEVEL = 150
GO

It raises error id 2628 in SQL Server 2019 with the details as shown below.

• Table: ABCD.dbo.MyDemoTable

• Column: Val

• Truncated Value: 'Hi, Hope y'

SQL Server 2019 Error Message

SQL Server 2019 makes it easier to troubleshoot the commonly faced error in data inserts or updates.