Scenario
There are scenarios when SQL server silently truncate the
values you passed to a column and sometime error as below. Actually what is
happening behind the seen here?
Msg 8152, Level 16, State 14, Line 2
String or binary data would be
truncated.
The statement has been terminated.
Let’s explore…….
The magic here is because of ANSI_WARNINGS begin set to OFF
In normal circumstances SQL server will truncate the data
passed with respect to the underlying column if ANSI_WARNINGS is set to OFF. In
case of ANSI_WARNINGS set to ON will result in the above error.
But this is overridden in case you are passing data through
a variable. For example assume you have an underlying column of varchar(50) and
you declare a variable varchar(50). If you try to assign the variable with a
string having length greater than 50 then SQL server will automatically
truncate the same to 50 with without popping any error irrespective of
ANSI_WARNINGS OFF or ON.
But the above can result in an error if you are declaring
the variable as varchar (40) for underling varchar (50) column.
Note: This truncation can also happen in .net
before hitting to SQL server if you have explicitly declared the size of SQL
parameter
For example:
comm.Parameters.Add("@Name", System.Data.SqlDbType.NVarChar, 50);
comm.Parameters["@Name"].Value = Name;
