Wednesday, 11 March 2015

Avoid changing default ANSI database options

Not having another way of fixing some specific errors, at times some people may turn off any ANSI database option as a final solution. To be perfectly honest, I do recommend getting to the bottom of each problem (and fix it at that level) instead of changing default configuration (unless it is truly necessary and the benefits are significant). For instance, a common error is the following one:

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


Surprisingly, many people choose to turn off ANSI_WARNINGS to fix that error, nevertheless, this is not recommendable because it may cause RECOMPILATION for each stored procedure where it was turned off, therefore, it may impact in the performance negatively. So, we should avoid turning off not only that option but also the following ones which will cause the same effect.
  • SET ANSI_DEFAULTS
  • SET ANSI_NULLS
  • SET ANSI_PADDING
  • SET ANSI_WARNINGS
  • SET CONCAT_NULL_YIELDS_NULL
Furthermore, recompilation may sometimes be necessary to 'optimize' specific stored procedures or Ad-Hoc queries because of parameter sniffing or bad-written code, but this should be done explicitly by using WITH RECOMPILE option,  RECOMPILE query hint, sp_recompile system stored procedure or another optimization strategy. That is all for now, let me know any remark you may have. Thanks for reading!