SQL Server Verbose Truncation Warnings

Sometimes you receive the data truncation error in SQL Server such as “String or binary data would be truncated” and it does not specify exact source of error in terms of database name, table name, column name and truncated values.

It usually happens when 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 detailed error .

Sometimes it becomes worse when you deals with the huge amount of data with lots of columns, so in this situation it becomes hard to find out which column, data caused 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 version 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 that it becomes hard to find out root cause of error when performing bulk insertion 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),
)
GOINSERT 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.

 768 total views,  5 views today