SQL Server Verbose Truncation Warnings

You might have get the data truncation error in SQL Server such as “String or binary data would be truncated” . It does not specify the exact source of error in terms of database name, table name, column name and truncated values.

It usually happens when a data value somewhere does not fit under a table’s column data type size so it returns only “String or binary data would be truncated”  error rather than giving a detailed error .

Sometimes it becomes worse when you are performing bulk insertions and data having have too many columns so in this situation it becomes hard to find out which column and data causing the issue.

In SQL Server 2019, you can see detailed error of truncation like database name, table name and column name that causing issue.

Lets demonstrate a truncation error for SQL Server different different versions until SQL Server 2017 and SQL server 2019.

Check SQL Server Verbose Truncation Warnings until SQL Server 2017

Here we are using SQL Server 2017 and its compatibility level is 140.

To check database compatibility level > Right Click on database > then Click on Properties > click on Option

You can also check same using T-SQL query.

select name , Compatibility_Level, collation_name from sys.databases 
where name = 'SQLServerTutorials'

Now create a table named as TBLX, here for column ‘NAME’  data type size is given 5.

Lets try to insert value for ‘NAME’ column of size more than 5 to explore the truncation error .

CREATE TABLE TBLX
(
ID INT identity(1,1),
NAME VARCHAR(5),
)
GO

INSERT INTO TBLX VALUES ('SQL SERVER')

As you can see, It gives an error saying –‘String or binary data would be truncated.’

It does not provide exact error message so it makes hard to find out the root cause of error when performing bulk insertions or dealing with large set of queries, multiple columns so on..

Now you take the same queries and execute in SQL Server 2019, to see whether it gives detailed error or not.

Check SQL Server Verbose Truncation Warnings with SQL Server 2019

It requires SQL Server Compatibility level to 150.

If database compatibility level is other than 150, it can be changed using below T-SQL Query.

ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = 150.

Lets execute the same Queries again in SQL server 2019

CREATE TABLE TBLX
(
ID INT identity(1,1),
NAME VARCHAR(5),
)
GO

INSERT INTO TBLX VALUES (‘SQL SERVER’)

Here you can see, It gives a detailed error of truncation string in terms of database name, table name , column name and truncated value.

 5,019 total views,  1 views today