As a database administrator or developer, I’m quite sure that you’ve encountered the following error when working with your database queries in SQL Server.
Msg 8152, Level 16, State 30, Line 27
String or binary data would be truncated.
The statement has been terminated.
As is quite obvious by the text, this error occurs when you try to insert or update data that exceeds the maximum length of a column. While this error is common and quite straightforward to understand, it can be frustrating to troubleshoot – especially when you’re not sure which column is causing the issue (e.g. in the case of integrations or data imports).
In newer versions of SQL Server (2019 and above), the error message has become much more descriptive and I have found it very helpful to quickly identify and resolve the problem.
Let’s check it out with a code example. I am creating a test database and then changing the compatibility level of that database to 130 to simulate SQL 2016 equivalent behaviour. Then I will attempt to insert data (~42 characters) that exceeds the maximum length of the [SomeColumn1] column (25 characters).
SET ANSI_WARNINGS ON;
GO
USE [master];
GO
CREATE DATABASE [SqlTwinsDB];
GO
USE [master];
GO
ALTER DATABASE [SqlTwinsDB]
SET COMPATIBILITY_LEVEL = 130; --Simulating SQL 2016 on my SQL 2022 instance
GO
USE [SqlTwinsDB];
GO
--Safety Check
BEGIN
IF OBJECT_ID('[dbo].[stringLengthError]','U') IS NOT NULL
BEGIN
DROP TABLE [dbo].[stringLengthError];
END
END
--Declarations
BEGIN
CREATE TABLE [dbo].[stringLengthError]([Id] INT NULL,
[SomeColumn1] VARCHAR(25) NULL,
[SomeColumn2] VARCHAR(25) NULL
);
END
--Run the test
BEGIN
INSERT INTO [dbo].[stringLengthError] ([Id],
[SomeColumn1],
[SomeColumn2]
)
VALUES (1,
REPLICATE('nav',14), --14*3 = 42 characters, can't fit into [SomeColumn1]
REPLICATE('nav',5) --14*3 = 42 characters, will fit into [SomeColumn2]
);
END
/* Expectation: Get the following error:
Msg 8152, Level 16, State 30, Line 27
String or binary data would be truncated.
The statement has been terminated.
Problem is: We don't know which column is generating the error,
unless we know the data and do some calculations.
*/
--Cleanup
BEGIN
IF OBJECT_ID('[dbo].[stringLengthError]','U') IS NOT NULL
BEGIN
DROP TABLE [dbo].[stringLengthError];
END
END
GO
The error message we get is quite generic and doesn’t provide any information about which column is causing the issue. This would mean that we would have to dump the data into another temporary table/file and begin the tedious task of comparing the lengths and finding which field/column is being hit with the offending data.
Msg 8152, Level 16, State 30, Line 27
String or binary data would be truncated.
The statement has been terminated.
Let us repeat this test with Compatibility Level set to 150 or higher (I will go with the default (160) for my version. Since the database is already created, I will simply change the compatibility level and try again.
SET ANSI_WARNINGS ON;
GO
USE [master];
GO
ALTER DATABASE [SqlTwinsDB]
SET COMPATIBILITY_LEVEL = 160;
GO
USE [SqlTwinsDB];
GO
--Safety Check
BEGIN
IF OBJECT_ID('[dbo].[stringLengthError]','U') IS NOT NULL
BEGIN
DROP TABLE [dbo].[stringLengthError];
END
END
--Declarations
BEGIN
CREATE TABLE [dbo].[stringLengthError]([Id] INT NULL,
[SomeColumn1] VARCHAR(25) NULL,
[SomeColumn2] VARCHAR(25) NULL
);
END
--Run the test
BEGIN
INSERT INTO [dbo].[stringLengthError] ([Id],
[SomeColumn1],
[SomeColumn2]
)
VALUES (1,
REPLICATE('nav',14), --14*3 = 42 characters, can't fit into [SomeColumn1]
REPLICATE('nav',5) --14*3 = 42 characters, will fit into [SomeColumn2]
);
END
--Cleanup
BEGIN
IF OBJECT_ID('[dbo].[stringLengthError]','U') IS NOT NULL
BEGIN
DROP TABLE [dbo].[stringLengthError];
END
END
GO
This time, we still get the “String or binary data would be truncated” error, but with:
- With a different message code (2628 instead of 8152)
- A more verbose error which shows:
- The field that is causing the error
- The value that is being truncated
Msg 2628, Level 16, State 1, Line 31
String or binary data would be truncated in table 'SqlTwinsDB.dbo.stringLengthError', column 'SomeColumn1'. Truncated value: 'navnavnavnavnavnavnavnavn'.
The statement has been terminated.
Personally speaking, I find this to be a significant enhancement & a real time-saver when troubleshooting large data imports. Tip: If you’re working on an integration project and frequently encounter these types of errors, it may be worth considering upgrading your database engine to take advantage of this feature.
Sometimes, you may not even get this error and the truncation would happen silently. Do read my previous post https://nakulvachhrajani.com/2014/08/04/0341-sql-server-random-string-or-binary-data-would-be-truncated-errors-during-bulk-data-loads/ that talks about connection configuration parameters (more specifically, ANSI_WARNINGS) that may affect this behaviour.
Untill we meet next time,
Be courteous. Drive responsibly.
SQLTwins by Nakul Vachhrajani 