How can I make SQL Server 2012 truncate insertions if they are too big?

Question

So I have a table with a column of type VARCHAR (100) and I'm wondering if there's a way to configure SQL Server 2012 (T-SQL) so that if a transaction tries to submit a string of 101+ characters then it takes the first 100.

Is this possible, or should I be doing the truncation in the C# side of things ???

Answer 1

Normally, SQL Server will present an error on any attempt to insert more data into a field than it can hold

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

SQL Server will not permit a silent truncation of data just because the column is too small to accept the data. But there are other ways that SQL Server can truncate data that is about to be inserted into a table that will not generate any form of error or warning.

By default, ANSI_WARNINGS are turned on, and certain activities such as creating indexes on computed columns or indexed views require that they be turned on. But if they are turned off, SQL Server will truncate the data as needed to make it fit into the column. The ANSI_WARNINGS setting for a session can be controlled by

SET ANSI_WARNINGS { ON|OFF }

Unlike with an insert into a table, SQL Server will quietly cut off data that is being assigned to a variable, regardless of the status of ANSI_WARNINGS. For instance:

declare @smallString varchar(5)
declare @testint int
set @smallString = 'This is a long string'
set @testint = 123.456
print @smallString
print @testint

Results is:

This 
123

This can occasionally show itself in subtle ways since passing a value into a stored procedure or function assigns it to the parameter variables and will quietly do a conversion. One method that can help guard against this situation is to give any parameter that will be directly inserted into a table a larger datatype than the target column so that SQL Server will raise the error, or perhaps to then check the length of the parameter and have custom code to handle it when it is too long.

For instance, if a stored procedure will use a parameter to insert data into a table with a column that is varchar(10), make the parameter varchar(15). Then if the data that is passed in is too long for the column, it will rollback and raise a truncation error instead of silently truncating and inserting. Of course, that runs the risk of being misleading to anyone who looks at the stored procedures header information without understanding what was done.

Source: Silent Truncation of SQL Server Data Inserts