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.
|
1 2 3 4 5 6 7 8 9 10 |
Create Database SQLShackDemo Go Use SQLShackDemo Go CREATE TABLE DemoSQL2019 ( [ID] INT identity(1,1), [NAME] VARCHAR(10), ) GO |
Example 1
|
1 2 3 4 |
INSERT INTO DemoSQL2019 VALUES ('SQLShack ApexSQL Community') GO INSERT INTO DemoSQL2019 VALUES ('Rajendra Gupta Author') GO |
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.
|
1 2 3 |
select len('SQLShack ApexSQL Community') as [StrringLength] Select len('Rajendra Gupta Author') as [StrringLength] |
While using below query, we can check that the Name column in our DemoSQL2019 table allows only 10 characters.
|
1 2 3 4 |
select character_maximum_length,column_name from information_schema.columns where table_name = 'DemoSQL2019' and Column_name='NAME' |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Customerstest]( [CustomerID] [int] NOT NULL, [CustomerName] [nvarchar](30) NOT NULL, [BillToCustomerID] [int] NOT NULL, [CustomerCategoryID] [int] NOT NULL, [BuyingGroupID] [int] NULL, [PrimaryContactPersonID] [int] NOT NULL, [AlternateContactPersonID] [int] NULL, [DeliveryMethodID] [int] NOT NULL, [DeliveryCityID] [int] NOT NULL, [PostalCityID] [int] NOT NULL, [CreditLimit] [decimal](18, 2) NULL, [AccountOpenedDate] [date] NOT NULL, [StandardDiscountPercentage] [decimal](18, 3) NOT NULL, [IsStatementSent] [bit] NOT NULL, [IsOnCreditHold] [bit] NOT NULL, [PaymentDays] [int] NOT NULL, [PhoneNumber] [nvarchar](20) NOT NULL, [FaxNumber] [nvarchar](20) NOT NULL, [DeliveryRun] [nvarchar](5) NULL, [RunPosition] [nvarchar](5) NULL, [WebsiteURL] [nvarchar](256) NOT NULL, [DeliveryAddressLine1] [nvarchar](60) NOT NULL, [DeliveryAddressLine2] [nvarchar](60) NULL, [DeliveryPostalCode] [nvarchar](10) NOT NULL, [DeliveryLocation] [geography] NULL, [PostalAddressLine1] [nvarchar](60) NOT NULL, [PostalAddressLine2] [nvarchar](60) NULL, [PostalPostalCode] [nvarchar](10) NOT NULL, [LastEditedBy] [int] NOT NULL, [ValidFrom] [datetime2](7) NOT NULL, [ValidTo] [datetime2](7) NOT NULL ) GO |
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:
- Installation of SQL Server 2019 Public preview version.
- SQL Server Management Studio 18.0 Preview 4
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.
|
1 |
ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = 150 |
Now let us run the query from the example 1 in this SQL Server 2019 Database with compatibility level 150.
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.
While in previous versions till SQL Server 2017, only ‘String or binary data would be truncated’ message is shown
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.
Now let us run the insert statement from our script in example 2 with DBCC TRACEON(460)
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’
- 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.
|
1 |
ALTER TABLE [TableName] ALTER COLUMN [Column_name] datatype(value) |
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.
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.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023
- Learn the PostgreSQL COALESCE command - January 19, 2023