SQL SERVER 2019 – Still Getting String or Binary Data Would be Truncated

If you have read release notes of SQL Server 2019, you might have been aware of the fact that Microsoft has finally made an enhancement to error 8152 – String or binary data would be truncated. I often get a question about this topic in my Comprehensive Database Performance Health Check.

One of my blog readers contact me and informed me that they have migrated from SQL Server 2017 to SQL Server 2019 and they are not able to see enhancement which Microsoft has done about the error message. Here are the script and screenshot they shared with me.

SET NOCOUNT ON
GO
SELECT SERVERPROPERTY('ProductVersion')
GO
USE master
GO
DROP DATABASE IF EXISTS TestTruncationMessage
GO
Create database TestTruncationMessage
GO
use TestTruncationMessage
GO
CREATE TABLE MyTable (i INT, j VARCHAR(10))
GO
INSERT INTO MyTable VALUES (1,'This is more than 10 characters') 
GO
USE [TestTruncationMessage]
GO
select * from sys.database_scoped_configurations where name = 'VERBOSE_TRUNCATION_WARNINGS'
GO

As we can see above that even my client was on SQL Server 2019 and setting ‘VERBOSE_TRUNCATION_WARNINGS’ was turned on, the message was still not the new one.

WORKAROUND/SOLUTION – Binary Data Truncated

I checked the documentation and found one important thing which my client missed earlier mentioned in the ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)

For SQL Server 2019 message 2628 becomes default under Database Compatibility Level 150. You can use the Database Scoped Configuration VERBOSE_TRUNCATION_WARNINGS to revert to back to Message 8152 as default. You can also use a lower Database Compatibility Level to revert back to Message 8152 as default.

After checking the compatibility of the database, I found that database compatibility was set to 140 since this was an upgrade from SQL Server 2017. The below script would explain the behavior.

ALTER DATABASE [TestTruncationMessage] SET COMPATIBILITY_LEVEL = 140
GO
INSERT INTO MyTable VALUES (1,'This is more than 10 characters') 
GO
ALTER DATABASE [TestTruncationMessage] SET COMPATIBILITY_LEVEL = 150
GO
INSERT INTO MyTable VALUES (1,'This is more than 10 characters') 
GO

As we can see compatibility level changes the error message.

Another way to control this behavior is database-level configuration VERBOSE_TRUNCATION_WARNINGS. Here is a quick demonstration.

SELECT DATABASEPROPERTYEX('TestTruncationMessage', 'CompatibilityLevel') 'CurrentCompatibilityLevel'
GO
USE [TestTruncationMessage]
GO
ALTER DATABASE SCOPED CONFIGURATION SET VERBOSE_TRUNCATION_WARNINGS = OFF;
GO
INSERT INTO MyTable VALUES (1,'This is more than 10 characters') 
GO
USE [TestTruncationMessage]
GO
ALTER DATABASE SCOPED CONFIGURATION SET VERBOSE_TRUNCATION_WARNINGS = ON;
GO
INSERT INTO MyTable VALUES (1,'This is more than 10 characters') 
GO

I truly hope, this would clarify the impact of the VERBOSE_TRUNCATION_WARNINGS setting and compatibility level of the database on the error message.

Let me know if you find this blog post useful. Leave your feedback in the comments area and I will reply to you. You can also follow me on twitter here.

Reference: Pinal Dave (https://blog.sqlauthority.com)