Avoid changing default ANSI database options

Not having another way of fixing some specific errors, at times some people may consider turning off any ANSI database options as a final solution. To be perfectly honest, I do recommend getting to the bottom of each problem and then fixing it at that level instead of changing default ANSI settings (unless it is just a quick fix or is truly necessary because you verified the benefits are significant). For instance, it is by no means uncommon to turn ANSI_WARRINGS off to fix the following error:

Msg 8152, Level 16, State 14, Line 5
String or binary data would be truncated.

Surprisingly, it may be misunderstood that there is no more to do. First of all, this error occurs because the new data to be inserted or updated cannot be placed into the column due to the data size is beyond the column size. This situation may only be a consequence of a poor database design, so should it be altered the size of the column? if so, are there indexes for this column? what happens with the statistics linked to the column? many things to take into consideration if we wanted to alter the column to give a final solution to this issue, and obviously, that is why the impact on the performance is of paramount importance. By and large, this is not highly advisable to turn off ANSI options as it may cause RECOMPILATION for each stored procedure where it was turned off, therefore, it may impact the performance detrimentally and the knock-on effect might be regrettable. So, we should avoid turning off not only ANSI_WARNINGS but also the following ones which will cause the same effect.

• SET ANSI_DEFAULTS
• SET ANSI_NULLS
• SET ANSI_PADDING
• SET CONCAT_NULL_YIELDS_NULL

Furthermore, recompilation may sometimes be used as a way to 'optimise' specific stored procedures or Ad-Hoc queries because of parameter sniffing issues or bad-written code, but this should be done explicitly and concientiously by using WITH RECOMPILE option,  RECOMPILE query hint, sp_recompile system stored procedure or another optimisation strategy. That is all for now, let me know any remarks you may have. Thanks for reading. Stay tuned.