There are some defaults that exist merely because nobody really knows what the effect of changing them would be. For example, the default instance-level collation when installing on a system that uses "US English" as the OS language is
SQL_Latin1_General_CP1_CI_AS. This makes no sense since the
SQL_* collations are for pre-SQL Server 2000 compatibility. Starting in SQL Server 2000 you could actually choose a Windows collation, and so the default for US English systems should have been changed to
Latin1_General_CI_AS. BUT, I guess nobody at Microsoft really knows what the impact will be to all of the various potential sub-systems and system stored procedures, etc.
So, I am not aware of any specific negative impact of setting it to ON as either a database default or even instance-wide. At the same time, I have not tested it. But even if I had tested it, I might still not use the same code paths as your application, so this is something that you really need to test in your environment. Set it to
ON at the instance level in your Dev and QA environments and see how that works for a month or two. Then enable it in Staging / UAT. If all continues to go well for several weeks, roll that config change to Production. The key is to give as much time as possible for testing various code paths that are not hit daily. Some are hit weekly or months or annually. Some code paths are only hit by support, or some ad hoc report or maintenance proc that someone created years ago and never told you about and only gets used at random intervals (nah, that never happens ;-).
So, I did some testing on an instance that still has the default "user options" setting as I have never changed it.
'user options' is a bitmasked value
- 64 is the bit for
I tested with both SQLCMD (which uses ODBC) and LINQPad (which uses .NET SqlClient):
SQLCMD -W -S (local) ^
-Q"SELECT CONCAT(DB_NAME(), N': ', @@OPTIONS & 64, N' (', ses.[client_interface_name], N')') FROM sys.dm_exec_sessions ses WHERE ses.[session_id] = @@SPID;"
^ is the DOS line continuation character; the
. on the last line is just to force the extra line to make it easier to copy-and-paste)
using (SqlConnection connection =
using (SqlCommand command = connection.CreateCommand())
command.CommandText = @"SELECT @RetVal =
CONCAT(DB_NAME(), N': ', @@OPTIONS & 64, N' (', ses.[client_interface_name], N')')
FROM sys.dm_exec_sessions ses
WHERE ses.[session_id] = @@SPID;";
SqlParameter paramRetVal = new SqlParameter("@RetVal", SqlDbType.NVarChar, 500);
paramRetVal.Direction = ParameterDirection.Output;
TEST 1: Before
master: 0 (ODBC)
tempdb: 0 (.Net SqlClient Data Provider)
CHANGE DEFAULT CONNECTION OPTION:
The following T-SQL enables
ARITHABORT without removing any other options that might be set, and without changing anything if
ARITHABORT is already set in the bitmasked value.
DECLARE @UserOptions INT;
-- Get current bitmasked value and ensure ARITHABORT is enabled:
SELECT @UserOptions = CONVERT(INT, cnf.[value_in_use]) | 64 -- enable "ARITHABORT"
FROM sys.configurations cnf
WHERE cnf.[configuration_id] = 1534 -- user options
-- Apply new default connection options:
EXEC sys.sp_configure N'user options', @UserOptions;
TEST 2: After
master: 64 (ODBC)
tempdb: 64 (.Net SqlClient Data Provider)
- There does not seem to be any benefit to having
- There is benefit to having
- The default connection setting (unless overridden by the connection) =
- It does not appear that either ODBC or OLEDB / .NET SqlClient attempt to set
ARITHABORT, thus they accept the default setting
I would suggest changing the instance-wide default connection options (as shown above). This would be less obtrusive than updating the application. I would only update the app if you find a problem with changing the instance-wide setting.
P.S. I did a simple test with changing
tempdb and not changing the instance-wide setting and it did not seem to work.