Database Options

This thread is related to the second article in the SqlCredit series: Part 2: Creating the Database, Tables, CRUD Procedures.

There are a number of database options that are OFF by default but should be ON based on recommendations from the SQL Server 2000 Best Practices Analyzer. The CreateDatabase script sets all these options to ON:

• ANSI_NULLS
• ANSI_WARNINGS
• ARITHABORT
• CONCAT_NULL_YIELDS_NULL
• QUOTED_IDENTIFIER

I am very interested to find what people are using in production environments (either 2000 or 2005).

Are you setting these options to ON? If not, why not? What other options do you change, and why?

Rob

You could write an entire article on this topic alone (yes Rob, take that as a suggestion). User options can be set at the server, database, and session level. These five user options must be ON to allow UPDATE, DELETE, or INSERT statements against tables with an index on a computed column or indexed views. If a user option is not explicitly set, it can be dependant on how a user has connected to the database (DbLib, ODBC, OLEDB, etc.). So a DML statement that works in Query Analyzer may not work correctly when run from a client connection.
I definitely think it’s a good idea to set these options at the database (and maybe the server level) especially if you have indexes on computed columns or views - or if you run distributed queries.
Also as a best practice, it is a good idea to avoid code that could behave differently with different user options such as:
Don’t use: SELECT * FROM tableX WHERE Col1 = NULL
Instead: SELECT * FROM tableX WHERE Col1 IS NULL

Don’t use: SET @s1 = 'Some string' + @s2 --when @s2 could be null
Instead: SET @s1 = 'Some string' + ISNULL(@s2,' ')

Don’t use double quotes around strings or object names.
Instead use single quotes around strings and brackets around object names.

Do check for division by zero and arithmetic overflow errors (or handle the exception). Alternatively, you can explicitly unset ARITHABORT and ANSI_WARNINGS (in a stored proc) in cases where you want to avoid a statement-terminating error.