Rajendra Gupta
Data truncation error

SQL truncate enhancement: Silent Data truncation in SQL Server 2019

October 31, 2018 by

In this article, we’ll take a look into SQL truncate improvement in SQL Server 2019.

Data inserts and updates are a normal and regular task for the developers and database administrators as well as from the application. The source of the data can be in multiple forms as if direct insert using T-SQL, stored procedures, functions, data import from flat files, SSIS packages etc.

Sometimes we receive the bad data in terms of character limits more than the defined limit in a column. For example, if we want to insert bulk data into a database table using an insert statement, we get a bad data on character length 11 into employee name column while our existing column (Employee_name) allows only 10 characters, so how SQL Server will behave? it will raise an SQL truncate error. Insert statement will fail in this case. We normally call it as silent truncation and occur when we try to insert string data (varchar, nvarchar, char, nchar) into more than the size of the column.

If we are dealing with the huge amount of data with lots of columns, if we get any error it becomes difficult to find out which column, data caused the issue. However, it is important for us to dig out into the data and identify the bad data, fix it in order to import the data. We can use a profiler or extended events to troubleshoot the data insert, update, but again it is a resource and time-consuming. We can also use the custom stored procedure to check the length of the data before inserting into the table, but it is an extra overhead for us. Moreover, if we are getting frequent SQL truncate errors, it might be difficult to troubleshoot using a profiler and extended events in that case. This is the issue developers and DBA used to highlight in a different forum to improve the error related with silent truncation of data so that it can be quickly fixed.

Let us first create a sample database, table and insert some dummy data into it.

Example 1


Create Sample Database and Tables

As we can see above, we get the SQL truncate error message ‘String or binary data would be truncated.’

Therefore, before we move further, let me explain why this error occurred. In below query, we are checking the length of the string that we want to insert into our workload.


Check String length

While using below query, we can check that the Name column in our DemoSQL2019 table allows only 10 characters.


Check column maximum allowed character value

As we can see above, SQL truncate error occurred due to the length of the data more than the length of the string allowed in the column.

Example 2

Let us look at the complex data:


Insert sample data in bulk order

We can notice here that the table involves many columns and we are trying to insert multiple records in our example. Now, we have the same SQL truncate error message, but we did not get any clue about which row is causing the issue. Due to a large number of insert statements, it would be difficult to fix this issue. This is the behavior until SQL Server 2017. Now in below section let us see how SQL Server 2019 solves this issue.

SQL Server 2019 behavior for data truncation

Pre-requisite

Before we explore the SQL Server 2019 solution to the silent data truncation issue, we should have below pre-requisites:

In my previous article, we learned that SQL Server 2019 preview version (SQL Server vNext CTP 2.0) launched recently and you can install it on windows version by following up the SQL Server 2019 overview and installation.

Now let us look at the SQL Server 2019 behavior of this SQL truncate issue. We can see in the database properties, the database compatibility level is set to 150, which is the new compatibility level for SQL Server 2019.

If database compatibility level is other than 150, we can change it using the below query.


SQL Server 2019 Compatibility level 150

Now let us run the query from the example 1 in this SQL Server 2019 Database with compatibility level 150.

Data truncation error

We got a similar error ‘String or binary data would be truncated’. So, do we have the same kind of behavior in SQL Server 2019 as well?

No, SQL Server 2019 gives information that is more useful to fix the issue. We get the below message if the string or binary data is truncated.

String or binary data would be truncated in table ‘%.*ls’, column ‘%.*ls’. Truncated value: ‘%.*ls’.

This error message highlights clearly the table name, column name, and the truncated value in SQL Server 2019. Therefore, we do not worry or troubleshoot to find the problematic data or column giving this error message. This was a real concern for most of the DBA’s and developers and this feature was in demand from a long time.

We can look this message into sys. messages table in SQL Server 2019; message id 2628 contains this SQL truncate error message. We can see the table contains error messages for the same error code in the different language. SQL Server picks up the correct message_id based on the collation setting which is the language id in this table.

Error message id newly intoduced in SQL 2019

While in previous versions till SQL Server 2017, only ‘String or binary data would be truncated’ message is shown

String truncation error message until SQL Server 2017

In SQL Server 2019, we need to enable trace flag 460 using DBCC TraceOn. This trace flag displays the SQL truncate error message – “String or binary data would be truncated in table ‘%.*ls’, column ‘%.*ls’. Truncated value: ‘%.*ls’.”

Let us enable the trace flag 460 and run the query.

New Error message fo data truncation in SQL Server 2019

Now let us run the insert statement from our script in example 2 with DBCC TRACEON(460)Data truncation error SQL Server 2019

Solution to fix String or binary data truncation

Now in SQL Server 2019.we can easily find out the column and data, which caused the issue. In order to fix the issue, we can choose from any of the below solution appropriate to us.

  • Fix the data that we are trying to insert or update. Data length should not exceed the maximum allowed limit for the particular column
  • Use ‘SET ANSI_WARNINGS OFF’ to truncate the data and insert it as per column maximum string length

In the below example used ‘ SET ANSI_WARNINGS off’. Therefore, SQL Server will truncate the data as needed to make it fit into the column. We will not get any SQL truncate error as well since data is truncated and inserted into the table.

We can see the SQL Server inserts data as fit into the column data size. For example, ‘SQLShack ApexSQL Community’ inserted to the table after truncation as ‘SQLShack A’

SET ANSI_WARNINGS off option to insert data

  • Modify the column using the alter table statement

We can also modify the column property to allow more string length data. We can use the below query to alter table.

In the below example, we modified the column [CustomerName] to varchar (50) from varchar(30) and executed the example 2 query. This time it executes successfully.

Alter the Column value to allow more characters

Conclusion

Silent data truncation in SQL Server 2019 is a really nice enhancement. This will make many database administrators and developers happy with this detailed SQL truncate error message. Currently, we have to enable the trace flag in SQL Server 2019. I believe this feature will be available without trace flag as well in an upcoming release.


Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta
112 Views