Updated: Dec 31, 2021
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.
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%'
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.
Truncated Value: 'Hi, Hope y'
SQL Server 2019 makes it easier to troubleshoot the commonly faced error in data inserts or updates.