Tuesday 13 February 2018

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.
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.

No comments:

Post a Comment

Let me know any remarks or questions you may have. Please write down your name.

HELLO, I'M PERCY REYES! — a book lover, healthy lifestyle lover... I've been working as a senior SQL Server Database Administrator (DBA) for over 20 years; I'm a three-time awarded Microsoft Data Platform MVP. I'm currently doing a PhD in Computer Science (cryptography) at Loughborough University, England — working on cryptographic Boolean functions, algorithmic cryptanalysis, number theory, and other algebraic aspects of cryptography. READ MORE