Why SQL server some times silently truncate the values you passed to a column and some time errors

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;