String or binary data would be truncated in SQL Server

In this SQL Server tutorial, we will understand the “String or binary data would be truncated” error in a SQL Server 2019, and we will also learn how to resolve this error. For this, we will cover the following topics.

• String or binary data would be truncated SQL Server Error
• String or binary data would be truncated SQL Server Which Column
• How to fix “String or binary data would be truncated”
• String or binary data would be truncated SQL Server 2019
• String or binary data would be truncated Datetime SQL Server
• String or binary data would be truncated in SQL Server Stored Procedure

String or binary data would be truncated SQL Server Error

This error usually occurs when we try to insert more data into a column than the specified column can store. Let’s understand this with the help of an example.

CREATE TABLE Sample (
	id INT,
	Name VARCHAR(10),
	gender VARCHAR(8)
);
insert into Sample (id, Name, gender) values (1, 'Maurits Hessing', 'Agender');

In the example, we are creating a table with 3 columns in it, and for the Name column, we have defined the data type as “VARCHAR(10)“. It means that the Name column can hold a string having 10 characters.

But while inserting a value into the table, we have specified the value for the Name column with 15 characters which is more than 10.

So the above query will return the following error.

Read SQL Server bulk insert from CSV file

String or binary data would be truncated SQL Server Which Column

Till now, we have understood the reason why we are getting this error. Now let’s understand how we can find the column due to which we are getting the error.

In SQL Server 2019, Microsoft has updated the error message for this error. For SQL Server 2019, the error message not only returns the column name but also returns the table for which the error has been encountered.

For demonstration, consider the error message that we have shown in the previous section.

Now, if we read the error message thoroughly, we will easily get the column name for which the error has been encountered.

If you have upgraded from some previous SQL Server version to SQL Server 2019. Then you might need to execute the following query in the database where you want a complete truncate warning.

ALTER DATABASE SCOPED CONFIGURATION 
SET VERBOSE_TRUNCATION_WARNINGS = ON;

And for SQL Server 2016/2017, we have to turn on the trace flag 460. And to enable the trace flag 460, we can execute the following query while inserting or updated table records.

insert into Sample (id, Name, gender) values (1, 'Maurits Hessing', 'Agender')
OPTION (QUERYTRACEON 460);

Now by enabling the trace flag 460, the SQL Server will return a detailed truncation warning, from which we can easily identify the column name.

Read: Identity Column in SQL Server

How to fix “String or binary data would be truncated”

The main reason behind this error is the more amount of data that we are trying to store in a column than a specific column can store. So a quick solution to solve this error is by increase the column size. For this, we can use the ALTER TABLE command and increase the column size.

For demonstration, consider the following query, used to modify the column size of the sample table.

--Modifying Table
ALTER TABLE Sample
ALTER COLUMN Name VARCHAR(20)
GO

--Inserting the new record
insert into Sample (id, Name, gender) values (1, 'Maurits Hessing', 'Agender')
GO

In the above example, first, we are using the ALTER TABLE and ALTER COLUMN statements to modify the size of the column. After this, we are using the INSERT statement to insert a new record. And if the inserted value is less than or equal to the size of the column, then the new record will be inserted successfully.

Read: Create Foreign Key in SQL Server

String or binary data would be truncated SQL Server 2019

While using SQL Server earlier versions, if we try to insert more data than specified for a column, we get the following error.

Now, this error message is not much helpful in debugging the issue. For this, we have to manually check the query to find and resolve the error.

But Microsoft has made some enhancements for this error message in SQL Server 2019. So, in SQL Server 2019, if this error has been encountered, the SQL Server will return a detailed truncate warning.

The column name, table name, even the value for which the error has been raised is mentioned in the error message. The example is already shown in the “String or binary data would be truncated SQL Server Which Column” topic.

If you are already using SQL Server 2019, but still not getting the detailed error message. Then execute the following query in the database where you want a complete truncate warning.

ALTER DATABASE SCOPED CONFIGURATION 
SET VERBOSE_TRUNCATION_WARNINGS = ON;

String or binary data would be truncated Datetime SQL Server

This issue commonly occurs when entering a record into a table with a VARCHAR or CHAR data type column and a value is longer than the column’s length. We will not get this error in the case of a Datetime datatype.

In the case of the DateTime data type, if we try to insert a longer value than the column size then the column will ignore the extra characters and save the value.

Now for demonstration, consider the following example given below.

--Creating table
CREATE TABLE Sample (
	id INT,
	Name VARCHAR(50),
	Joining_date DATETIME
);

--inserting values
insert into Sample (id, Name, Joining_date) values (1, 'Modesty Malley', SYSDATETIME());

• In the example, we are creating a table named “Sample” with 3 columns.
• Out of these 3 columns, we have defined the “Joining_date” column to be a DateTime column.
• Now, the Datetime column has a default fractional precision of 3, which means it can store a maximum of 3 characters in nanoseconds part (yyyy-mm-dd hh:mm:ss:nnn).
• But while inserting values, we are using the SYSDATETIME() function which returns the current date-time value in Datetime2 format. The SYSDATETIME() will return the date-time value with a fractional seconds precision of 7 (yyyy-mm-dd hh:mm:ss:nnnnnnn).
• But still, the value got successfully inserted in the Datetime column, but it has ignored the last 4 characters.

Read: Types of Backup in SQL Server

String or binary data would be truncated in SQL Server Stored Procedure

This error can also occur while executing a stored procedure in SQL Server, which is trying to insert new values in a column. But still, the reason behind this error will remain the same i.e, the column length is less than the value length that we are trying to insert.

Let’s understand this with the help of an example. And for this, we are using the following query to create a sample table.

--Creating table
CREATE TABLE Sample (
	id INT,
	Name VARCHAR(5),
	Joining_date DATETIME
);

Now take a look at the Name column, for the Name column we have defined the character length to be 5.

Next, we are using the following stored procedure to insert new records in the sample table.

ALTER PROCEDURE Insert_data
AS
--inserting values
insert into Sample (id, Name, Joining_date) values (1, 'Modesty Malley', GETDATE());

GO

Exec Insert_data

But while inserting the record through the stored procedure, we have specified the name value to be 14 characters. So, if we try to execute this procedure, we will get the following error.

And the simplest solution to resolve this error is by modifying the column and increasing the column length. This solution is already been discussed in the topic above.

You may like the following SQL server tutorials:

• SQL Server Row_Number
• SQL Server Add Column + Examples
• IDENTITY_INSERT in SQL Server
• SQL Server stored procedure output parameter

So in this SQL Server tutorial, we have learned how to solve the “String or binary data would be truncated” error in a SQL Server, and we have also covered the below topics.

• String or binary data would be truncated SQL Server Error
• String or binary data would be truncated SQL Server Which Column
• How to fix “String or binary data would be truncated”
• String or binary data would be truncated SQL Server 2019
• String or binary data would be truncated Datetime SQL Server
• String or binary data would be truncated in SQL Server Stored Procedure