Thursday, February 5, 2009

String or binary data would be truncated

There are many times where a query may result to the following error:

String or binary data would be truncated.
The statement has been terminated.

We will go through a simple example for explaining this behavior.

Consider the following table:

CREATE TABLE [dbo].[Products](
[id] [int] NOT NULL,
[code] [varchar](10) NULL,
[description] [varchar](100) NULL,
[id] ASC

In our scenario, we will use the Products table which as the name implies, it stores information about products.

The table has three columns:

  • id of type int which is also the primary key
  • code of type varchar with size 10
  • description of type varchar with size 100

Let's try to insert some records:

insert into Products(id,code,description)
values (1,'00000-1234','Product A')

We can see that the above insert statement was executed successfully:

select * from Products


id | code | description
1 |00000-1234 | Product A

Now let's try to insert another record:

insert into Products(id,code,description)
values (2,'00000-34567','Product B')

In this case, the above insert statement returns an error:

Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.

The above error occurs because the size of the code value (11) in the insert statement exceeds the allowed size (10).

If we take a look at the table's definition, we can see that the size of the code column is 10.
Though the size of the value (00000-34567) in the insert statement is 11 characters. In such cases SQL Server returns the abovementioned error.

If we also try to insert multiple records (i.e. by using row constructors) and there is even one record which contains a value that exceeds the allowed size defined for the specific field in the table, then the whole statement fails:

insert into Products(id,code,description)
values (1,'00000-1234','Product A'),(2,'00000-34567','Product B'),(3,'00000-3456','Product C')

Error Message:

Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.

Now let's try something else:

insert into Products(id,code,description)
values (2,cast('00000-34567' as varchar(10)),'Product B')

Within the above statement the 11-characters code value was converted to varchar(10) and the insert statement was executed successfully.

Though if we see the records in the Products table we can see that the last character (7) was not included.

select * from Products


id | code | description
1 | 00000-1234 | Product A
2 | 00000-3456 | Product B

As we can see, there are two approaches handling this issue: either filter your data correctly (with respect to the size of characters for the columns in the table's definition) before trying to insert them into the table, or use a SQL Server function (i.e. cast, convert, substring, etc.) in order to automatically remove the redundant characters. The choice is yours!

Of course, you can always use larger column sizes I guess! :)

My latest projects:
DBA Security Advisor: Secure your SQL Server instances against security risks.
In-Memory OLTP Simulator: Easily benchmark SQL Server's In-Memory OLTP Engine against your custom workload.
Artemiou SQL Books: Download my latest free books on SQL Server.
Artemiou Data Tools: See my latest software project.
Easily secure your SQL Server instances! Try out DBA Security Advisor!
DBA Security Advisor for SQL Server by SQLArtBits
DBA Security Advisor was developed by SQLArtBits. The tool was being carefully developed for an entire year and it contains comprehensive security checks and recommendations based on proven SQL Server security best practices. Feel free to download and use the Community Edition which is free and if you find it useful, you can consider upgrading to the Enterprise Edition!

Artemakis Artemiou [MVP]

Author & Editor

Artemakis Artemiou is a Senior SQL Server Architect, Software Developer and Microsoft Data Platform MVP. He is also an author, regular blogger, president of Cyprus.NET User Group and CY Country leader of INETA-EU. He is also the creator of DBA Security Advisor and In-Memory OLTP Simulator. Artemakis is a frequent guest author of worldwide well-respected online journals where he writes articles focusing on many SQL Server topics.

Reference: The SQL Server and .NET Hub (


Ameenulla said...

i am getting the same problem.i am dumping data from one table to another(insert in to table 1 select * from table2) , here my requirement is insert the valid record and discard the invalid record whose lenth is exceeding .