Problem
Anybody working with SQL Server for a while has seen this annoying and frustrating error message when the value for a column exceeds the length for an INSERT statement:
Msg 8152, Level 16, State 30, Line 3 String or binary data would be truncated. The statement has been terminated.
SQL Server 2019 provides us a much anticipated and improved data truncate error message. Verbose Truncation Warnings, which give us all the details of the data truncation issue so that we can quickly solve it without going through a painful and lengthy debugging process.
Solution
To fully understand SQL Server 2019 Verbose Truncation Warnings, we will briefly review the dreadful old truncate warning, then introduce the long waited and detailed new verbose warning message and the requirements. Finally, we will point out some pitfalls to avoid in order to make the new verbose warning really work.
Old SQL Server String or Binary Data Would be Truncated Error Message
Before, SQL Server 2019, this was the vague and dreadful error message – Msg 8152 "String or binary data would be truncated", without additional information to help you figure out the issue.
Prior to SQL Server 2019, when trying to store string or binary data that exceeds the target column size, we will receive the vague and dreadful error message as demonstrated below:
If you use SQL Server 2016 with SP2 Cumulative Update 6 or newer (including SQL Server 2017 with CU12 and higher builds), you can enable Trace Flag 460 at the server global level or connection/session level. Then when the above query runs you will receive an error message that contains additional information showing clearly the table name, column name, and the truncated value as shown below:
New SQL Server Truncate Error Message with Verbose Truncation Warnings
SQL Server 2019 introduced the long-waited 'VERBOSE_TRUNCATION_WARNINGS' attribute and it is enabled by default. It can also be turned on and off, if needed. This new feature provides us a much more insightful and detailed data truncation error message including the table, column names, and the truncated value.
Let’s run the same insert statement in SQL Server 2019, as we define the FirstName column with a maximum of 10 characters and try to insert the column with data exceeding 10 characters:
CREATE TABLE [dbo].[Members] (
[MemberID] INT IDENTITY (1,1) PRIMARY KEY,
[FirstName] VARCHAR(10),
[LastName] VARCHAR(10)
)
GO
INSERT INTO [dbo].[Members] ([FirstName], [LastName])
VALUES ('Marycatherine', 'Booth')
GO
We would get the following error message:
This would allow us to quickly resolve the data error and relieve us of the time-consuming, head-scratching investigation of the data source of the error when we only had the vague “String or binary data would be truncated” error log.
Additional Notes on SQL Server 2019 VERBOSE_TRUNCATION_WARNINGS
There are some nuances and gotchas for SQL Server 2019’s new VERBOSE_TRUNCATION_WARNINGS we need to pay close attention to. Below are the two major ones we should give a bit more careful examination.
(1) Prerequisite: Database Compatibility Level 150
Starting with database compatibility level 150, the Verbose Truncation Warnings (message ID 2628) is the default and trace flag 460 has no effect. As any new databases created on SQL Server 2019 will have the compatibility level automatically set to 150, so this new feature is instantly available for new databases.
But some databases in SQL Server 2019 may have been migrated from a previous version such as SQL Server 2017, then the database compatibility level could be 140. In this case, we would still see the old Msg 8152 "String or binary data would be truncated".
Here is an example. The database named [TruncateDemo2017] sits in a SQL Server 2019, but has a compatibility level 140 and the database setting ‘VERBOSE_TRUNCATION_WARNINGS’ was turned on.
Let’s create the same table and run the same INSERT statement in this database:
USE [TruncateDemo2017]
GO
CREATE TABLE [dbo].[Members] (
[MemberID] INT IDENTITY (1,1) PRIMARY KEY,
[FirstName] VARCHAR(10),
[LastName] VARCHAR(10)
)
GO
INSERT INTO [dbo].[Members] ([FirstName], [LastName])
VALUES ('Marycatherine', 'Booth')
GO
What we received is the same old, vague Msg 8152:
To fix this, we have to upgrade the database’s compatibility level to 2019 (level 150) and then run the INSERT statement:
ALTER DATABASE [TruncateDemo2017] SET COMPATIBILITY_LEVEL = 150
GO
INSERT INTO [dbo].[Members] ([FirstName], [LastName])
VALUES ('Marycatherine', 'Booth')
GO
Then we will have the new, insightful Msg 2628:
(2) SET ANSI_WARNINGS and Truncation Warnings Message
When ANSI_WARNINGS is set to OFF, SQL Server will just truncate the data as needed to insert into the column as the maximum size allows, thus we will not receive any string or binary data would be truncated error message.
SET ANSI_WARNINGS OFF
INSERT INTO [dbo].[Members] ([FirstName], [LastName])
VALUES ('Marycatherine', 'Booth')
GO
The above insert succeeds and we can see the truncated FirstName data in the table:
Next Steps
- Please read Silent Truncation of SQL Server Data Inserts for more information on SET ANSI_WARNINGS and SQL Server truncation.
- For more SQL Server 2019 feature and tips, please check the following list SQL Server 2019 Tips.
