String or Binary Data would be Truncated in SQL

Last week, I worked on a stored procedure in SQL Server. I got an error while working on the query about adding the values in the table. The error came as “String or Binary Data would be Truncated in SQL Server”.

String or Binary Data would be Truncated in SQL

This is a common error that database administrators and developers struggle with is the String or binary data being truncated error.

This error usually arises when data is updated or added to a SQL Server table that exceeds the allowed column sizes. However, without the proper information, identifying the root cause of the error and fixing it cannot be easy.

Let’s understand the “String or binary data would be truncated” error in SQL Server and learn how to resolve it.

Here, was trying to create a table name called HRDetails with the columns and insert the values into it.

• Id
• Name VARCHAR(10) -> Name column holds 10 characters.
• Gender VARCHAR(8)

CREATE TABLE HRDetail (
	id INT,
	Name VARCHAR(10),
	gender VARCHAR(8)
);
insert into HRDetail (id, Name, gender) values (10, 'THOMAS JAMES William', 'Male');

While inserting a value into the table, I specified a value for the Name column that is more than 10 characters long. So, after executing the above query, I got the following error as shown in the below screenshot.

This is the error I got when I tried to add more values than the actual value.

String or Binary Data would be Truncated SQL Server Which Column

We understand why we are getting this error. Now, let’s understand how to find the column for which we get the error.

Microsoft has updated the error message for this error in SQL Server 2019. The error message now returns the column name and the table for which the error has been encountered.

If we read the error message thoroughly, we will easily find the column name for which the error has been encountered.

Suppose 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.

Solution

The reason for this error is the more data. We are trying to insert a column that a specific column can store. So, the solution to this error is to increase the column size. We can use the ALTER TABLE command to increase the column size.

Look at the below query, which is used to modify the column size of the table HRDetail.

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

--Inserting the new record
insert into HRDetail (id, Name, gender) values (10, 'THOMAS JAMES William', 'Male')
GO

In the above example, we first use the ALTER TABLE and ALTER COLUMN statements to modify the size of the column. Then, we use the INSERT statement to insert a new record. If the inserted value is less than or equal to the size of the column, then the new record will be inserted successfully.

By doing this, we can alter the column in SQL Server.

While using earlier versions of SQL Server, we will get the error below if we try inserting more data than is specified for a column.

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

However, Microsoft has improved this error message in SQL Server 2019. So, if this error appears, SQL Server 2019 will return a detailed truncate warning.

The error message mentions the column name, table name, and even the value for which the error has been raised. 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 do not get the detailed error message, 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 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. However, the reason behind this error will remain the same: the column length is less than the value length we are trying to insert.

Let’s understand this with the help of an example where I got this error. I executed the below query to create a sample table for this.

--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, I used the following stored procedure to insert new records into 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

However, I specified the name value as 13 characters while inserting the record through the stored procedure. So, After executing this procedure, I got the same error as shown in the screenshot below.

The simplest solution to resolve this error is to modify and increase the column length, which has already been discussed in the topic above.

Conclusion

Now you have gained knowledge about the error message String, or Binary Data Would Be Truncated in SQL Server. When inserting it with the stored procedure, we will also get the error until we alter the table’s column size.

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