There are 96 server, database, and user properties, settings, and defaults that if changed can cause your working scripts, stored procedures, functions, and triggers to fail in the future.  I deliver my databases and upgrades to external customers; I want to verify my updates will succeed on their servers.

If you are excessively careful, you can override all 96 configuration values at the beginning of each of your t-sql objects to defend against future changes to server defaults.  Or you can trust your DBA or yourself to create an explicit, stable server and database environment and keep your t-sql writing limited to solving business problems.

For example, executing the next four lines in text output mode in an SSMS query window demonstrates how results can vary depending on how someone has set default settings.

There is a script attached to this article.  The script creates a function that returns a table of 96 configurations and reports how they compare with "assumptions" that I have embedded in the function.  The value and "intelligence" of this script is in reporting where settings are not as expected.  Go ahead and run the script in one of your databases.  Then, highlight one of the SELECT statements and execute it to see what results are returned.

In the picture above, you can see I executed the statement that shows errors.  Errors are properties that differ from my hard-coded expected values.  In row 1 we see that the server connection default setting for NUMERIC_ROUNDABORT is set to zero but I expect it to be set to 1.  Since I set IsRequiredMatch=1 and the default of zero does not match my expected value of one, I report it as an error state.  Now let's see how to customize the function.

In the picture above, you can see one of 96 configurations being selected into the return table.  The first two columns are hard-coded with identifying names.  The third column is the live value selected from sqlserver using various methods.  The fourth column is my expected value hard-coded.  I will customize this depending on which project's database I am working on.  The fifth column is called IsRequiredMatch. I will set it to 1 if the setting must match to avoid potential errors.  The last column is called IsMatched.  I set it to zero but it gets calculated and updated at the bottom of the function.

I had to dig through SQL Server's Books Online to figure out how to select the 96 live values from the server and database.  There may be additional settings in newer versions of Sql Server.  I wrote this one to work in 2008R2.  Feel free to change expected values to match your project's policies.  For the more experienced developers, consider extending this function by expecting certain values returned from the sp_configure resultset.

Your developers can run this function to see if their SSMS Tools:Options:QueryExecution:SqlServer:Advanced settings are correct for your project (database).  Your developers' applications can run this function to make sure their application connection object is setting the right default values.  With everyone working from the same set of defaults, variations on output should be eliminated.

With a stable context you will have simpler and cleaner code.  Go ahead and validate your settings and then rip out all the scattered code in your stored procedures that hard-code settings due to not having had a sturdy foundation on which to code.  I've removed all ANSI settings from all objects in my databases except where the overrides are strictly required.  See my previous article on Get Your ANSI_NULLs Settings Consistent for directions on how to do that.